CODEWITHSUNDEEP
sqlsql-serversql-server-2019except
Arty155
Arty155

Reputation: 113

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

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

Upvotes: 2

Views: 280

Answers (4)

Papara
Papara

Reputation: 21

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

Upvotes: 2

Stu
Stu

Reputation: 32614

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

Upvotes: 1

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

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

Upvotes: 2

zhiguang
zhiguang

Reputation: 355

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

Upvotes: 0

Related Questions