Need help writing a Stored Procedure

[Griffyn]

Hi all,

Apologies for the weak subject title. I have a task that I'm stuck on that I'm sure is simple if only I had a bit more experience with this sort of thing.

I have a database table of product sales history organised as following:

Field List: CODE, YEAR, MONTH_1, MONTH_2 .. MONTH_12

So a particular product may have 5 records. The Year field is 0..4 indicating current year, last year, year before that, etc. And the MONTH fields indicate a total of sales for a particular month. MONTH_1 is always July, MONTH_2 is August, MONTH_12 is June.

So to see the history for May 2006, and we're currently in May 2006, I would look at a record where YEAR=0 and get the MONTH_11 field. If today were in July 2006, to get the same figure I would look for a record where YEAR=1 and get the MONTH_11 field.

I'm stuck with this format (our accounting package) and I need to write a stored procedure so that I can get the data in a better format. eg. if I pass to this procedure CODE="XXX", DATE_FROM="1-Mar-2005", DATE_TO="1-Sep-2005" then it will give me the fields:

CODE, MONTH_1, MONTH_2 .. MONTH_7

where MONTH_1 is Mar-2005 and MONTH_7 is Sep-2005.

I'm lost right from the beginning, so I very much appreciate any guru giving me a headstart or a finished procedure.

Thanks!

 

[vbkris]

hi, how come the field names themselves are changing? this can be done using Dynamic SQLs, but it can be very tiresome.

approach:
-> using Datepart get the months passed in from and to.
-> convert them to ur respective months (e.g.: if 1-Mar-2005 is passed then it refers to the MONTH_9 column right?)
-> build the SQL dynamically (so that the correct months are selected.


Known is handfull, Unknown is worldfull

 

[JohnDTampaBay]

How about creating a view that normalizes the data, then querying the view?

CREATE VIEW SomeView AS
  SELECT Code, Year, Month_1 AS July FROM YourTable
  UNION ALL
  SELECT Code, Year, Month_2 AS August FROM YourTable
  UNION ALL
  ...etc...

It will take a little more work to get the From and To dates correct. I need more Mountain Dew and little more information. What's the primary key on this table, (Code, Year)?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)