Reputation: 409
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
Upvotes: 0
Views: 106
Reputation: 8735
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.
Upvotes: 2