I am having a problem because my LAG code, which needs to check multiple columns for a match (against previous row) is so verbose to write (and that worries me that during code DEV or Maintenance there will be a small typo and therefore Bug / Cockup).
However, compared to using a #TEMP table with an IDENTITY and LEFT OUTER JOIN #TEMP to find the ID-1 row, or using a CTE with ROW_NUMBER() and doing a similar LEFT OUTER JOIN, the Query Plan looks to be much MUCH more efficient.
So is there a more concise way to code this?
CREATE TABLE dbo.TEMP_LAG_Test
(
InvoiceNo INT NOT NULL
, SubInvoiceNo INT NOT NULL
, MatterID VARCHAR(20) NOT NULL
, ChargeAmount MONEY NOT NULL
, PRIMARY KEY
(
InvoiceNo
, SubInvoiceNo
, MatterID
, ChargeAmount
)
)
INSERT INTO dbo.TEMP_LAG_Test
VALUES (1234, 1, 'ABC123', 1.23)
, (1234, 1, 'ABC123', 2.34)
, (1234, 1, 'ABC123', 2.43)
, (1234, 1, 'XYZ456', 3.45)
, (1234, 1, 'XYZ456', 4.56)
, (1234, 2, 'ABC123', 5.67)
, (1234, 2, 'ABC123', 6.78)
, (1234, 2, 'XYZ456', 7.89)
, (1234, 2, 'XYZ456', 8.90)
, (1234, 2, 'XYZ456', 8.09)
, (5678, 1, 'ABC123', 9.01) -- A different invoice
Lets say that I just want a True/False value when any of InvoiceNo, SubInvoiceNo, MatterID change. My first attempt is this:
SELECT [NewSubHeading] =
CASE WHEN T.InvoiceNo =
LAG(T.InvoiceNo, 1) OVER(
PARTITION BY T.InvoiceNo, T.SubInvoiceNo, T.MatterID
ORDER BY T.ChargeAmount
)
AND T.SubInvoiceNo =
LAG(T.SubInvoiceNo, 1) OVER(
PARTITION BY T.InvoiceNo, T.SubInvoiceNo, T.MatterID
ORDER BY T.ChargeAmount
)
AND T.MatterID =
LAG(T.MatterID, 1) OVER(
PARTITION BY T.InvoiceNo, T.SubInvoiceNo, T.MatterID
ORDER BY T.ChargeAmount
)
THEN 0 ELSE 1 END
, T.InvoiceNo
, T.SubInvoiceNo
, T.MatterID
, T.ChargeAmount
FROM dbo.TEMP_LAG_Test AS T
ORDER BY T.InvoiceNo, T.SubInvoiceNo, T.MatterID
, T.ChargeAmount -- Tie break
GO
DROP TABLE dbo.TEMP_LAG_Test
GO