July 12, 2008 at 1:55 am
Dear friends
I will have to upload the data on weekly bases. For that i need to maintain one week matrix.
In that table i have the fallwoing columns.
currentweekdate,lastweekdate,lastqtrdate,lasttolastqtrdate.
Can u plz help me to update the columns lastweekdate lastqtrdate and lasttolastqtrdate.
Thanks
koti
July 13, 2008 at 11:07 am
Are you maintaining just one row in the table and updating it with each load?
Is the following example what you have in mind?
currentweekdate = 2008-07-14
(date of run)
lastweekdate = 2008-07-07
(one week before currentweekdate)
lastqtrdate = 2008-07-01
(start of quarter currentweekdate is in)
lasttolastqtrdate = 2008-04-01
(start of previous quarter)
July 13, 2008 at 11:39 pm
Hi u r almost cortrect.
Every week i will have to add one row and i will have to maintain a flag with column name Currentweek and this column is Y for the new row and N for the rest of the rows.
WHat have u said was correct.
But how to implement that through Package or some other way.
July 14, 2008 at 8:50 am
I use a calendar table for something similar. I have a list of dates and certain values (Fiscal Year, Fiscal Qtr, Fiscal Month, Fiscal Week) for each date. The calculations for these are straight forward. I then have a function dbo.fGetDateOnly that returns only the data of a datetime value. By querying SELECT FYear, Fweek FROM tCalendar WHERE CDate = dbo.fDateOnly(DATEADD(dd, -6, CURRENT_TIMESTAMP)) I can get the fiscal year and fiscal week number of the last full fiscal week (which is what we use most often.
ALTER FUNCTION [dbo].[fDateOnly](@CDate AS DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATEADD(dd, DATEDIFF(dd,0,ISNULL(@CDate, CURRENT_TIMESTAMP)), 0)
END
I hope this gives you some ideas!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply