By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

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

P: 32
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?
Feb 12 '09 #1
Share this Question
Share on Google+
1 Reply

Uncle Dickie
P: 67
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!

Expand|Select|Wrap|Line Numbers
  1. DECLARE @myStartDate DATETIME
  3. DECLARE @mthCounter INT
  4. SET @myStartDate = getDate()-760
  5. SET @mthCounter = 0
  6. WHILE @mthCounter < 25
  7. BEGIN
  8.     SET @myDate = Dateadd(month, @mthCounter, @myStartDate)
  9.     PRINT CASE Datepart(weekday, CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/01' AS datetime))
  10.                 WHEN 1 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/19' AS datetime)
  11.                 WHEN 2 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/18' AS datetime)
  12.                 WHEN 3 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/17' AS datetime)
  13.                 WHEN 4 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/16' AS datetime)
  14.                 WHEN 5 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/15' AS datetime)
  15.                 WHEN 6 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/21' AS datetime)
  16.                 WHEN 7 THEN CAST(CAST(year(@myDate) AS varchar) + '/' + CAST(month(@myDate) AS varchar) + '/20' AS datetime)
  17.             END
  18.       SET @mthCounter = @mthCounter + 1
  19. END
Feb 12 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.