Where and Case - 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

General SQL Server Forums

New to SQL Server Programming

Where and Case

Author

Topic

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 23:48:21

can someone help me to solve this sql statement?

where
invoiceSum >= 1000
and

-- insert this case inside
--case:
--when @type = 'daily' then invoice_date = today
--when @type = 'monthly' then month(invoice_date) = thisMonth and year--(invoice_date) = thisYear
--when @type = 'yearly' then year(invoice_year) = thisYear

i want to filter the result by giving the user to view for daily, monthly and yearly. And i'm not sure too if the switch-case is suitable or there are other better solution for this. thx

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-15 : 00:07:36

where invoiceSum >= 1000
and (invoice_date = case when @type = 'daily' then dateadd(day, 0, datediff(day, 0,getdate())) end
or datepart(month, invoice_date) = case when @type = 'monthly' then datepart(month,getdate()) end
or datepart(year,invoice_date) = case when @type = 'yearly' then datepart(year,getdate()) end)

--------------------------------------------------
S.Ahamed

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-15 : 00:07:54

Can't use case that way.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-15 : 00:09:31

try some thing like

Where
invoicesum >= 100
and ( ( @type = 'daily' and invoice_date = today ) or
( @type = 'monthly' and month(invoice_date) = month(getdate()) and year(invoice_date) = year(getadate()) ) or
( @type = 'yearly' and year(invoice_year) = year(getadate()) )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-15 : 00:49:38

this should be better in terms of performance


select @thisDay   = dateadd(day,   datediff(day,   0, getdate()), 0),
       @nextDay   = dateadd(day,   datediff(day,   0, getdate()) + 1, 0),
       @thisMonth = dateadd(month, datediff(month, 0, getdate()), 0),
       @nextMonth = dateadd(month, datediff(month, 0, getdate()) + 1, 0),
       @thisYear  = dateadd(year,  datediff(year,  0, getdate()), 0),
       @nextYear  = dateadd(year,  datediff(year,  0, getdate()) + 1, 0)

-- Note the variable above are all datetime data type

where invoicesum >= 1000
and   (
          (@type = 'daily'   and invoice_date >= @thisDay   and invoice_date < @nextDay)
      or  (@type = 'monthly' and invoice_date >= @thisMonth and invoice_date < @nextMonth)
      or  (@type = 'yearly'  and invoice_date >= @thisYear  and invoice_date < @nextYear)
      )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-15 : 00:53:30

this will be much cleaner


declare	@start	datetime,
	@end	datetime

select 	@start = case 	@type
			when 'daily' 	then dateadd(day,   datediff(day,   0, getdate()), 0)
			when 'monthly'	then dateadd(month, datediff(month, 0, getdate()), 0)
			when 'yearly'	then dateadd(year,  datediff(year,  0, getdate()), 0)
			end

select 	@end = case 	@type
			when 'daily' 	then dateadd(day,   datediff(day,   0, getdate()) + 1, 0)
			when 'monthly'	then dateadd(month, datediff(month, 0, getdate()) + 1, 0)
			when 'yearly'	then dateadd(year,  datediff(year,  0, getdate()) + 1, 0)
			end
select . . . 
from   . . .
where  invoicesum >= 1000
and    invoice_date >= @start 
and    invoice_date <  @end

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 08:29:21

go with option #2, that's definitely a great, clean way to do it. also the most efficient.

General rule of thumb: rarely should you use CASE expressions in WHERE conditions.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources