Update week matrix

• kvaramu2005

  SSC-Addicted

  Points: 417

  More actions

  July 12, 2008 at 1:55 am

  #154520

  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

• kbatta

  SSCrazy

  Points: 2274

  More actions

  July 13, 2008 at 11:07 am

  #842107

  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)

• kvaramu2005

  SSC-Addicted

  Points: 417

  More actions

  July 13, 2008 at 11:39 pm

  #842190

  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.

• Timothy J Hartford

  SSCarpal Tunnel

  Points: 4214

  More actions

  July 14, 2008 at 8:50 am

  #842438

  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!