How to Display current week number for a year

Question

Post reply

How to Display current week number for a year

84gopi

Mr or Mrs. 500

Points: 567
More actions
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

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

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

select datepart(week,getdate())

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 2

Hi,

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

datepart(MERCHANDISING_WEEK,getdate())

Thanks

Gopi

Ant-Green SSC Guru Points: 113509 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 4

Sorry - should have made my answer a bit clearer.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 5

Hi,

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

PFA error.

Thanks

Gopi

Ant-Green SSC Guru Points: 113509 More actions · Answer 6

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

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 7

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 8

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.

Phil Parkin SSC Guru Points: 246991 More actions · Answer 9

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 10

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 11

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ant-Green SSC Guru Points: 113509 More actions · Answer 12

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.