Brain lock...

• jconaty54

  Newbie

  Points: 5

  More actions

  June 6, 2013 at 8:09 am

  #394922

  I have a table with schema that looks like this

  Year, Jan, Feb, Mar, Apr, May,June, July, Aug, etc...

  The report calls for the month selected plus 3 months back. This works great until a user selects Feb. This means I have to go to another row in the table to get the prev year. I could do this in a loop or even scarier a cursor. I'm looking for a fresh set of eyes. Any suggestion on the best way to return the results for the following?.

  Year = 2013 AND Month = Jan (- 3 Months back. ie Jan - Oct)

  Thanks!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 6, 2013 at 8:30 am

  #1621765

  jconaty54 (6/6/2013)

  ---

  I have a table with schema that looks like this

  Year, Jan, Feb, Mar, Apr, May,June, July, Aug, etc...

  The report calls for the month selected plus 3 months back. This works great until a user selects Feb. This means I have to go to another row in the table to get the prev year. I could do this in a loop or even scarier a cursor. I'm looking for a fresh set of eyes. Any suggestion on the best way to return the results for the following?.

  Year = 2013 AND Month = Jan (- 3 Months back. ie Jan - Oct)

  Thanks!

  Can you post ddl for the table with a few sample rows (as INSERTs) please?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 6, 2013 at 9:23 am

  #1621794

  I was going to suggest the CROSS APPLY UNPIVOT[/url] but I realized it was the 2005 forum.

  There's not a great solution with denormalized data like this one. A UNION ALL could help but I'm not sure that's going to be a great improvement.

  An idea that came out would be:

  WHERE Year BETWEEN YEAR(@Date) AND YEAR( DATEADD( MONTH, -3, @Date))

  You must have a date that you can build from your Year and Month variables.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  June 6, 2013 at 9:46 am

  #1621809

  It seems like your first step is going to have to be normalizing this data. If you can't normalize the base table you will need to do this in a cte or a temp table. Then you can easily query against the normalized data.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/