Case in where clause(Sql Svr 2000)

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

General SQL Server Forums

New to SQL Server Programming

Case in where clause(Sql Svr 2000)

Author

Topic

gem1204
Starting Member

9 Posts

Posted - 2011-03-14 : 12:17:54

I need to show a report that shows stats based on the current quarter and also the previous quarter. If its 1st quarter then I need to show the 1st quarter of the current year and the 4th Quarter of the previous year. I found a couple of examples but they all use a case example that involves using an example using an equal eveluation which won't work for me. If the quarter is anything other than 1st quarter all I have to do is show the current year but if its first quarter then I have to show the current year and last year.

This is the what I need to do

	
If the qtr is 1 then
	show 1st Quarter this year and and 4th quarter last year
	Esle
	Show Current Quarter and previous qtr of current year

I found a couple of examples but I can get any of them to work. I was just trying to get code to figure out the year first and couldn't even get that to work. Any ideas on this?
This is one of the things I tried in query analyzer using my own data I tried but had errors on line 9 near '='


Declare @MainYear as integer
Declare @MainQtr as Integer
Set  @MainYear = 2011
Set @MainQtr =1
SELECT DISTINCT CompanyFYYear, CompanyFYQuarter
FROM         dbo.tblCmpyFiscalYears

	WHERE  
	Case @MainQtr 
		when <>1 then CompanyFYYear = @MainYear
		else
		CompanyFYYear Between @MainYear and @MainYear - 1
	end

Can someone help me out on this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 12:33:41

Does your table have any dates, or is it all just years and quarters as in your example?

Jim

Everyday I learn something that somebody else already knew

gem1204
Starting Member

9 Posts

Posted - 2011-03-14 : 12:37:29

It has a date field, fiscal year (2011,2010 etc) and quarter (1,2 3 4). I using distinct to show only unique years and quarters

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 12:41:36

This is good news. Can you give some examples of the values in the date field?

Thanks,

Jim

Everyday I learn something that somebody else already knew

gem1204
Starting Member

9 Posts

Posted - 2011-03-14 : 12:54:48

The table has dates from 01/01/2001 - 01/01/2020 and has every day of every year listed.
This is a sample of the data
Date CompanyFYYear CompanyFYQuarter
01/01/2011 2011 3
01/02/2011 2011 3
01/03/2011 2011 3

[Date] field is datetime and is the primar key
CompanyFYYear and CompanyFYQuarter fields are integers

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 16:08:35

There's a better way to do this. I'll work on it some more and figure it out.

SET DATEFORMAT dmy

DECLARE @Table TABLE (Date datetime,CompanyFYYear int ,CompanyFTQuarter tinyint)
INSERT INTO @table
SELECT '01/01/2011', 2011, 3 UNION ALL
SELECT '01/02/2011', 2011 ,3 UNION ALL
SELECT '01/03/2011', 2011, 3 UNION ALL
SELECT '01/10/2010', 2011, 2 UNION ALL
SELECT '01/11/2010', 2011 ,2 UNION ALL
SELECT '01/12/2010', 2011, 2 UNION ALL
SELECT '01/7/2010', 2011, 1 UNION ALL
SELECT '01/8/2010', 2011 ,1 UNION ALL
SELECT '01/9/2010', 2011, 1 UNION ALL
SELECT '01/04/2011', 2011, 4 UNION ALL
SELECT '01/05/2011', 2011 ,4 UNION ALL
SELECT '01/06/2011', 2011, 4 UNION ALL
SELECT '01/4/2010', 2010, 4 UNION ALL
SELECT '01/5/2010', 2010 ,4 UNION ALL
SELECT '01/6/2010', 2010, 4

DECLARE @Qtr tinyint
SET @qtr = 1
DECLARE @year int
SET @year = 2011

DECLARE @tgtMinDate datetime
DECLARE @tgtmaxDate datetime
SET @tgtMinDate =
(select min(date)
from @table
where CompanyFTQuarter = @qtr
and CompanyFYYear = @year
)
SET @tgtMinDate = DATEADD(quarter,-1,@tgtMinDate)
SET @tgtMaxDate =
(select max(date)
from @table
where CompanyFTQuarter = @qtr
and CompanyFYYear = @year
)

SELECT *
FROM @table t1
WHERE Date between @tgtMinDate and @tgtmaxDate

Jim

Everyday I learn something that somebody else already knew

gem1204
Starting Member

9 Posts

Posted - 2011-03-14 : 16:38:50

You definetly know more than I do! I copied your code and ran it against my table and got 04/01/2010 to 09/30/2010. I may have done something wrong when I tried to create the code. Our fiscal year goes from July 1 to June 30
(Then only thing I had to change was CompanyFTQuarter to CompanyFYQuarter - a typo I guess)

Here's what I did to get my results
***************************
DECLARE @Qtr tinyint
SET @qtr = 1
DECLARE @year int
SET @year = 2011

DECLARE @tgtMinDate datetime
DECLARE @tgtmaxDate datetime

SET @tgtMinDate =
(select min(date)
from dbo.tblCmpyFiscalYears
where CompanyFYQuarter = @qtr
and CompanyFYYear = @year
)

SET @tgtMinDate = DATEADD(quarter,-1,@tgtMinDate)
SET @tgtMaxDate =
(select max(date)
from dbo.tblCmpyFiscalYears
where CompanyFYQuarter = @qtr
and CompanyFYYear = @year
)

Select *

from dbo.tblCmpyFiscalYears
WHERE Date between @tgtMinDate and @tgtmaxDate
Order by date desc
******************************
I appreciate your help and appreciate what you're doing. I'll keep working on it and I hope you do too - you know a lot more than I do

GEM

gem1204
Starting Member

9 Posts

Posted - 2011-03-14 : 16:50:58

I apologize jimf - it was correct!!!! first fy qtr 2011 would have been Jul aug and Sept 2010 and the previouse qtr would have been april may and june. All I need now is to just select distinct on year and quarter. These fy years are confusing me!

I'll study your code and learn more about how it actually works. I think you were right when you said there had to be a better work. If you think of one, please let me know!
Thanks
GEM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources