Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inserting a Date range in a case statement? (Tax)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  11:07:58  Show Profile  Reply with Quote
Hello all

I am trying to workout how to insert a date range into a CASE statement.

What I want it to do is show me what the tax percentage should be if a job was pay claim was between a certain date.

So for example, if its between 01-01-2008 and 31-12-2009 then the tax rate is 15%

if its betweeen 01-01-2010 and 31-12-2010 then it should be 17.5%. And so on.

This is my SELECT statement so far, it works but I am not sure how to enter a more than or equal to and less than equal to range:
-----------------------------------
SELECT
payment_job.payment_number,
payment_job.order_job_number,
job.job_notes,
pay_commit_time,
CASE
WHEN pay_commit_time <'2009-12-31 23:59:59.999' THEN '15%'
WHEN pay_commit_time < '3000-01-01 23:59:59.999' THEN '17.5%'
ELSE 'CHECK DATE'
END,
-----------------------------------

Any help would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:11:53  Show Profile  Reply with Quote
i would recommend storing tax rate in a table with effective dates and using it by means of join in your query. this will be easier to maintain whenever tax changes so that its just a matter of insertion/modification into this table rather than changing all queries that has the conditional statement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:15:06  Show Profile  Reply with Quote
table would be like

TaxRateInfo

TaxRate    ValidFrom   ValidTo
15         01-01-2008  31-12-2009
17.5       01-01-2010  31-12-2010

...


and in code use like

SELECT...
t.TaxRate
..
...
INNER JOIN TaxRateInfo t
ON pay_commit_time BETWEEN t.ValidFrom AND t.ValidTo
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  11:20:05  Show Profile  Reply with Quote
Hi Vikash

Thanks for the help. Unfortunately I cannot create a table within this database due to restrictions. Any other way I can search within a date range?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:26:38  Show Profile  Reply with Quote
then build it on the fly

SELECT
payment_job.payment_number,
payment_job.order_job_number,
job.job_notes,
pay_commit_time,
COALESCE(t.TaxRate,'CHECK DATE')
....

LEFT JOIN
(
SELECT '15%' AS TaxRate,CAST('2008-01-01' AS datetime) AS ValidFrom,
CAST('2009-12-31' AS datetime) AS ValidTo
UNION ALL
SELECT '17.5%','2010-01-01','2010-12-31'
)t
ON pay_commit_time BETWEEN t.ValidFrom AND t.ValidTo
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  11:37:26  Show Profile  Reply with Quote
Sorry I tried to get that to work but am formatting it wrong!

This is the entire code I am dealing with, how do I insert that into it?

-----------------------
SELECT
payment_job.payment_number,
payment_job.order_job_number,
job.job_notes,
pay_commit_time,
CASE
WHEN pay_commit_time <'2008-11-30 23:59:59.999' THEN '17.50%'
WHEN pay_commit_time <'2009-12-31 23:59:59.999' THEN '15%'
WHEN pay_commit_time > '2010-01-01 00:00:00.000' THEN '17.5%'
ELSE 'CHECK COMMIT DATE'
END,
payment_batch.pay_commit_login,
(SELECT
sum(payment_item.payment_value)
FROM
payment_item
WHERE
payment_job.contract_code=payment_item.contract_code AND
payment_job.order_number=payment_item.order_number AND
payment_job.order_job_number=payment_item.order_job_number) as Total
FROM
payment_job,
job,
payment_batch
WHERE
payment_job.order_job_number=job.job_number AND
payment_job.contract_code=payment_batch.contract_code AND
payment_job.payment_number=payment_batch.payment_number
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:41:14  Show Profile  Reply with Quote
please use ANSI style of join syntax

SELECT
payment_job.payment_number,
payment_job.order_job_number,
job.job_notes,
pay_commit_time,
COALESCE (t.TaxRate,'CHECK COMMIT DATE'),
payment_batch.pay_commit_login,
(SELECT
sum(payment_item.payment_value)
FROM
payment_item
WHERE
payment_job.contract_code=payment_item.contract_code AND
payment_job.order_number=payment_item.order_number AND
payment_job.order_job_number=payment_item.order_job_number) as Total
FROM
payment_job
inner join job
on payment_job.order_job_number=job.job_number
inner join payment_batch
on payment_job.contract_code=payment_batch.contract_code AND
payment_job.payment_number=payment_batch.payment_number
LEFT JOIN
(
SELECT '15%' AS TaxRate,CAST('2008-01-01' AS datetime) AS ValidFrom,
CAST('2009-12-31' AS datetime) AS ValidTo
UNION ALL
SELECT '17.5%','2010-01-01','2010-12-31'
)t
ON pay_commit_time BETWEEN t.ValidFrom AND t.ValidTo


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  11:47:20  Show Profile  Reply with Quote
Thanks Vikash! :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:49:24  Show Profile  Reply with Quote
quote:
Originally posted by Maverick_

Thanks Vikash! :)


Welcome
b/w I'm Visakh not Vikash

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  11:55:14  Show Profile  Reply with Quote
Sorry Visakh, been looking at code all day! Have a good day

quote:
Originally posted by visakh16

quote:
Originally posted by Maverick_

Thanks Vikash! :)


Welcome
b/w I'm Visakh not Vikash

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  11:58:09  Show Profile  Reply with Quote
quote:
Originally posted by Maverick_

Sorry Visakh, been looking at code all day! Have a good day

quote:
Originally posted by visakh16

quote:
Originally posted by Maverick_

Thanks Vikash! :)


Welcome
b/w I'm Visakh not Vikash

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






No problem
you too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  12:20:52  Show Profile  Reply with Quote
Oh one more thing, if I want to add more years to this code where do I need to add it? And how? (So I don't bug everyone here all the time :))

So for example, I want to do a similar date range for 2007, 2006, 2005 etc. And 2011, 2012 etc for future.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  12:23:43  Show Profile  Reply with Quote
just keep on adding using UNION ALL to inner query that i added with corresponding tax rates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 02/12/2010 :  12:28:40  Show Profile  Reply with Quote
You are a star Visakh, thank you very much I appreciate your help!

Have a good weekend!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/12/2010 :  12:29:39  Show Profile  Reply with Quote
quote:
Originally posted by Maverick_

You are a star Visakh, thank you very much I appreciate your help!

Have a good weekend!


Welcome
You too have a great weekend

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000