December 16, 2004 at 11:58 am
I have a Crosstab query I creaed in Access and I need to convert it to a Stored procedure or Sql Query that allows me to pass a varialbe (DLookup).
I need to create a method to calcuate the StoreCount by Fiscal Period, unfortunately Fiscal period can crossover months (ie. Fiscal Period = 1/1/04-2/5/04) I currently have a Table that handles assigning the correct Fiscal period based on the date passed. FIscalCalendarDay(Table), CalendarDay, DateText, FiscalMonth(1/1/03). I also need the data in a pivot format with the field names fixed.
This is my attempt in Access, unfortunately - crosstab queries do not allow you to name the fileds, and since the date range may change this is not an option.
TRANSFORM Sum(FCStoreCount.StoreCount) AS SumOfStoreCount
SELECT DLookUp("FiscalMonth","FiscalCalendarDaily","CalendarDate = Date()") AS CurrentFiscalPeriod, FCStoreCount.DemandSrcID
FROM FCStoreCount
WHERE ((DateDiff("m",DLookUp("FiscalMonth","FiscalCalendarDaily","CalendarDate = Date()"),[FCStoreCount]![FYPeriod])<1 And DateDiff("m",DLookUp("FiscalMonth","FiscalCalendarDaily","CalendarDate = Date()"),[FCStoreCount]![FYPeriod])>-12))
GROUP BY FCStoreCount.DemandSrcID
PIVOT FCStoreCount.FYPeriod;
This is my attempt at a SQL Query - again I can't use the GetDate() - I need to subustitute for the CalendarDay connection to pass the Fiscal period from the FCCalendarDAy table.
SELECT fcd.FCHdrID,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(GetDate()) THEN fcd.ShippedQty ELSE 0 END) AS CurrMonth ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -1, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth1 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -2, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth2 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -3, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth3 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -4, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth4 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -5, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth5 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -6, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth6 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -7, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth7 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -8, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth8 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -9, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth9 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -10, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth10 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -11, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth11 ,
SUM(CASE Month(fcd.FYPeriod) WHEN Month(DateAdd( m, -12, GetDate())) THEN fcd.ShippedQty ELSE 0 END) AS PrevMonth12
FROM FCDetail fcd
GROUP BY fcd.FCHdrID
Any and all assistances is greatly appreciated.
Karen
December 20, 2004 at 8:00 am
This was removed by the editor as SPAM
December 20, 2004 at 12:46 pm
Hm, this http://www.rac4sql.com is a frequently referenced tool to help make the move from Access to SQL Server with respect to such issues. Maybe it helps a bit.
You can assign the correct Fiscal Period to a variable within your stored procedure and use this variable in the further processing or you can also have a UDF that returns the appropriate Fiscal Period from your table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 20, 2004 at 12:47 pm
I'm not sure what your question is. Is it how to pass in the date?
This is how you create a procedure with a parameter:
create proc usp_MyNewProc @myDate dateTime as
In your select statement, substitute @myDate for getDate().
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply