Week No - Quarter number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Week No - Quarter number

Author

Topic

whitegrs
Starting Member

5 Posts

Posted - 2009-11-27 : 12:16:26

Hello All

I have a BI project that I am working on.
I need to categorize data into week numbers and quarters.

The problem is this: Some WeekNos fall across 2 quarters :

Example: records in the 27th week of the year (200927) fall into the second quarter and the third quarter: becuase week starts 29/6/2009 - 04/07/2009.

The problems is showing up as duplicated results in the queries.

Any Ideas?

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-27 : 12:23:39

define a table with 1 record per day.

1/1/2009 q1
2/1/2009 q1
3/1/2009 q1
etc
29/6/2009 q2
30/6/2009 q2
etc
04/7/2009 q3

or apply a formula to the month number... quarter = floor(month/3)

whitegrs
Starting Member

5 Posts

Posted - 2009-11-27 : 12:26:45

Thanks for the reply

I cant use Floor(month) becuase it crosses 2 months.
I also cant use a caledar table becuase the client doesnt want to maintain a table

whitegrs
Starting Member

5 Posts

Posted - 2009-11-27 : 12:29:16

Also, Im using indexed aggregated views so I cant use a CASE statement in the indexed view.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-30 : 05:40:14

a) find something that works
b) find something that works better.

At the moment you seem to be rejecting options that satisfy a). Good luck in the search.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-30 : 07:10:55

What's wrong with:



SELECT DATEPART(QUARTER,[yourDate])
SELECT DATEPART(WEEK, [yourDate])

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources