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.
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.
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