Help with LAG function in Recursive CTE

Hi guys,
I need help in using LAG function in recursive CTE. I want to get the previous contract last service date for the current contract but having no luch with it.
Below is my code
DECLARE @Contracts TABLE
    (
        ContractId INT PRIMARY KEY ,
        OriginatingContract INT ,
        LastService DATETIME,
        FirstContract INT ,
        Serviced BIT ,
        Expired BIT
    );

INSERT INTO @Contracts
VALUES ( 1, NULL,GETDATE()-4, 1, 1, 1 ) ,
       ( 2, 1,GETDATE()-3, 1, 0, 1 ) ,
       ( 3, 2,GETDATE()-2, 1, 0, 1 ) ,
       ( 4, 3,GETDATE()-1, 1, 0, 0 ) ,
       ( 5, NULL,GETDATE()-4, 5, 0, 1 ) ,
       ( 6, 5,GETDATE()-3, 5, 1, 1 ) ,
       ( 7, 6,GETDATE()-2, 5, 0, 1 ) ,
       ( 8, 7,GETDATE()-1, 5, 0, 0 ) ,
       ( 9, NULL,GETDATE()-4, 9, 0, 1 ) ,
       ( 10, 9,GETDATE()-3, 9, 0, 1 ) ,
       ( 11, 10,GETDATE()-2, 9, 1, 1 ) ,
       ( 12, 11,GETDATE()-1, 9, 0, 0 ) ,
       ( 13, NULL,GETDATE()-4, 13, 0, 1 ) ,
       ( 14, 13,GETDATE()-3, 13, 0, 1 ) ,
       ( 15, 14,GETDATE()-2, 13, 0, 1 ) ,
       ( 16, 15,GETDATE()-1, 13, 0, 1 ) ,
       ( 17, 16,GETDATE()-4, 13, 1, 0 );

WITH Hierarchy
AS ( SELECT A.ContractId ,
            A.OriginatingContract ,
            A.Serviced ,
            A.Expired ,
            A.ContractId AS RootContractID ,
            '\\' + CAST(A.ContractId AS NVARCHAR(MAX)) AS ContractPath ,
            0 AS ContractLevel ,
            A.Serviced AS InPathServiced,
            A.LastService,
            A.LastService as PreviousContractService
     FROM   @Contracts A
     WHERE  A.OriginatingContract IS NULL
     UNION ALL
     SELECT C.ContractId ,
            C.OriginatingContract ,
            C.Serviced ,
            C.Expired ,
            P.RootContractID ,
            P.ContractPath + '\' + CAST(C.ContractId AS NVARCHAR(MAX)) ,
            P.ContractLevel + 1 ,
            CASE 
				WHEN C.Serviced = 1
				THEN
						CAST (1 AS BIT)
				ELSE 
						CAST(P.InPathServiced AS BIT)
			END,
			C.LastService,
			LAG(P.LastService,1,0) Over (Partition by RootContractID Order by (c.LastService)) PreviousContractService 
            --CAST(IIF(C.Serviced = 1, 1, P.InPathServiced) AS BIT)
     FROM   Hierarchy P
            INNER JOIN @Contracts C ON P.ContractId = C.OriginatingContract )
SELECT   H.*
FROM     Hierarchy H
 --WHERE EXPIRED = 0
 ORDER BY H.ContractPath;

Open in new window

The result i am getting back is '1900-01-01 00:00:00.000' where i am expecting to get previous rows date.
My result looks like thisResult set
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Instead of using 0 as the default value, use the column name:
LAG(P.LastService,1,P.LastService)

Open in new window

0
 
PortletPaulfreelancerCommented:
Perform the lag calculation after of the recursion, e.g.

WITH Hierarchy
AS ( SELECT A.ContractId ,
            A.OriginatingContract ,
            A.Serviced ,
            A.Expired ,
            A.ContractId AS RootContractID ,
            '\\' + CAST(A.ContractId AS NVARCHAR(MAX)) AS ContractPath ,
            0 AS ContractLevel ,
            A.Serviced AS InPathServiced,
            A.LastService
     FROM   @Contracts A
     WHERE  A.OriginatingContract IS NULL
     UNION ALL
     SELECT C.ContractId ,
            C.OriginatingContract ,
            C.Serviced ,
            C.Expired ,
            P.RootContractID ,
            P.ContractPath + '\' + CAST(C.ContractId AS NVARCHAR(MAX)) ,
            P.ContractLevel + 1 ,
            CASE 
				WHEN C.Serviced = 1
				THEN
						CAST (1 AS BIT)
				ELSE 
						CAST(P.InPathServiced AS BIT)
			END,
			C.LastService
            --CAST(IIF(C.Serviced = 1, 1, P.InPathServiced) AS BIT)
     FROM   Hierarchy P
            INNER JOIN @Contracts C ON P.ContractId = C.OriginatingContract )
SELECT   
       H.*
     , LAG(H.LastService,1,NULL) Over (Partition by H.RootContractID Order by H.LastService) PreviousContractService 
FROM     Hierarchy H
 --WHERE EXPIRED = 0
 ORDER BY H.ContractPath;

Open in new window

0
 
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot thats what i wanted for.

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.