MSSQL NULL's Between specific dates

Question

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

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.

Answer 1

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

Answer 2

Your sample doesn't help as there isn't a correlation between your "ColumnXX" and the final columns in your output.

Saying that, I think what you want to do is use a LEFT JOIN between the table with CompanyNames and possibly a derived table that has the monthly summary for companies that transacted that month, joined on Company Name.

This will initially have NULL values where there was no transactions but then simply using ISNULL(, 0) will then give you the required output