How to include a count of 0 in a Having statement (SSMS)

Databases
1

I only have very basic SQL knowledge, and I am uncertain how to alter an existing script to provide the results I need.

I am trying to do a query that will look to see if the resultant matches are 0 or 1, however it only shows matches of 1. There are matches of 0 which should be showing, but are not. I am guessing that it has to do with either the Having or the Count clause, likely not accounting for a result of 0, as being a match.

Could someone please suggest what I would need to change to achieve what I am looking for.

BR_CTE
   ( MasterPatronId )
As ( Select lt.MasterPatronId
       From LastBRTransactionDate_CTE lt
       Join BRTransaction_CTE          t On t.MasterPatronId = lt.MasterPatronId
                                        And t.TransactionDate Between dbo.FN_Start_of_Day(DateAdd(year, -5, lt.TransactionDate))
                                                                  And dbo.FN_End_of_Day(lt.TransactionDate)
      Where lt.TransactionDate >= @x_Five_Years_Previous
      Group By lt.MasterPatronId
     Having Count(Distinct t.ReportId) < 2 ) -- Check if only has 0 or 1 transactions in last 5 years.
2

You need to change the “JOIN“ (which is an inner join and therefor only returns a row if the patron has an report) to an „LEFT JOIN“ which also returns the patron id if there are no reports on it.

3

I have now tried amending the Join to Left Join, however it is still returning the same results.

4

Below is the full code I am working with.

Here is what I want to accomplish:

List all MasterPatronId’s that have 0 or 1 ReportId’s in the last 5 years, AND where the CurrentRiskLevelCd is one of 4 matching codes. (Note: reports are formed from 1 or more transactions).

Declare @p_End_Date            datetime = dbo.FN_End_of_Day(GetDate())
      , @x_Five_Years_Previous datetime = dbo.FN_Start_of_Day(DateAdd(year, -5, GetDate()))

;
With BRTransaction_CTE
  ( MasterPatronId
  , ReportId
  , TransactionDate )
As ( Select p.MasterPatronId
          , r.ReportId
          , t.TransactionDate
       From Patron_RV       p
       Join Report_RV       r On r.PatronId              = p.PatronId
                             And r.ReportTypeCd         <> 24004 -- Unusual Transaction Report
                             And r.FacilityId           <> 999999
       Join ReportStage_RV rs On rs.ReportId             = r.ReportId
                             And rs.ReportStatusCd      In ( 25011   -- Accepted	
                                                           , 25008 	 -- Awaiting Submission
                                                           , 25009 	 -- Submission In Progress								 
                                                           , 25017 ) -- Recordable
       Join Transaction_RV t On t.ReportId              = r.ReportId )
, LastBRTransactionDate_CTE
   ( MasterPatronId
   , TransactionDate )
As ( Select MasterPatronId
          , Max(TransactionDate)
       From BRTransaction_CTE
      Group By MasterPatronId )
, BR_CTE
   ( MasterPatronId )
As ( Select lt.MasterPatronId
       From LastBRTransactionDate_CTE lt
       Left Join BRTransaction_CTE          t On t.MasterPatronId = lt.MasterPatronId
                                        And t.TransactionDate Between dbo.FN_Start_of_Day(DateAdd(year, -5, lt.TransactionDate))
                                                                  And dbo.FN_End_of_Day(lt.TransactionDate)
      Where lt.TransactionDate >= @x_Five_Years_Previous
      Group By lt.MasterPatronId
     Having Count(Distinct t.ReportId) < 2 ) -- Check if only has 0 or 1 transactions in last 5 years.
Select Distinct p.MasterPatronId
     , Concat( p.Surname
             , ', '
             , p.GivenName
             , IsNull(Space(1) + p.Initial, '') ) As PatronName
     , c1.Name                                    As Gender
     , p.BirthDate
  From Patron_XV                 p
  Join MasterPatron_XV          mp On mp.MasterPatronId      = p.MasterPatronId
                                  And mp.IdLatestPatron      = p.PatronId
                                  And mp.MasterPatronId Not In (40, 743, 13504, 23559)
  Join BR_CTE                   br On br.MasterPatronId      = p.MasterPatronId
  Left Join Patron_Biometric_RV pb On pb.PatronId            = p.PatronId
  Left Join Code_RV             c1 On c1.CodeId              = pb.GenderCd

 Where p.Surname Is Not Null
   And p.Surname Not In ('', 'Unknown')
   And p.CurrentRiskLevelCd In (24101,24102,24103,24104) -- Check if Low, Medium, High, POI
5

TL;DR

to debug a complex query, break it down into small, simple steps

join just two tables, without grouping, and visually inspect the results for correctness

add another join to the query, and inspect again

if everything is still okay, do the grouping/aggregation

we reall cannot tell from looking at a large complex query what your data actually reflects