Help with Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Query

Author

Topic

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-07-18 : 12:57:32

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-18 : 13:47:23

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;

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-18 : 13:51:57

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

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-07-18 : 15:54:03

Awesome! That was quick. Thanks everyone!

I chose jleitao's solution because the ID is not necessarily sequential.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 16:31:10

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources