Help -sql query

How to do it in a query ?

IDEffDTMonthFirstDay

162028111

16202812123

16202815567

Expect result:

IDEffDTMonthFirstDayEffDTMonthFirstDay

16202811112 123

1620281212315567

1620281556715567

Thanks

Jessie,

Please post the query you ran to get the unexpected results.

You might be able to join the table to itself, but I can't be sure without more information.

|||

It is not clear what you want to get here. If you want the get on the right hand side EffDT column the next value of the left hand side EffDT column then a query that would do it is:

DECLARE @.MyTable TABLE (ID Int, EffDT Int, MonthFirstDay Int)

INSERT INTO @.MyTable

VALUES (162028, 1, 11)

INSERT INTO @.MyTable

VALUES (162028, 12, 123)

INSERT INTO @.MyTable

VALUES (162028, 15, 567)

SELECT ID, EffDT, MonthFirstDay

,EffDT = IsNULL((SELECT TOP 1 EffDT FROM @.MyTable b WHERE b.ID=a.ID AND b.EffDT>a.EffDT ORDER BY EffDT ASC), EffDT)

,MonthFirstDay = IsNull((SELECT TOP 1 MonthFirstDay FROM @.MyTable b WHERE b.ID=a.ID AND b.EffDT>a.EffDT ORDER BY EffDT ASC), MonthFirstDay)

FROM @.MyTable a