Trending dynamic data 1

[slybitz]

I have a stored procedure that takes in two date parameters (start date, and end date). In the proc I create a temp table with three columns (description, quantity, date), and I fill that temp table with data. I fill the temp table with data by looping through the date range of what is passed in through the parameters to find the quatntity values of each product. So for example if I pass in the parameter dates of '5/1/2005', '6/30/2006' my proc will set off a loop querying the database for quantity values between the date params and fill the temp table with the product description, the quantity values, and the date that the quantity values were bought. So the temp table would end up looking something like this:

Description     Quantity      Date
-----------     --------      -------------------
product 1              5      May  1 2006 12:00AM
product 1             15      Jun  1 2006 12:00AM
.
.
.

So my question is how can I trend this data so that my result set would look like below:

Description    May Qty   Jun Qty 
-----------    -------   -------
product 1            5        15
product 2           13        28
.
.
.

Is this possible with dynamic date parameters? I can do this very easily if the parameters were not dynamic. BUT I have no idea how to trend the data like the above if the date parameters are dynamic. Any suggestions would be GREATLY appreciated! Thank you for your time and sorry for the really long post.

 

[SQLSister]

First, I'm not sure why you are looping to get the data you need. YOu should be able to use a set-based query for this. Looping through recorsds is a bad thing in a database as it is the most inefficent way to get data.

That said - on to the real subject.

Personally when I set up file like this I specifically set them up to cover all the 12 months of the latest year and then there will be 0s for the months that your data doesn't cover. Then you can use Case statements to pull the data from your temp table for each month.

Questions about posting. See faq183-874

 

[slybitz]

Perfect. I decided to set up all 12 months and use a case statement pulling from only the dates between the parameter start and end dates like you said. I was hoping there was an easier way but I don't think there is. ...that actually turned out to be pretty easy.

Thanks for you help!