Week genearation dynamically

  • I am using the below sp to generate the weeks from 2009 and 2010, but i did not get the exaact results...Current week is getting, but previous week is not getting..

    SP:

    select WKRed from (

    select distinct ' + @p_Suffix + '+

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 1),' + @p_Column + ')) , 111 ),10) + ''-'' +

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 7),' + @p_Column + ')) , 111 ),10) WKRed,

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 7),' + @p_Column + ')) , 111 ),10) WKRedOrd

    from ' + @p_Table + ' ' + @p_Where + '

    union

    select ' + @p_Suffix + '+

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 1),getdate())) , 111 ),10) + ''-'' +

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate())) , 111 ),10) WKRed,

    right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate())) , 111 ),10) WKRedOrd

    )TAB

    ORDER BY TAB.WKRedOrd desc

    The result IS:

    [Week:2010/01/10-2010/01/16],[Week:2009/12/27-2010/01/02],[Week:2009/12/20-2009/12/26].

    Here 2010/01/03-2010/01/09 week is missing.

    Please help me regarding this

  • It looks like you're using dynamic query.

    The second part seems to work (I'm getting WKRed:2010/01/10-2010/01/16, WKRedOrd: 2010/01/16).

    It looks like there are some data missing when you apply your dynamic table selection and WHERE clause (from ' + @p_Table + ' ' + @p_Where).

    You should print the statement instead of executing it and check if the resulting query is what you need.

    If you'd like us to have a closer look at it we'd need some sample data. Please see the first link in my signature on how to do that.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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