Sql-server – MSSQL NULL’s Between specific dates

sql serversql-server-2016t-sql

I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s).

I also show companies that have 0 approved, declined and failed using the line

OR 'datecolumnName' IS NULL

The script is missing companies that HAVE values in 'datecolumnName' but not for the month I am interrogating. I want somehow to say where 'datecolumnName' is NULL OR NULL for 'This Month'

E.G Company ABC didn't transact in DEC but did in NOV so the OR 'datecolumnName' NULL script doesn't bring back any data for Company ABC

(Apologies if this is confusing didn't find it easy to explain)

In response to Squirrels message I have attached the code I am running anonymized

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
DECLARE @startOfPreviousMonth DATETIME
SET @startOfPreviousMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT Object1.Column1 AS CompName, 
       SUM(CASE
               WHEN Object2.Column3 = '00'
               THEN 1
               ELSE 0
           END) AS Column4, 
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 0
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 9)
               THEN 1
               ELSE 0
           END) AS Column5, 
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 'A'
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 'Z')
               THEN 1
               ELSE 0
           END) AS Column6, 
       Object1.Column7
FROM Object3 Object1 WITH(NOLOCK)
     FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
     FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10

                            WHERE Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth) 
                            AND Object2.Column11 < @startOfCurrentMonth
                    OR Object2.Column11 IS NULL

     GROUP BY Object1.Column1, 
         Object1.Column7
ORDER BY Object1.Column1

This is the output

Screen Grab Of Script output

The rows with all '0' means that company has never transacted but if a company has transacted in a different month the row is ignored.

Best Answer

Move the condition from WHERE to JOIN:

FROM Object3 Object1 WITH(NOLOCK)
FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10
                                            AND Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth) 
                                            AND Object2.Column11 < @startOfCurrentMonth
Related Question