SQL problem

Database
Enthusiast

SQL problem

Hi Team,


I have data like this.

Recommendation Identifier   Audience Identifier  Directory Service Identifier  Start Date    End Date   Surrogate Key Value

-------------------------  --------------------  ----------------------------  ----------  ----------  --------------------

                   12345.                    1.                          101.  2016-01-01  9999-12-31                  101.

                   12345.                    1.                          102.  2016-01-06  9999-12-31                  101.

                   54321.                    5.                          105.  2016-01-10  9999-12-31                  105.

Whenever surrogate key value become same ,we need to close previous record.Closing of records means end_dt should be updated with next start_dt -1 Value

like for recommendation_id=12345 and ds_id=101 end_dt would become ('2016-01-06') -1 which is nothing but '2016-01-05'.

So data required is :-

Recommendation Identifier   Audience Identifier  Directory Service Identifier        Start Date         End Date           Surrogate Key Value

-------------------------             --------------------  ----------------------------       ----------           ----------        ----------------------------

                   12345.                    1.                          101.                               2016-01-01        2016-01-05                  101.

                   12345.                    1.                          102.                               2016-01-06        9999-12-31                  101.

                   54321.                    5.                          105.                               2016-01-10        9999-12-31                  105.

Can any one please provide update on this???

Thanx and Regards,

Abhilash

Tags (1)
1 REPLY
Enthusiast

Re: SQL problem

Hi Abhilash,

please try something like that:

UPDATE T

from yourtable T

, (

select

RecoID, AudID

Start_date,

max(Start_Date) over(partition by RecoID, AudID order by Start_Date rows between 1 following and 1 following) - 1 (date) AS NEXT_1

FROM yourtable

qualify max(Start_date) over(partition by RecoID, AudID order by Start_date rows between 1 following and 1 following) - 1 is not null

) W

SET End_Date = W.NEXT_1

WHERE T.RecoID = W.RecoID and T.AudID = W.AUDID

AND T.Start_Date = W.Start_Date;

Pierre