(T-SQL) How to query an audit table, and find changes between 2 dates

Question

The audit table looks like this:

Audit ID    VendorID     PaymentType     CreateDateUTC
 999        8048         2               2017-10-30-08:84:24
1000        1234         5               2017-10-31-01:17:34
1001        8048         7               2017-10-31-01:17:45
1002        1234         5               2017-10-31-01:17:53
1003        1234         7               2017-10-31-01:18:23
1004        1234         5               2017-11-01-01:18:45

In this example, you can see that say - VendorID 1234 started as PaymentType 5, then had another entry where it's still 5 (the audit table records additional changes not relevant to my query), then it changes to 7, but then back to 5.

Say I'd want to answer the question: 'Between now and date X, these VendorIDs had a change in PaymentType'. A bonus would be - this was the previous PaymentType.

Expected Results:

VendorID  PaymentType  Prev_PaymentType
8048      7            2

So say if I queried between now and 10-31-01:00:00, I'd want it to return VendorID 8048 as having changed (and as a bonus, that it's previous PaymentType was 2), but VendorID 1234 shouldn't show up, since at 2017-10-31-01:00:00 it was a 5, and now is still a 5, despite the intermittent changes.

How would one go about querying the VendorIDs whose payment type changed between 2 dates?

Thanks!

Answer 1

Here is an alternative approach that my prove useful, using OUTER APPLY. Note that the AuditID column is used as a tie-breaker mostly because the sample data does not have datetime values.

SQL Fiddle

CREATE TABLE AuditTable (
      AuditID int
    , VendorID int
    , PaymentType int
    , CreateDateUTC date
);

INSERT INTO AuditTable
      VALUES (999, 8048, 2, '2017-10-30'),
      (1000, 1234, 5, '2017-10-31'),
      (1001, 8048, 7, '2017-10-31'),
      (1002, 1234, 5, '2017-10-31'),
      (1003, 1234, 7, '2017-10-31'),
      (1004, 1234, 5, '2017-11-01');

Query 1:

select
*
from AuditTable a
outer apply (
  select top(1) PaymentType, CreateDateUTC
  from AuditTable t
  where a.VendorID = t.VendorID
  and a.CreateDateUTC >= t.CreateDateUTC
  and a.AuditID > t.AuditID
  order by CreateDateUTC DESC, AuditID DESC
  ) oa (PrevPaymentType, PrevDate)
order by
      vendorid 
    , CreateDateUTC

Results:

| AuditID | VendorID | PaymentType | CreateDateUTC | PrevPaymentType |   PrevDate |
|---------|----------|-------------|---------------|-----------------|------------|
|    1000 |     1234 |           5 |    2017-10-31 |          (null) |     (null) |
|    1002 |     1234 |           5 |    2017-10-31 |               5 | 2017-10-31 |
|    1003 |     1234 |           7 |    2017-10-31 |               5 | 2017-10-31 |
|    1004 |     1234 |           5 |    2017-11-01 |               7 | 2017-10-31 |
|     999 |     8048 |           2 |    2017-10-30 |          (null) |     (null) |
|    1001 |     8048 |           7 |    2017-10-31 |               2 | 2017-10-30 |

Answer 2

Here is a variant without using LEAD() or LAG() but does use ROW_NUMBER and COUNT() OVER().

See this verision work at:SQL Fiddle

CREATE TABLE AuditTable (
      AuditID int
    , VendorID int
    , PaymentType int
    , CreateDateUTC date
);

INSERT INTO AuditTable
      VALUES (999, 8048, 2, '2017-10-30'),
      (1000, 1234, 5, '2017-10-31'),
      (1001, 8048, 7, '2017-10-31'),
      (1002, 1234, 5, '2017-10-31'),
      (1003, 1234, 7, '2017-10-31'),
      (1004, 1234, 5, '2017-11-01');

Query 1:

WITH
      rowz AS (
                  SELECT *
                        , ROW_NUMBER() OVER (PARTITION BY VendorID
                                             ORDER BY CreateDateUTC, AuditID) AS lagno
                  FROM AuditTable
            ),
      cte AS (
                  SELECT *
                        , ROW_NUMBER() OVER (PARTITION BY VendorID, CreateDateUTC
                                             ORDER BY c DESC, span_dt) rn
                  FROM (
                        SELECT  r1.AuditID, r1.VendorID, r1.CreateDateUTC
                              , r1.PaymentType AS prevpaymenttype
                              , r2.PaymentType
                              , COALESCE(r2.CreateDateUTC, CAST(GETDATE() AS date)) span_dt
                              , COUNT(*) OVER (PARTITION BY r1.VendorID, r1.CreateDateUTC, r1.PaymentType) c
                        FROM rowz r1
                        LEFT JOIN rowz r2 ON r1.VendorID = r2.VendorID
                              AND r1.lagno = r2.lagno - 1
                  ) d
            )
SELECT
      AuditID, VendorID, PrevPaymentType, PaymentType, CreateDateUTC
FROM (
      SELECT
            *
      FROM cte
      WHERE ('20171031' BETWEEN CreateDateUTC AND span_dt AND rn = 1)
      OR (CAST(GETDATE() AS date) BETWEEN CreateDateUTC AND span_dt AND rn = 1)
) d
WHERE PaymentType <> PrevPaymentType

Results:

| AuditID | VendorID | PrevPaymentType | PaymentType | CreateDateUTC |
|---------|----------|-----------------|-------------|---------------|
|     999 |     8048 |               2 |           7 |    2017-10-30 |

Answer 3

CREATE TABLE AuditTable (
    AuditID     INT,
    VendorID    INT, 
    PaymentType INT,    
    CreateDateUTC DATE
    );

INSERT INTO AuditTable VALUES
(999 ,        8048,         2,               '2017-10-30'),
(1000,        1234,         5,               '2017-10-31'),
(1001,        8048,         7,               '2017-10-31'),
(1002,        1234,         5,               '2017-10-31'),
(1003,        1234,         7,               '2017-10-31'),
(1004,        1234,         5,               '2017-11-01');

WITH CTE AS (
SELECT *,
       ROW_NUMBER () OVER (PARTITION BY CreateDateUTC ORDER BY PaymentType) AS N1

FROM AuditTable
WHERE CreateDateUTC <= '2017-11-02' AND CreateDateUTC >= '2017-10-01'
    ) ,
    MAXP AS(
            SELECT VendorID, PaymentType, CreateDateUTC  
            FROM CTE
            WHERE N1 = (SELECT MAX(N1) FROM CTE) 
        ) 
    SELECT TOP 1 MAXP.VendorID, MAXP.PaymentType AS PaymentType, CTE.PaymentType AS Prev_PaymentType
    FROM MAXP
        JOIN CTE ON CTE.VendorID = MAXP.VendorID;

Result:

+----------+-------------+------------------+
| VendorID | PaymentType | Prev_PaymentType |
+----------+-------------+------------------+
|     8048 |           7 |                2 |
+----------+-------------+------------------+

Demo