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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mtl777
Yak Posting Veteran

61 Posts
Posted - 07/18/2012 :  12:57:32  Show Profile  Reply with Quote
I have a table of rate changes for different departments. This is table DeptRates with the following columns: ID int (unique row ID), DeptNo varchar(6), EffectDate smalldatetime, Rate decimal(7,2). Sample data for this table:

ID DeptNo EffectDate Rate
10 000001 03/01/2010 340.00
11 000001 11/15/2010 350.00
12 000001 07/05/2011 360.00
13 000002 05/01/2010 520.00
14 000002 03/15/2011 530.00
15 000002 07/01/2012 540.00

EffectDate is the date that each new rate took effect. I would like to make a query that returns the following record set:

ID DeptNo EffectDate EndingDate Rate
10 000001 03/01/2010 11/14/2010 340.00
11 000001 11/15/2010 07/04/2011 350.00
12 000001 07/05/2011 12/31/9999 360.00
13 000002 05/01/2010 03/14/2011 520.00
14 000002 03/15/2011 06/30/2012 530.00
15 000002 07/01/2012 12/31/9999 540.00

The EndingDate column is calculated as the day before the next EffectDate for the department. If there is no further EffectDate that follows, the EndingDate should be set to 12/31/9999.

I would greatly appreciate if someone could please give me an efficient query for doing this.

Thanks in advance!

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/18/2012 :  13:47:23  Show Profile  Reply with Quote
Assuming ID's are sequential and assuming that EffectDate is of type DATETIME, you can do the following.
SELECT
	a.Id,
	a.DeptNo,
	a.EffectDate,
	COALESCE(DATEADD(dd, -1, b.EffectDate),'99991231') AS EndingDate,
	Rate
FROM
	DeptRates a
	LEFT JOIN DeptRates b ON a.ID+1 = b.ID;
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts
Posted - 07/18/2012 :  13:51:57  Show Profile  Reply with Quote
If id is not sequential:

SELECT
ID, DepNo, effectDate,

COALESCE(
DATEADD(dd, -1,
(select MIN(effectDate) FROM DeptRates T
WHERE T.effectDate > A.effectDate
and A.deptno = T.deptno)
)
,'99991231')
as EndingDate,
Rate
FROM DeptRates A
order by 1
Go to Top of Page

mtl777
Yak Posting Veteran

61 Posts
Posted - 07/18/2012 :  15:54:03  Show Profile  Reply with Quote
Awesome! That was quick. Thanks everyone!

I chose jleitao's solution because the ID is not necessarily sequential.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 07/18/2012 :  16:31:10  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Assuming ID's are sequential and assuming that EffectDate is of type DATETIME, you can do the following.
SELECT
	a.Id,
	a.DeptNo,
	a.EffectDate,
	COALESCE(DATEADD(dd, -1, b.EffectDate),'99991231') AS EndingDate,
	Rate
FROM
	DeptRates a
	LEFT JOIN DeptRates b ON a.ID+1 = b.ID;


shouldnt it require DeptNo comparison also in join condition?

------------------------------------------------------------------------------------------------------
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.06 seconds. Powered By: Snitz Forums 2000