Query to find difference between today and yesterday's data along with a pseudo column

Question

There is a table that stores account data for everyday. I want to find out the difference in data between today and yesterday. The query for table creation and insert statements are below :

CREATE TABLE daily_account_data (id varchar(6), Name varchar (20), DS_DW_Id varchar(4), flag_1 varchar(5), flag_2 varchar(5), Insert_date date );

INSERT INTO daily_account_data VALUES('A01R11', 'Gene Graham',      'PT12', 'TRUE', 'FALSE', '2023-06-01');
INSERT INTO daily_account_data VALUES('A01R16', 'Molly Ringwald',   'PT15', 'TRUE', 'TRUE',  '2023-06-01');
INSERT INTO daily_account_data VALUES('A01R19', 'John Doe',         'PT24', 'FALSE','TRUE',  '2023-06-01');
INSERT INTO daily_account_data VALUES('A01R34', 'Jane Doe',         'PT26', 'TRUE', 'FALSE', '2023-06-01');
INSERT INTO daily_account_data VALUES('A01R11', 'Gene Wilder',      'PT12', 'TRUE', 'FALSE', '2023-06-02');
INSERT INTO daily_account_data VALUES('A01R16', 'Molly Ringwald',   'PT15', 'TRUE', 'TRUE',  '2023-06-02');
INSERT INTO daily_account_data VALUES('A01R19', 'John Doe',         'PT24', 'TRUE', 'TRUE',  '2023-06-02');
INSERT INTO daily_account_data VALUES('A01R34', 'Jane Doe',         'PT26', 'TRUE', 'FALSE', '2023-06-02');

I have the query to find the difference in the data of the 2 days.

SELECT id, name, DS_DW_Id, flag_1, flag_2 FROM daily_account_data WHERE Insert_date = '2023-06-02'
EXCEPT
SELECT id, name, DS_DW_Id, flag_1, flag_2 FROM daily_account_data WHERE Insert_date = '2023-06-01';

But I can't figure out to get the data in the pseudo column. The last column is changed data. On 1st June data the name of the id A01R11 is Gene Graham and on 2nd it is Gene Wilder. The pseudo column should display "Name change".

Similarly for id A01R19 (John Doe) the value for flag_1 has changed to TRUE. The pseudo column should display "flag_1 change".

The output should look like :

id	Name	DS_DW_Id	flag_1	flag_2	Data Change
A01R11	Gene Wilder	PT12	TRUE	FALSE	Name Change
A01R19	John Doe	PT24	TRUE	TRUE	flag_1 Change

My code is in the description. Anyways I will type it out here. I can easily find the difference in data of 2 days by : select id, name, DS_DW_Id, flag_1, flag_2 FROM daily_account_data WHERE Insert_date = '2023-06-02' EXCEPT select id, name, DS_DW_Id, flag_1, flag_2 FROM daily_account_data WHERE Insert_date = '2023-06-01'; — Arty155, Jun 03 '23 at 20:30

score 2 · Accepted Answer · answered Jun 03 '23 at 20:56

You can join the table and subtract the date. If the order of the record is correct (the previous day must be the previous record, you can use the window function(LEAD ))

select 
         a.id
        ,a.Name
        ,a.DS_DW_Id
        ,a.flag_1
        ,a.flag_2

        ,iif(a.Name=b.Name ,'',' Name Change') 
        +iif(a.DS_DW_Id=b.DS_DW_Id ,'',' DS_DW_Id Change')
        +iif(a.flag_1=b.flag_1 ,'',' flag_1 Change')
        +iif(a.flag_2=b.flag_2 ,'',' flag_2 Change') AS [Data Change]

from daily_account_data a
inner join(
            select *
            from daily_account_data b
)b on   a.id=b.id 
and DATEADD(day,-1, b.Insert_date)=a.Insert_date
where a.Name<>b.Name 
        or  a.DS_DW_Id<>b.DS_DW_Id   
        or    a.flag_1<>b.flag_1
        or    a.flag_2<>b.flag_2

Thanks! Worked like a charm. I found another way but your seems to be less cost. My approach was - looks like I can't type the query in a comment. Anyways, thanks a lot. — Arty155, Jun 03 '23 at 21:06

Papara · Answer 2 · 2023-06-04T04:19:59.467

Added test rows to see what if everything changed...

/*
INSERT INTO daily_account_data VALUES('A01R99',      'Bruce',            'PT71',              'TRUE',            'FALSE', '2023-06-01');
INSERT INTO daily_account_data VALUES('A01R99',      'Caitlyn',          'PT17',              'FALSE',           'TRUE',  '2023-06-02');
*/
with
daily_entries as (
    select *, row_number() over (partition by id order by Insert_date) entry_id
    from daily_account_data
)
select
    de.id,
    de.insert_date,
    de.name,
    de.ds_dw_id,
    de.flag_1,
    de.flag_2,
    case when de.name <> pe.name then 'name ' else '' end 
    + case when de.ds_dw_id <> pe.ds_dw_id then 'ds_dw_id ' else '' end 
    + case when de.flag_1 <> pe.flag_1 then 'flag_1 ' else '' end 
    + case when de.flag_2 <> pe.flag_2 then 'flag_2 ' else '' end 
    data_chage
from
    daily_entries de
    join daily_entries pe on /*previous entries*/
        de.id = pe.id
        and de.entry_id = pe.entry_id + 1
where
    de.name <> pe.name
    or de.ds_dw_id <> pe.ds_dw_id
    or de.flag_1 <> pe.flag_1
    or de.flag_2 <> pe.flag_2

And got:

ID	INSERT_DATE	NAME	DS_DW_ID	FLAG_1	FLAG_2	DATA_CHAGE
A01R11	2023-06-02	Gene Wilder	PT12	TRUE	FALSE	name
A01R19	2023-06-02	John Doe	PT24	TRUE	TRUE	flag_1
A01R99	2023-06-02	Caitlyn	PT17	FALSE	TRUE	name ds_dw_id flag_1 flag_2

I had not thought of data change in more than 1 column. Thanks for sharing this. I will have to take a closer look at the data now. Honestly, I can't express my gratitude. Thank you very much. — Arty155, Jun 03 '23 at 22:00
This is not valid SQL Server as the question asks for - please correct it. — Dale K, Jun 04 '23 at 00:13
Ok, changed to SQL server :) see fiddle: https://dbfiddle.uk/uCJydRxo — Papara, Jun 04 '23 at 04:20

score 1 · Answer 3 · answered Jun 03 '23 at 20:57

You can use apply to join the previous row and then simply check each required column for equivalence, then concatenate into your new column:

select d.id, d.name, d.DS_DW_Id, d.flag_1, d.flag_2, yd.*
from daily_account_data d
cross apply (
  select Concat(data_change, ' change') data_change
  from daily_account_data x
  cross apply(values(
      Concat_Ws(', ', 
        case when d.name != x.name then 'Name' end,
        case when d.flag_1 != x.flag_1 then 'Flag1' end,
        case when d.flag_2 != x.flag_2 then 'Flag2' end
      )))c(data_Change)
  where x.insert_date = DateAdd(day, -1, d.insert_date) 
    and d.Id = x.Id
    and Data_Change !=''
)yd
where d.Insert_date = '20230602';

Here's a Fiddle demo

score 0 · Answer 4 · answered Jun 03 '23 at 20:51

here is my solution, but only if you have the same user order and number of users for all your data

select * from (select *, case when lead(Name,(select count(Insert_date)/count (distinct Insert_date) from daily_account_data)) over (order by (select null)) != Name then 'Name Change' 
    when lead(DS_DW_Id,(select count(Insert_date)/count (distinct Insert_date) from daily_account_data)) over (order by (select null)) != DS_DW_Id then 'DS_DW_Id Change'
    when lead(flag_1,(select count(Insert_date)/count (distinct Insert_date) from daily_account_data)) over (order by (select null)) != flag_1 then 'flag_1 Change'
    when lead(flag_2,(select count(Insert_date)/count (distinct Insert_date) from daily_account_data)) over (order by (select null)) != flag_2 then 'flag_2 Change' end as test from daily_account_data) as tbl where tbl.test is not null;

fiddle here

Query to find difference between today and yesterday's data along with a pseudo column

4 Answers4