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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy