how to get current, previous 9 week numbers & also next 6 weeks number for any month

• 84gopi

  Mr or Mrs. 500

  Points: 567

  More actions

  July 13, 2012 at 5:36 am

  #277850

  Hi,

  How to get output for current week starting with 0 say, then previous 9 weeks and next 6 weeks like

  -9,-8,-7,-6,-5,-4,-3,-2,-1,0,1,2,3,4,5,6 ........need to get these details in 1 row for a given month in 2009 or 2010.

  How to write SQL for this? can any one help me plz.......

  Thanks

  Gopi

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  July 13, 2012 at 5:58 am

  #1512482

  Your explanation is not very clear

  Can you provide some more details along with an example and sample results

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• 84gopi

  Mr or Mrs. 500

  Points: 567

  More actions

  July 13, 2012 at 6:24 am

  #1512494

  Hi,

  I need to get current week - previous week ( 9 ) + next 6 weeks of data in cross tab report.

  irrespective of the month & year you selece from the report.

  For example: if u select july 2012 then it should display current and previous 9 weeks of july & next 6 weeks from August 2012 & sept 2012...........

  I am using rdbms & retails bi as package. i use MERCHANDISING_WEEK_DIM_ID1 column in order to display week number.

  So how to modify below expn using above column......os that I can add one query calculated item in cross tab and do the testing...............But i have 3 cascading prompts in report that will select segment name, family name & session year will display like ........PFA for the same...

  So based on the session year week numbers will display in the report and below to this I should get the current week, previous week and next 6 weeks like that...............

  In the attachment you find out output format i need to get in my report...........

  Thanks

  Gopi

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 13, 2012 at 6:26 am

  #1512495

  here's my best guess getting the date ranges; i leave it up to you to convert it to the cross tabl format, and whatever the data is you want in it?

  /*--Results

  CurrentWeek MondayOfCurrentWeek WeekNumber

  ----------- ----------------------- -----------

  -9 2012-05-07 00:00:00.000 19

  -8 2012-05-14 00:00:00.000 20

  -7 2012-05-21 00:00:00.000 21

  -6 2012-05-28 00:00:00.000 22

  -5 2012-06-04 00:00:00.000 23

  -4 2012-06-11 00:00:00.000 24

  -3 2012-06-18 00:00:00.000 25

  -2 2012-06-25 00:00:00.000 26

  -1 2012-07-02 00:00:00.000 27

  0 2012-07-09 00:00:00.000 28

  1 2012-07-16 00:00:00.000 29

  2 2012-07-23 00:00:00.000 30

  6 2012-08-20 00:00:00.000 34

  4 2012-08-06 00:00:00.000 32

  5 2012-08-13 00:00:00.000 33

  6 2012-08-20 00:00:00.000 34

  */

  SELECT

  CurrentWeek = WeekRange ,

  MondayOfCurrentWeek = DATEADD(wk,WeekRange,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)),

  WeekNumber = DATEPART("wk", DATEADD(wk,WeekRange,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)))

  FROM ( SELECT -9 AS WeekRange UNION ALL

  SELECT -8 AS WeekRange UNION ALL

  SELECT -7 AS WeekRange UNION ALL

  SELECT -6 AS WeekRange UNION ALL

  SELECT -5 AS WeekRange UNION ALL

  SELECT -4 AS WeekRange UNION ALL

  SELECT -3 AS WeekRange UNION ALL

  SELECT -2 AS WeekRange UNION ALL

  SELECT -1 AS WeekRange UNION ALL

  SELECT 0 AS WeekRange UNION ALL

  SELECT 1 AS WeekRange UNION ALL

  SELECT 2 AS WeekRange UNION ALL

  SELECT 6 AS WeekRange UNION ALL

  SELECT 4 AS WeekRange UNION ALL

  SELECT 5 AS WeekRange UNION ALL

  SELECT 6 AS WeekRange

  ) t2

  desire

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• 84gopi

  Mr or Mrs. 500

  Points: 567

  More actions

  July 30, 2012 at 4:10 am

  #1519063

  Hi,

  I have week number in my table its displaying value as wk1,wk2,wk3.....like that.......

  So is it possible to write sql function by using this column on order to display current week as 0, then previous 7 or 8 weeks then next 3 weeks like that........

  Plz reply me ASAP.....since I need to use this function in my report.......

  Thanks

  gopi