Current QTD, Previous QTD - 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)

Current QTD, Previous QTD

Author

Topic

jay83091
Starting Member

14 Posts

Posted - 2010-07-01 : 01:12:16

Hi Experts,

I have a detailed date dim table trying to figure out a way to create a column that indicates 'Calendar Type' (i.e. determines whether the period [e.g. 200912, 201001 etc] is the current QTD or previous QTD)

e.g. for the current month of July 2010

20104 - previous QTD
20105 - previous QTD
20106 - previous QTD

20107 - current QTD
20108 - current QTD
20109 - current QTD

thanks for your help in advance

I have these in the view already.

Current MAT 201001
Current YTD 201001
Month 201001
Current MAT 201002
Current YTD 201002
Month 201002
Previous Month 201002
Current MAT 201003
Current Month 201003
Current YTD 201003
Month 201003
Month 201004
Month 201005
Month 201006
Month 201007
Month 201008
Month 201009
Month 201010
Month 201011
Month 201012

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-01 : 01:53:09

[code]
select datecol,
case when datecol >= dateadd(quarter, datediff(quarter, 0, getdate()), 0)
then 'Current'
else 'Previous'
end
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jay83091
Starting Member

14 Posts

Posted - 2010-07-01 : 02:29:17

thanks for the reply.

woudln't this code apply 'Current' to all period beyond?

i.e. when we are on July 2010,
Current QTD will be displayed on 'Calendar Type' based on period 201007,201008,201009

Previous QTD will be displayed on 'Calendar Type' based on previous period of 201004, 201005, 201006

and from period 201010 onwards, it wouldn't apply yet.

Looking to things like this below.

Period / Calendar Type
201004 / Previous QTD
201005 / Previous QTD
201006 / Previous QTD
201007 / Current QTD
201008 / Current QTD
201009 / Current QTD
201010 / NULL

and once the calendar hits 2010 Oct, it would change to Current QTD

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-01 : 07:59:35

[code]
select datecol,
case when datecol > dateadd(quarter, datediff(quarter, 0, getdate()) + 4, 0)
then 'Future'
when datecol >= dateadd(quarter, datediff(quarter, 0, getdate()), 0)
and datecol < dateadd(quarter, datediff(quarter, 0, getdate()) + 4, 0)
then 'Current'
else 'Previous'
end
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jay83091
Starting Member

14 Posts

Posted - 2010-07-01 : 22:51:11

Thanks Mate

you are the best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources