T-SQL Query will not return 0 value for a Sum

Question

I have a query where I'm calculating Days of Therapy for medications. I want to have 0 values to show for months that have no data. Currently the query returns no record if the Sum is 0. Can't seem to figure this out. See the Query Below:

If I were to comment out identifiers related to the DOT_ALL table along with the Where Clause I get 60 rows, 1 for each month for the past 5 years. However, otherwise i get only 57 for the drug in the Where Clause since there are not DOTs for Aug 2016, April 2016 and Jan 2015.

Thanks in advance.

----------------------------------------------------------------------------

SELECT
    AMS.[Medication Name]
    , SUM(AMS.DOT) AS DOT
    ,  PD.[Patient Days]
    , PD.[Month_Name]
    , PD.[Fiscal_Month]
    , PD.[Accounting_Year]
    , PD.[Year]

FROM
    DW_PROD.dbo.Patient_Days_By_Month PD 
    Left JOIN [DW_PROD].[dbo].[DOTS_All] AMS ON (PD.Month_Name = AMS.Month AND PD.Year = AMS.Year)

WHERE
    [Medication Name] = 'CEFUROXIME'

GROUP BY
    AMS.[Medication Name]
    , PD.[Patient Days]
    , PD.[Month_Name]
    , PD.[Fiscal_Month]
    , PD.[Accounting_Year]
    , PD.[Year]

ORDER BY
    ACCOUNTING_YEAR
    ,FISCAL_MONTH

Medication Name will be a column of DOTS_All (?), and it is in the WHERE so it will restrict the whole resultset. Try adding it as an additional join condition instead. — Cee McSharpface
– Cee McSharpface, Commented Jan 30, 2017 at 23:32
Use a table for the dates (cte, subquery, hardcoded, whatever) and left join both tables from that. — Malk
– Malk, Commented Jan 30, 2017 at 23:34
This article may help you understand the logical order in which query elements are processed. Since your WHERE clause eliminates some months, and it is processed before the SELECT and, hence, aggregation, you won't get rows for those months. Using a numbers or tally table to provide the desired range of months and OUTER JOIN to add your data will let you achieve the desired result. — HABO
– HABO, Commented Jan 31, 2017 at 1:14

Community · Accepted Answer · 2017-05-23 11:53:08Z

2

This may be the cheapest solution, under the assumption that Patient_Days_By_Month is already some kind of calendar.

SELECT
    'CEFUROXIME' AS [Medication Name],
    SUM(AMS.DOT) AS DOT,
    PD.[Patient Days],
    PD.[Month_Name],
    PD.[Fiscal_Month],
    PD.[Accounting_Year],
    PD.[Year]
FROM DW_PROD.dbo.Patient_Days_By_Month PD 
LEFT JOIN [DW_PROD].[dbo].[DOTS_All] AMS 
    ON PD.Month_Name = AMS.Month 
    AND PD.Year = AMS.Year
    AND [Medication Name] = 'CEFUROXIME'
GROUP BY
    PD.[Patient Days],
    PD.[Month_Name],
    PD.[Fiscal_Month],
    PD.[Accounting_Year],
    PD.[Year]
ORDER BY
    PD.ACCOUNTING_YEAR,
    PD.FISCAL_MONTH

JOIN conditions do not need to refer to columns in other tables, they can as well contain constants.

The medication name was originally restricted in the WHERE clause - that eliminated all non-cefuroxime records from the resultset.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jan 30, 2017 at 23:48

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL Query will not return 0 value for a Sum

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related