Post reply

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

  • 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

  • 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/

  • 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

  • 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!

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply