06-10-2013 11:10 AM
In the below table i want to write an sql to get year-to-last week data dynamically so whenever i run the below query it should give me the data starting from financial week 1 to last week. In the below eg if i run today(10th June 13) it should give me data starting from week 1 to week 12.
i am using the following query to connect to fact table
create table RDW1
sum(a1.F_SLS_AMT) as SALES
from RDW.SLS_SBC_LW_DM a1,
where a1.WK_IDNT=a2.WK_IDNT and
group by a1.WK_IDNT
06-10-2013 11:27 AM
If your financial year is the same as fiscal year, you could dig into the intnx function, which helps you calculate date intervals dynamically. Idf the standard intervals doesn't fit, it even lets you use an imported calendar...
06-10-2013 04:51 PM
Again with consideration of fiscal and calendar year something like
week(yourdatevariable) = (week(today()) - 1)
may be close to what you're looking for.