question

andersdl avatar image
andersdl asked

Create a difference query between 2 stored reports

I have reports stored in a SQL table that I report out on using SSRS. I am a NOT a SQL expert. I can do some simple Queries but I can figure how to start a query to do the below without writing procedure and spinning through the 2 sets and creating a 3rd

Data set

dt tag c1 c2

dt1 t1 1 1

dt1 t3 1 2

dt1 t4 2 3

dt2 t1 1 1

dt2 t2 0 1

dt2 t3 1 1

dt2 t4 2 3

report of differences between time stamped records all dt1 (datetime) are the same time all dt2 are the same time but different than dt1 for tags (string) that are the same with any differences in c# columns (strings)

RESULT set

dt tag c1 c2

dt2 t2 0 1 missing from dt1

dt1 t3 1 2 A difference found for t3 in c2

dt2 t3 1 1

reportstored procedure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

I don't expect it to be as simple as this, but it might give you an idea of how to approach this.

I take the set of data that is 'dt1' and see which rows are different from 'dt2' by using the EXCEPT operator. Repeat this for those in 'dt2' that are different from 'dt1', and union the 2 result sets together

declare @DataSet table (dt varchar(10), tag varchar(10), c1 int, c2 int)

insert into @DataSet (dt, tag, c1, c2)
values 
 ('dt1','t1',1,1),
 ('dt1','t3',1,2),
 ('dt1','t4',2,3),
 ('dt2','t1',1,1),
 ('dt2','t2',0,1),
 ('dt2','t3',1,1),
 ('dt2','t4',2,3)


(select tag, c1, c2 from @DataSet where dt = 'dt1'
except
select tag, c1, c2 from @DataSet where dt = 'dt2'
)
union all
(select tag, c1, c2 from @DataSet where dt = 'dt2'
except
select tag, c1, c2 from @DataSet where dt = 'dt1'
)


tag        c1          c2
---------- ----------- -----------
t3         1           2
t2         0           1
t3         1           1

(3 rows affected)


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

andersdl avatar image
andersdl answered

Kev eloquently simple using "except" great to learn something I have not used before. You were correct it was not as simple as that since I do need the DT value in the results The below works but I am sure more efficient ways exist.

declare @DataSet table (dt varchar(10), tag varchar(10), c1 int, c2 int)
insert into @DataSet (dt, tag, c1, c2)
values 
 ('dt1','t1',1,1),
 ('dt1','t3',1,2),
 ('dt1','t4',2,3),
 ('dt1','t5',1,1),
 ('dt2','t1',1,1),
 ('dt2','t2',0,1),
 ('dt2','t3',1,1),
 ('dt2','t4',2,3),
 ('dt3','t5',2,3)
(select a1.dt,a1.tag,a1.c1,a1.c2 from
(select dt,tag,c1,c2  from @dataset a where dt='dt1') as a1,
(select dt,tag,c1,c2  from @dataset b where dt='dt2') as b1
where (a1.tag = b1.tag) and ( (a1.c1 <> b1.c1) or (a1.c2 <> b1.c2)) or (a1.tag not in (select tag  from @dataset  where dt='dt2'))  
)
union
(select b1.dt,b1.tag,b1.c1,b1.c2 from
(select dt,tag,c1,c2  from @dataset a where dt='dt1') as a1,
(select dt,tag,c1,c2  from @dataset b where dt='dt2') as b1
where (a1.tag = b1.tag) and ( (a1.c1 <> b1.c1) or (a1.c2 <> b1.c2)) or (b1.tag not in (select tag  from @dataset  where dt='dt1'))
) order by tag asc,dt asc
dt	tag	c1	c2
-----------------------------
dt2	t2	0	1
dt1	t3	1	2
dt2	t3	1	1
dt1	t5	1	1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

You can add the dt value into the results using the same approach in my other answer

declare @DataSet table (dt varchar(10), tag varchar(10), c1 int, c2 int)

insert into @DataSet (dt, tag, c1, c2)
values 
 ('dt1','t1',1,1),
 ('dt1','t3',1,2),
 ('dt1','t4',2,3),
 ('dt1','t5',1,1),
 ('dt2','t1',1,1),
 ('dt2','t2',0,1),
 ('dt2','t3',1,1),
 ('dt2','t4',2,3),
 ('dt3','t5',2,3)




select 'dt1' as dt, dt1_except_dt2.tag, dt1_except_dt2.c1, dt1_except_dt2.c2
from
    (select tag, c1, c2 from @DataSet where dt = 'dt1'
    except
    select tag, c1, c2 from @DataSet where dt = 'dt2'
    ) dt1_except_dt2
union all
select 'dt2' as dt, dt2_except_dt1.tag, dt2_except_dt1.c1, dt2_except_dt1.c2	
from
    (select tag, c1, c2 from @DataSet where dt = 'dt2'
    except
    select tag, c1, c2 from @DataSet where dt = 'dt1'
    ) dt2_except_dt1


dt   tag        c1          c2
---- ---------- ----------- -----------
dt1  t3         1           2
dt1  t5         1           1
dt2  t2         0           1
dt2  t3         1           1

(4 rows affected)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.