CODEWITHSUNDEEP
functionsql-server-2012
Emeria
Emeria

Reputation: 168

SQL Server - LAG(field1 + field2) function giving unwanted results

I am trying to write a query that will keep a rolling sum of the 2 fields from the previous record. My current query looks like this:

SELECT [NC_GROUP]
  ,[NC_COVCAT]
  ,[NC_POST_DTE]
  ,[NC_GRP_SIZE]
  ,CASE
        WHEN (rtrim(ltrim([NC_GROUP])) LIKE LAG(rtrim(ltrim([NC_GROUP]))) OVER (order by [NC_GROUP], [NC_COVCAT] ) 
        AND rtrim(ltrim([NC_COVCAT])) LIKE LAG(rtrim(ltrim([NC_COVCAT]))) OVER (order by [NC_GROUP], [NC_COVCAT] ))
        THEN
            LAG([NC_PREV_PD_PREM] + [NC_CURR_PD_PREM]) OVER (ORDER BY [NC_GROUP], [NC_COVCAT])
        ELSE
            [NC_PREV_PD_PREM]
   END [NC_PREV_PD_PREM]
  ,[NC_CURR_PD_PREM]
FROM [DBO].[Database]
order by [NC_GROUP], [NC_COVCAT]

The logic runs through my table seen below, checking if the current record has the same group and category as the previous record, then SET the current [NC_PREV_PD_PREM] to the sum of the last records [NC_PREV_PD_PREM] and [NC_CURR_PD_PREM] values.

The table before

The query I linked above is giving me the following results: The current results - wrong Notice that the query is only adding for the second entry in each [NC_GROUP], [NC_COVCAT] grouping. The records afterwards are not being processed with the same logic. I am unsure of how to proceed.

My desired results are the following picture: The desired results

I am new to the LAG function/OVER clause and I am wondering if I am missing something with it. Any help is much appreciated.

Upvotes: 0

Views: 75

Answers (1)

Sagar
Sagar

Reputation: 275

I think you are not using the Partition by clause and also the order by clause is not correct

You may try this ...

LAG(rtrim(ltrim([NC_GROUP]))) OVER (Partition By [NC_GROUP] order by some column which defines the ordering of records within this partition)

As you are Partitioning on NC_GROUP and NC_COVCAT you will always get the NC_GROUP = Prev_NC_GROUP and [NC_COVCAT]=[Prev_NC_COVCAT]

Try this:

SELECT 
[NC_GROUP],[NC_COVCAT],[NC_POST_DTE],[NC_GRP_SIZE],
Case When [NC_GROUP]=[Prev_NC_GROUP] and [NC_COVCAT]=[Prev_NC_COVCAT] Then [Prev_NC_PREV_PD_PREM]+[Prev_NC_CURR_PD_PREM]
Else [NC_PREV_PD_PREM]
End as [NC_PREV_PD_PREM]
 FROM (
SELECT [NC_GROUP]
      ,[NC_COVCAT]
      ,[NC_POST_DTE]
      ,[NC_GRP_SIZE]
      ,LAG(rtrim(ltrim([NC_GROUP]))) OVER (Partition By [NC_GROUP], [NC_COVCAT] order by [id] ) [Prev_NC_GROUP]
      ,LAG(rtrim(ltrim([NC_COVCAT]))) OVER (Partition By [NC_GROUP], [NC_COVCAT] order by [id] ) [Prev_NC_COVCAT] 
      ,LAG([NC_PREV_PD_PREM]) OVER (Partition By [NC_GROUP], [NC_COVCAT] order by [id]) [Prev_NC_PREV_PD_PREM]
      ,LAG([NC_CURR_PD_PREM]) OVER (Partition By [NC_GROUP], [NC_COVCAT] order by [id]) [Prev_NC_CURR_PD_PREM]
      ,[NC_PREV_PD_PREM]
      ,[NC_CURR_PD_PREM]
FROM [DBO].[Database] ) X

Upvotes: 2

Related Questions