All Forums Database
kumar_abhilash 15 posts Joined 04/14
18 Jan 2016
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:
LUCAS 56 posts Joined 06/09
18 Jan 2016

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

You must sign in to leave a comment.