Check if two views have the same column values-CodePudding

I try to explain my problem in the simplest way.

I have a table, let's call it Table_A, structured like this:

This Table_A is filled with data once a day.

A second table, named Table_B, structurally identical to the previous one, takes the data in real-time (it is refreshed over and over again a day).

I have to find a way to highlight daily if and which counter (Counter_A, Counter_B, Counter_C) is different between Table_A and Table_B.

A numerical example:

Table_A

ID	Name	Code	Status	Counter_A	Counter_B	Counter_C
01	aaa	971283	online	0	3	0
02	bbb	287301	online	4	2	2
03	ccc	718923	online	5	5	5
04	ddd	789021	online	0	0	0
05	eee	890123	online	1	1	4

Table_B

ID	Name	Code	Status	Counter_A	Counter_B	Counter_C
01	aaa	971283	online	0	3	1
02	bbb	287301	online	0	2	2
03	ccc	718923	online	5	5	5
04	ddd	789021	online	0	0	0
05	eee	890123	online	0	0	2

My idea would be to run a script daily and check if the counters are the same, adding incremental columns to a view_B, so that view_B would be:

View_B ( What I want )

ID	Name	Code	Status	Counter_A	Counter_B	Counter_C	Counter_A_check	Counter_B_check	Counter_C_check
01	aaa	971283	online	0	3	1	0	0	1
02	bbb	287301	online	0	2	2	1	0	0
03	ccc	718923	online	5	5	5	0	0	0
04	ddd	789021	online	0	0	0	0	0	0
05	eee	890123	online	0	0	2	1	1	1

If the data is not the same, then I increase the value by one. In this way I would know in addition to the discrepancy, also for how many days the values have been misaligned. In the example, 1 = one-day misaligned.

it seems to work but I don't know how to implement it in SQL

Currently I have set up the two tables. The View_B and the script are missing.

CodePudding user response：

declare @Table_A Table(ID char(2), Counter_A int, Counter_B int, Counter_C int)
declare @Table_B Table(ID char(2), Counter_A int, Counter_B int, Counter_C int)

insert into @Table_A
select '01', 0, 3, 0
union all
select '02', 4, 2, 2
union all
select '03', 5, 5, 5
union all
select '04', 0, 0, 0
union all
select '05', 1, 1, 4

insert into @Table_B
select '01', 0, 3, 1
union all
select '02', 0, 2, 2
union all
select '03', 5, 5, 5
union all
select '04', 0, 0, 0
union all
select '05', 0, 0, 2

select 
    b.*,
    case when a.Counter_A != b.Counter_A then 1 else 0 end Counter_A_Check,
    case when a.Counter_B != b.Counter_B then 1 else 0 end Counter_B_Check,
    case when a.Counter_C != b.Counter_C then 1 else 0 end Counter_C_Check
from @Table_A a
    join @Table_B b on a.ID = b.ID

CodePudding user response：

You can't use a view in SQL Server in this way. Views can only "store" (not actually store) data derived from other tables. You are asking your view View_B to store historical information that is not available in any table.

What you could do instead is to is to create a view AB_Diff that shows you in which records and in which columns you have differences, like this

CREATE VIEW AB_Diff (IDa, Counter_A_check,  Counter_B_check, Counter_C_check)
AS
SELECT  a.ID, 
        CASE WHEN a.Counter_A <>b.Counter_A THEN 1 ELSE 0 END, 
        CASE WHEN a.Counter_B <>b.Counter_B THEN 1 ELSE 0 END,
        CASE WHEN a.Counter_C <>b.Counter_C THEN 1 ELSE 0 END 
    FROM Table_A AS a
    INNER JOIN Table_B b ON b.ID = a.ID;
GO

Using your data this query SELECT * FROM AB_Diff would return:

IDa	Counter_A_check	Counter_B_check	Counter_C_check
1	0	0	0
2	1	0	0
3	0	0	0
4	0	0	0
5	1	1	1

You could now use this view to create a new table AB_Hist_Diff where you could store the sum of the day-by-day instances of the differences, using a stored procedure that you run daily. If you go that way, I recommend also creating a stored procedure to reset these historical data when useful.

If you still want to have your view View_B, you can create it using Table_B and AB_Hist_Diff as I created AB_Diff using Table_A and Table_B.

Makes sense?