Post reply

How to Display current week number for a year

  • July 24, 2012 at 4:00 am

    #259363

    Hi,

    Can some one tel me how can I display current week number in SQL?

    I need to use this function in Cognos 10.1- cross tab report for the requirement.

    Thanks

    Gopi

  • July 24, 2012 at 4:05 am

    #1516686

    select datepart(week,getdate())

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 24, 2012 at 4:17 am

    #1516692

    Hi,

    If I use your function in my report with below expression I am getting parsing error.

    datepart(MERCHANDISING_WEEK,getdate())

    Thanks

    Gopi

  • July 24, 2012 at 4:19 am

    #1516693

    DATEPART(WEEK, MERCHANDISING_WEEK)

    DATEPART requires the part first then the date to which to calculate the part from.

    So you want the WEEK of MERCHANDISING_WEEK

  • July 24, 2012 at 4:25 am

    #1516695

    Sorry - should have made my answer a bit clearer.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 24, 2012 at 4:26 am

    #1516696

    Hi,

    Then also same error QE-DEF-0459 CCLException pasing error before or near expression....

    PFA error.

    Thanks

    Gopi

  • July 24, 2012 at 4:29 am

    #1516697

    Ergh Cognos, well that will be a problem as you will need to look at the EXTRACT function instead

    Found this for Cognos8 http://www-01.ibm.com/support/docview.wss?uid=swg21345500

  • July 24, 2012 at 4:35 am

    #1516699

    Hi,

    My report is cross tab & uses relational modelling now tel me how can I get the required week numbers in my cross tab report based on the season which I select

    from the prompt.....

    For example: If I select Groceries - as segment name, Groceries - as family name, Season year - Spring2009

    then I should get week number starting from 6,7, 8,etc..........in 1st row, then in second row I should get corresponsding month&year.

    Now I am already getting above 2 rows problem is with getting week numbers in 3rd row.....by considering 0 as current week and previous 9 weeks(-9)

    and next 6 weeks.........PFA of current output what I am getting already & required format I need to get finally i the report which shows week numbers in 3rd row of cross tab.

    Please refre attachments you will get the idea what i am trying to ask....

    Please let me know if you need more details

    Thanks

    Gopi

  • July 24, 2012 at 4:41 am

    #1516705

    I'm not a Cognos expect, I personally stay away from it as I think you can do all that Cognos does and more in SQL with SSAS SSRS and SSIS without paying the extra price tag.

    I would push your employer for Cognos training if not already and/or maybe hire a contractor who knows Cognos like the back of their hand.

    You might get lucky on here if they are any Cognos experts about.

  • July 24, 2012 at 4:46 am

    #1516706

    84gopi (7/24/2012)

    Hi,

    My report is cross tab & uses relational modelling now tel me how can I get the required week numbers in my cross tab report based on the season which I select

    from the prompt.....

    For example: If I select Groceries - as segment name, Groceries - as family name, Season year - Spring2009

    then I should get week number starting from 6,7, 8,etc..........in 1st row, then in second row I should get corresponsding month&year.

    Now I am already getting above 2 rows problem is with getting week numbers in 3rd row.....by considering 0 as current week and previous 9 weeks(-9)

    and next 6 weeks.........PFA of current output what I am getting already & required format I need to get finally i the report which shows week numbers in 3rd row of cross tab.

    Please refre attachments you will get the idea what i am trying to ask....

    Please let me know if you need more details

    Thanks

    Gopi

    This is a SQL Server forum. Your questions appear to relate to the use of Cognos, not SQL Server, so I suggest that you need to find a different forum for these questions.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 24, 2012 at 4:48 am

    #1516707

    Hi,

    I agree if you don't know cognos......but you can help me writing SQL right.....becasue I need to use sql or any function which validates with the requirement.........then only cognos tool....comes......

    I am asking to provide the SQL with the req details so that I can implement that in my report.....

    Hole you got what I am trying to say.,..........

    Thanks

    Gopi

  • July 24, 2012 at 4:55 am

    #1516712

    We've done that. The Datepart solution works in pure SQL Server.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 24, 2012 at 4:56 am

    #1516713

    Yes we can help with a SQL query for your requirements, but we will require you to provide DDL, sample data, expected outcome as per the second link in my signature, then we will be able to give you a SQL query which fits your needs.

Viewing 13 posts - 1 through 12 (of 12 total)

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