SQL Server Forums
Profile | Register | 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)
 New Topic  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
52317 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
52317 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
52317 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
52317 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
52317 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
52317 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
52317 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
52317 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000