Qauterly Data Problem - 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 2000 Forums

Transact-SQL (2000)

Qauterly Data Problem

Author

Topic

stsj
Starting Member

9 Posts

Posted - 2003-04-17 : 09:40:21

Hi all,

I have one table with oper_year and oper_month , those two are int.
Now i want to take the quaterly data from the table as per the user selection like this
If user selects....200001 to 200212 means in that we have 8 quaters...4 in 2001 and 4 in 2002 and if user selects the range from 200101 to 200209 it has 7 quaters..means in 2001..4 Quaters.. and in 2002 --3 Quaters....
i have the following query it is working but if i give the 200101 to 200209 it is shows only 6 quaters...what is the wrong in that if possible please correct it.....

SELECT CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN(oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-'+ Cast(oper_year as varchar)END
,sum(amount)
FROM oper_sundata
where oper_year between 2001 and 2002
and oper_month between 01 and 09
group by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End
order by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 09:47:06

....and why exactly aren't you using real dates? DATEDIFF would make yor life soooooo much easier...Also September is still the 3rd quarter of the year, so the quarter might not yet be completed, I bet if you make it October (since the 3rd quart has past) you'd get your result.

Brett

8-)

stsj
Starting Member

9 Posts

Posted - 2003-04-17 : 10:08:44

thanks Brett ...but see as per my selection of the range it is
from 2001 Jan to 2002September so in this we have Q1,,q2,q3,q4 of 2001 and Q1,Q2,Q3 of 2002..........means that the user selected
from 2001 hole year and 3 quaters of 2002 ....
i am new to this so can u tell me how to use datediff...

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 10:28:55

Do you know Book Online? Comes with the Client install...very helpful, just go the index look up and type datediff...anyway, unless you use datetime datatypes, or convert your data to datetime, then you won't be able to use datediff.

But I think you're problem is that you really haven't left the quarter. I think that if you want to make it think you're IN the quarter, you'll have to round up. The quarter is a quantity and is not complete until it's over..anyway

Here's an example with datediff....it still shows '6', but should be simpler than what you're doing. And should be even easier with DateAdd to round up.

Good Luck



Declare @Year1 varchar(4), @year2 varchar(4), @Month1 varchar(2), @month2 varchar(2), @Date1 Datetime, @Date2 Datetime

select @Year1 = '2001', @Month1 = '01', @Year2 = '2002', @Month2 = '09'

SELECT    CONVERT(datetime,@Year1+'/'+@Month1+'/01')
	, CONVERT(datetime,@Year2+'/'+@Month2+'/30')

SELECT    @Date1 = CONVERT(datetime,@Year1+'/'+@Month1+'/01')
	, @Date2 = CONVERT(datetime,@Year2+'/'+@Month2+'/30')

Select DATEDIFF(qq,@Date1, @Date2)

Brett

8-)

dsdeming

479 Posts

Posted - 2003-04-17 : 14:12:30

I think the reason you're missing a quarter is the line:

and oper_month between 01 and 09

That filters out q4 of 2001, doesn't it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources