Data manipulation - 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

General SQL Server Forums

New to SQL Server Programming

Data manipulation

Author

Topic

_sqlnewbie
Starting Member

2 Posts

Posted - 2007-12-26 : 12:10:46

Hello,

I have to perform some data manipulation based on ID1, ID2 with respect to Effective dates (below represented as effdt)
Source Code:

create table #source (id1 int, id2 int, effdt datetime)
insert into #source values (111, 123, '1/1/2007')
insert into #source values (111, 123, '3/1/2007')
insert into #source values (111, 123, '4/1/2007')
insert into #source values (111, 124, '5/1/2007')
insert into #source values (111, 125, '6/1/2007')
insert into #source values (111, 123, '7/1/2007')

Source Code Data:

ID1 ID2 Effdt
111 123 1/1/2007
111 123 3/1/2007
111 123 4/1/2007
111 124 5/1/2007
111 125 6/1/2007
111 123 7/1/2007

Target Data should look like:

ID1 ID2 Effdt Expdt
111 123 1/1/2007 4/30/2007
111 124 5/1/2007 5/31/2007
111 125 6/1/2007 6/30/2007
111 123 7/1/2007 7/31/2007

The logic is like the min(effdt) as effdt, min(effdt) - 1 as expdt of the next id2 from the source. Nut the problem comes when the same ID2 comes later after a different id2, so just a group by on id1, id2 does not suffice.
If it is the last record then the expdt is that month's end date.
Can someone throw me some light on what kind of logic I should be using to accomplish this.

Thanks in advance.
Greg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 13:05:01

Try this & see if it provides you with desired result(i've not tested this):-

;
With Num_CTE (RowNo,ID1,ID2,EffDate,EndDate) as
(
SELECT ROW_NUMBER() OVER (PARTITION BY tmp.ID1,tmp.ID2,tmp.CompDt ORDER BY Effdt) AS 'RowNo',
       tmp.ID1,
       tmp.ID2,
       tmp.EffDt,
       DATEADD(d,-1,tmp.CompDt)
FROM ( SELECT t1.ID1,t1.ID2,t1.EffDt,t2.CompDt 
       FROM #source  t1
       CROSS APPLY ( SELECT MIN(EffDt) AS 'CompDt'
                     FROM #source  
                     WHERE ID1 = t1.ID1
                     AND ID2 <> t1.ID2
                     AND EffDt > t1.EffDt)t2
      )tmp

)


SELECT * FROM Num_CTE  WHERE RowNo =1

_sqlnewbie
Starting Member

2 Posts

Posted - 2007-12-26 : 15:13:32

Thanks visakh16..
It works. I have never heard/used CROSS APPLY before. I believe its newly added in sql 2005. Thanks once again for introducing me to something new.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources