CODEWITHSUNDEEP
sqlsql-serversql-server-2008conditional-statements
Hituptony
Hituptony

Reputation: 2860

SELECT NEXT ROW, COMPARE, SET PREVIOUS ROW

So I have a history table called Member_Phys_History in SQL-Server 2008

Looks like this:

RecID, MemberID, Phys_ID, Phys_Start, Phys_End, Phys_Update

The first column is identity, the Phys_Start, Phys_End, and Phys_Update are dates.

I have another table called Member_Phys_Update

MemberID, Phys_ID, Phys_Start_Date

So once a week or so this Update table get's an update from client where The Phys_ID changes and the Phys_start is later in time...So I add this information to my History table and it would look like this:

1|ABC123|555|2014-01-01|NULL|NULL
2|ABC123|556|2014-04-01|NULL|NULL

Here's what I need to do:

I want to basically set the first records Phys_End_Date to the day before the second records Phys_Start_Date. So it would look like this:

1|ABC123|555|2014-01-01|2014-03-30|NULL
2|ABC123|556|2014-04-01|NULL|NULL

I cannot use a stored procedure with a cursor unfortunately, my DBA says it's inefficient. I was wondering if there was any way I could do this in a couple queries...

A cursor may be ideal, but can I do this with a FETCH NEXT or something?

Upvotes: 1

Views: 1838

Answers (3)

Erran Morad
Erran Morad

Reputation: 4743

SQL fiddles -

You will not see all columns of select here. Run it in your sql server. http://sqlfiddle.com/#!3/75dca/2

Limitation - My code works only when Phys_ID (your table) or Code (my table) are consecutive numbers.

Another way do the self join - 

select *
from tblz as t1
inner join tblz as t2
on t1.id = t2.id
and (t1.code = t2.code - 1)

Update using the above query - 

update t1
set t1.EndDate = Dateadd(DAY, -1, t2.StartDate)
--select *
from tblz as t1
inner join tblz as t2
on t1.id = t2.id
and (t1.code = t2.code - 1)

Upvotes: 0

rs.
rs.

Reputation: 27427

Try this

Select A.*, B.Phys_End_Date
from table1 A
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B

SQL DEMO

Edit (Adding Update SQL)

update A
set A.Phys_End_Date = B.Phys_End_Date
from table1 A     
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B

or

INSERT INTO table2 (memberid, phys_id,Phys_Start_Date,Phys_End_Date)
Select A.*, B.Phys_End_Date
from table1 A
outer apply (select (min(Phys_Start_Date) - 1) Phys_End_Date from table1 x
             where x.Phys_Start_Date > A.Phys_Start_Date
             AND X.MemberID = A.MemberID) B;

Upvotes: 4

user275683
user275683

Reputation:

As alternative you can do it using Common Table expression.

;WITH   base
          AS (
               SELECT *
                   ,ROW_NUMBER() OVER ( PARTITION BY MemberID ORDER BY Phys_Start ASC ) AS rn
                FROM Member_Phys_History 
             ),
        nextDate
          AS (
               SELECT *
                   ,ROW_NUMBER() OVER ( PARTITION BY MemberID ORDER BY Phys_Start ASC ) AS rn
                FROM Member_Phys_History 
             )
    SELECT b.RecID
           ,b.MemberID
           ,b.Phys_ID
           ,b.Phys_Start
           ,DATEADD(dd, -1, n.Phys_Start) AS Phy_End
           ,b.Phys_Update
        FROM base AS b
        LEFT OUTER JOIN nextDate AS n
            ON b.MemberID = n.MemberID
               AND b.rn = n.rn - 1;

Than it is really easy to turn it into UPDATE statement

;WITH   base
          AS (
               SELECT *
                   ,ROW_NUMBER() OVER ( PARTITION BY MemberID ORDER BY Phys_Start ASC ) AS rn
                FROM Member_Phys_History 
             ),
        nextDate
          AS (
               SELECT *
                   ,ROW_NUMBER() OVER ( PARTITION BY MemberID ORDER BY Phys_Start ASC ) AS rn
                FROM Member_Phys_History 
             )
    UPDATE b
        SET b.Phys_End = DATEADD(dd, -1, n.Phys_Start)
        FROM base AS b
        LEFT OUTER JOIN nextDate AS n
            ON b.MemberID = n.MemberID
               AND b.rn = n.rn - 1;

Upvotes: 2

Related Questions