Resultsets Diff

Question

Hi

I have below two identical SQL using different sets of tables, given below. How can I get a list of EventIDs where StaffTotal1<>StaffTotal2 or ClientTotal1<>ClientTotal2 or if a matching row (based on EventID) is missing from either of the resulsets?

Thanks

Regards

SELECT        Events1.ID AS EventID, SUM(StaffBookings1.StaffTotal) AS StaffTotal1, SUM(StaffBookings1.Total) AS ClientTotal1
FROM            Events1 INNER JOIN
                         Clients1 ON Events1.Client = Clients1.ID INNER JOIN
                         StaffBookings1 ON Events.ID = StaffBookings1.Event INNER JOIN
                         Staff1 ON StaffBookings1.Staff = Staff1.ID
GROUP BY Events1.ID


SELECT        Events2.ID AS EventID, SUM(StaffBookings2.StaffTotal) AS StaffTotal2, SUM(StaffBookings2.Total) AS ClientTotal2
FROM            Events2 INNER JOIN
                         Clients2 ON Events2.Client = Clients2.ID INNER JOIN
                         StaffBookings2 ON Events.ID = StaffBookings2.Event INNER JOIN
                         Staff2 ON StaffBookings2.Staff = Staff2.ID
GROUP BY Events2.ID

Answer 1

Below is a query for you.

At first you may not recognize anything, but the two CTEs (see Ronen's post above), are actually your original query. I have however reformatted the queries to make them more readable. I've changed the creepy formatting introduced by the Query Designer and introduced aliases to make the query more concise.

I've also commented out two of the tables, because I cannot see that they are actually used in the query, and possibly they could introduce incorrect results. Then again, I don't know your tables, so may be there is a point. But from where I sit, they do look suspicious.

The final query is a full outer join as Ronen discussed. The WHERE clause is a convenient way of finding all differences without having to list each and every column and with all various with NULL etc.

Disclaimer: since I don't have your tables, I have not been able to test.

WITH Query1 AS (
   SELECT  E.ID AS EventID, SUM(SB.StaffTotal) AS StaffTotal1, SUM(SB.Total) AS ClientTotal1
   FROM    Events1 E
--   JOIN    Clients1 C ON E.Client = C.ID 
   JOIN    StaffBookings1 SB ON E.ID = StaffBookings1.Event 
--   JOIN    Staff1 S ON SB.Staff = S.ID
   GROUP BY E.ID
), Query2 AS (        
  SELECT  E.ID AS EventID, SUM(SB.StaffTotal) AS StaffTotal2, SUM(SB.Total) AS ClientTotal2
  FROM    Events2 E
--  JOIN    Clients2 C ON E.Client = C.ID 
  JOIN    StaffBookings2 SB ON E.ID = SB2.Event 
--  JOIN    Staff2 S ON SB2.Staff = S.ID
  GROUP BY Events2.ID
)
SELECT isnull(Q1.EventID, Q2.EventID) AS EventID,
       Q1.StaffTotal1, Q2.StaffTotal2,
       isnull(Q1.StaffTotal1, 0) - isnull(Q2.StaffTotal2, 0) AS DiffStaff,
       Q1.ClientTotal1, Q2.ClientTotal2, 
       isnull(Q1.ClientTotal1, 0) - isnull(Q2.ClientTotal2, 0) AS DiffClient
FROM   Query1 Q1
FULL JOIN Query2 Q2 ON Q1.EventID = Q2.EventID
WHERE NOT EXISTS (SELECT Q1.* INTERSECT SELECT Q2.*)

Answer 2

Hi,

missing from either of the resulsets

Since you are speaking about missing from either of the result sets it means that we need to get data from both queries. This can be done using FULL (OUTER) JOIN between the two result of the queries.

Each query you can wrap as Common_Table_Expression (CTE) and then use the JOIN between the two CTEs

If you need more specific solution then please provide (1) queries to create the relevant table and insert some sample data. (2) In addition please describe the expected result SET which you want to get according to the sample data. (3) Which version of SQL Server are you using - so we will provide solution which fit the version.