CODEWITHSUNDEEP
sql-servert-sql
Roger Dodger
Roger Dodger

Reputation: 987

TSQL get COUNT of rows that are missing from right table

There was one other SIMILAR answer but it is 2 pages long and my requirement doesn't need that. I have 2 tables, tableA and a tableB, and I need to find the COUNTS of rows that are present in tableA but are not present in tableB OR if update_on in tableB is not today's date.

My tables:

tableA:

release_id      book_name        release_begin_date  
----------------------------------------------------
1122           midsummer          2016-01-01          
1123           fool's errand      2016-06-01          
1124           midsummer          2016-04-01          
1125         fool's errand        2016-08-01

tableB:

release_id   book_name        updated_on
-----------------------------------------
1122         midsummer          2016-08-17 
1123         fool's errand      2016-08-16**

Expected result: Since each book is missing one release id, 1 is count. But in addition fool's errand's existing row in tableB has updated_on date of yesterday and not today, it needs to be counted in count_of_not_updated. 

book_name       count_of_missing  count_of_not_updated
-------------------------------------------------------
midsummer          1                 0
fool's errand      1                 1

Note: Even though fool's errand is present in tableB, I need to show it in count_of_missing because it's updated_on date is yesterday and not today. I know it has to be a combination of a left join and something else, but the kicker here is not only getting the missing rows from left table but at the same time checking if the updated_on table was today's date and if not, count that row in count_of_not_updated.

Upvotes: 1

Views: 179

Answers (2)

neer
neer

Reputation: 4092

Try this

DECLARE @tableA TABLE (release_id  INT, book_name  NVARCHAR(50), release_begin_date  DATETIME)
DECLARE @tableB TABLE (release_id  INT, book_name  NVARCHAR(50), updated_on  DATETIME)

INSERT INTO @tableA
VALUES
(1122,  'midsummer',          '2016-01-01'),          
(1123,  'fool''s errand',      '2016-06-01'),          
(1124,  'midsummer',          '2016-04-01'),          
(1125,  'fool''s errand',      '2016-08-01') 


INSERT INTO @tableB
VALUES
(1122, 'midsummer',      '2016-08-17'), 
(1123, 'fool''s errand',  '2016-08-16')

;WITH TmpTableA
AS
(
    SELECT
        book_name,
        COUNT(1) CountOfTableA
    FROM
        @tableA
    GROUP BY
        book_name
), TmpTableB
AS 
(
    SELECT
        book_name,
        COUNT(1) CountOfTableB,
        SUM(CASE WHEN CONVERT(VARCHAR(11), updated_on, 112) = CONVERT(VARCHAR(11), GETDATE(), 112) THEN 0 ELSE 1 END) count_of_not_updated
    FROM
        @tableB
    GROUP BY
        book_name
)


SELECT
    A.book_name ,
    A.CountOfTableA - ISNULL(B.CountOfTableB, 0) AS count_of_missing,
    ISNULL(B.count_of_not_updated, 0) AS count_of_not_updated
FROM
    TmpTableA A LEFT JOIN 
    TmpTableB B ON A.book_name = B.book_name

Result:

book_name            count_of_missing count_of_not_updated
-------------------- ---------------- --------------------
fool's errand        1                1
midsummer            1                1

Upvotes: 1

TPhe
TPhe

Reputation: 1671

select sum(case when b.release_id is null then 1 else 0 end) as noReleaseID 
     , sum(case when datediff(d, b.release_date, getdate()) > 0 then 1 else 0 end) as releaseDateNotToday
     , a.release_id
from tableA a 
left outer join tableB b on a.release_id = b.release_id
Group by a.release_id

This example uses a sum function on a case statement to add up the instances where the case statement returns true. Note that the current code assumes, as in your example, that you are looking to count all old release dates from table b - more steps would be required if each book has multiple old release dates in table b, and you only want to compare to the most recent release date.

Upvotes: 2

Related Questions