Look at every *Third Tuesday* of the month on record ...?

[maxx233]

I need to look at/average some data from every third tuesday of each month during a specific time of day, over the course of the last 2 years. Can anyone think of ways, or recommend something to cleanly do this in one shot? I can't think of anything, and can't seem to find anything on it either. I know how to do everything that's not date sensitive if I were to look up all 24 dates of the third tuesday of the month and manually enter those into my query as conditions, or possibly 24 different queries loading up a temp table if all else fails... but I'd really like a one shot automated way of doing it that doesn't require that I update the query each time to remove some old date conditions and add more recent dates instead.

Surely there must be a way I would think, I would imagine many places would have need to compare data to see how sales are doing the 1st monday of the month as opposed to the third monday... Or to compare how all monday sales look against how all thursday sales look.. My needs are pretty much the same

Any ideas?

[Uncle Dickie]

Hi there, don't know if you can make use of the following but this returns a list of the 3rd Thursday every month for the last 2 years.

If you can work out a way of taking the resulting set of dates in to a WHERE IN criteria then you should be able to get what you want.

Unfortunately my skills stop at the list of dates!

DECLARE @myStartDate DATETIME
DECLARE @myDate DATETIME
DECLARE @mthCounter INT
SET @myStartDate = getDate()-760
SET @mthCounter = 0
WHILE @mthCounter < 25
BEGIN
    SET @myDate = Dateadd(month, @mthCounter, @myStartDate)
    PRINT CASE Datepart(weekday, CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/01' AS datetime))
                WHEN 1 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/19' AS datetime)
                WHEN 2 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/18' AS datetime)
                WHEN 3 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/17' AS datetime)
                WHEN 4 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/16' AS datetime)
                WHEN 5 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/15' AS datetime)
                WHEN 6 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/21' AS datetime)
                WHEN 7 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/20' AS datetime)
            END
      SET @mthCounter = @mthCounter + 1
END