Inserting a Date range in a case statement? (Tax)

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

Inserting a Date range in a case statement? (Tax)

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 11:07:58

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

52326 Posts

Posted - 2010-02-12 : 11:11:53

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:15:06

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/

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 11:20:05

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:26:38

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/

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 11:37:26

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:41:14

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/

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 11:47:20

Thanks Vikash! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:49:24

quote:
Originally posted by Maverick_

Thanks Vikash! :)

Welcome

b/w I'm Visakh not Vikash

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

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 11:55:14

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:58:09

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/

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 12:20:52

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:23:43

just keep on adding using UNION ALL to inner query that i added with corresponding tax rates

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

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-02-12 : 12:28:40

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

Have a good weekend!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:29:39

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources