need help with where clause - 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)

need help with where clause

Author

Topic

Tart_SQL
Starting Member

41 Posts

Posted - 2008-03-17 : 21:52:24

Hello,

I have this query that should give me records for some date range. But the problem is it is only giving me just two records instead of all records with the date range. I know the most recent date should be stated last in the where clause but doing so gave me no records like the one below, interchanging it gave me two rows. I dont know what I am doing wrong. Any Help will be appreciated.

DECLARE @startQuarter varchar(20)
DECLARE @finishQuarter varchar(20)

SET @startQuarter = '3Q07'
SET @finishQuarter = '1Q08'

SELECT year, quarter, CAST(DATEPART(Quarter ,createdDate) as varchar(4)) + 'Q' + RIGHT(YEAR(createdDate), 2) as qt_Yr, startQrtDate as FirstDayOfQuater
FROM ......

where startQrtDate between

(
case
when substring(@startQuarter,1,2) = '1Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '01/01/')
when substring(@startQuarter,1,2) = '2Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '04/01/')
when substring(@startQuarter,1,2) = '3Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '07/01/')
when substring(@startQuarter,1,2) = '4Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '09/01/')
end ) and
(
case
when substring(@finishQuarter,1,2) = '1Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '03/31/')
when substring(@finishQuarter,1,2) = '2Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '06/30/')
when substring(@finishQuarter,1,2) = '3Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '09/30/')
when substring(@finishQuarter,1,2) = '4Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '12/31/')
end
)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-18 : 02:09:56

I think you need to convert your case condition datatype to datetime.
For example take case condition when substring(@startQuarter,1,2)='3Q'.Change the case statement to

cast(replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20' + substring(@startQuarter,3,2)),1,2) , '07/01/')as datetime).

Tart_SQL
Starting Member

41 Posts

Posted - 2008-03-18 : 02:21:00

thanks for the insight I will try that, also I had some errors in the quarter dates which I need to fix.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources