SQLTeam.com | Weblogs | Forums

Returning Null records in a join query


#1

I am trying to write a query with 2 joins. So far I have the code below, but I know records from table a are not being returned because there is not a record in either table b or c. What do I need to amend so that all records are returned and if b.new_transactioncount is a null return a 0 ect

DECLARE @MONTHBEGINDATE DATETIME = DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)
DECLARE @MONTHLASTDATE DATETIME = DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))
DECLARE @DueDate DATETIME = DATEADD(d , 10, @MONTHLASTDATE)

SELECT DISTINCT(a.accountid), a.[Name] As 'Licence'
,a.[new_companyname] as 'Comapany Name'
,CASE a.[new_service]
WHEN 1 THEN 'WebBacs'
WHEN 2 THEN 'DebBacs'
WHEN 3 THEN 'Facilities Management'
ELSE 'ERROR'
END as 'Service'
,a.[new_paybydirectdebit]
,@MONTHLASTDATE as ' InvoiceDate'
,@DueDate as 'DueDate'
, 0 AS 'InvoiceType'
, 'QuickBooks' AS 'PriceList'
,'account' as 'AccountType'
, ISNULL(CAST(SUM(b.new_transactioncount)as INT), 0) as 'transactioncount'
,ISNULL(CAST(COUNT(b.new_transactioncount)as INT), 0) as 'FileCount'
,a.[new_vatable] AS 'Vatable'
,ISNULL(c.new_annualsupportmonth, 0) AS 'SUPPORTMTH'
,ISNULL(CAST(a.[new_numberoforiginatingaccounts] as int), 0) as 'OrigAcc'
FROM [X_MSCRM].[dbo].[Account] a
LEFT OUTER JOIN [X_MSCRM].[dbo].[new_transactionsets] b
ON a.accountid = b.new_licenceid
LEFT OUTER Join [X_MSCRM].[dbo].[new_tariff] c
ON a.accountid = c.new_licenceid
WHERE a.new_licencestatus IN (4,5,7)
AND b.new_submitteddate BETWEEN @MONTHBEGINDATE AND @MONTHLASTDATE
GROUP BY a.accountid, a.[Name], a.[new_companyname], a.[new_service]
, a.[new_licencestatus] , a.[new_paybydirectdebit] , a.[new_numberoforiginatingaccounts]
, a.[new_vatable] , c.[new_annualsupportmonth]
ORDER BY a.[Name]


#2

That forces the OUTER JOIN to [X_MSCRM].[dbo].[new_transactionsets] to become an "inner join"

You could move that condition to the JOIN instead ? [quote="Pete_N, post:1, topic:10500"]
DECLARE @MONTHLASTDATE DATETIME = DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))
[/quote]

What if a record is dated in the last second of the last day? Don't use BETWEEN for this job:

DECLARE @MONTHLASTDATE DATETIME = DATEADD(month, DATEDIFF(month, 0, getdate())+1, 0)
... 
AND b.new_submitteddate >= @MONTHBEGINDATE 
AND b.new_submitteddate < @MONTHLASTDATE -- << Less-Than One-past the end point

but @MONTHLASTDATE as ' InvoiceDate' would need some attention


#3

Probably nothing, but I'm suspicious that you have parenthesis around (a.accountid) as though DISTINCT can be used as a function ?