0

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.

  • 1
    show your table schema, sample data & expected result. This will help us to understand your case better – Squirrel Jan 30 at 2:06
  • Do NOT replace real names/aliases with abstract ObjectX/ColumnY. Or at least make sure that the query text and the screenshot of the result match each other. – Akina Jan 30 at 5:12
  • Akina I used sentry one plan explorer to hide info, hence the generic names. Thank you looking at the issue regardless – Stockburn Feb 3 at 21:37
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
| improve this answer | |
  • Hi Akina this is what I have ended up doing, sadly it is still beyond slow but I am looking at rewriting the entire thing as this was something I inherited. Appreciate all your help. – Stockburn Feb 3 at 21:39
0

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

| improve this answer | |
  • Thanks Martin will give that a crack now. – Stockburn Feb 3 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.