0

I have a query which returns the sum of records in a month for a given practice. I am limiting the range to look at today’s date - 2 years and <= today’s date. I want to show a zero for the records where a service_date doesn’t exist. How can I do this? I have tried left, right, full outer joins but none seem to work.

Query:

SELECT EH.TIN, Dates.Daily_date, DATEADD(m, DATEDIFF(m, 0, Dates.Daily_date), 0),ISNULL(count(ed.detail_key),0) as 'Claim_Detail' 
FROM [dbo].[ENCOUNTER_DETAIL] ED 
FULL OUTER JOIN 
       (
       SELECT TOP(73049) DATEADD(d, -1, ROW_NUMBER() OVER (ORDER BY o.object_id)) AS Daily_Date
       FROM master.sys.objects o, master.sys.objects o1, master.sys.objects o2
       ) Dates On Dates.Daily_Date = ED.Service_Date
FULL OUTER JOIN [dbo].[ENCOUNTER_HEADER] EH with (nolock) ON ED.encounter_key = EH.encounter_key
WHERE ed.data_source_id = '1234' 
AND Dates.Daily_date > DATEADD(month, -24, GETDATE())
AND Dates.Daily_date <= getdate()
group by EH.TIN, Dates.Daily_date, DATEADD(m, DATEDIFF(m, 0, Dates.Daily_date), 0)

For example: My output is

Practice_Name   Year_Month  Claim_Detail
Prac_1  10/1/2017   75
Prac_1  12/1/2017   8

But I would like my output to be: 

Practice_Name   Month_Year  Claim_Detail
Prac_1  5/1/2016    0
Prac_1  6/1/2016    0
Prac_1  7/1/2016    0
Prac_1  8/1/2016    0
Prac_1  9/1/2016    0
Prac_1  10/1/2016   0
Prac_1  11/1/2016   0
Prac_1  12/1/2016   0
Prac_1  1/1/2017    0
Prac_1  2/1/2017    0
Prac_1  3/1/2017    0
Prac_1  4/1/2017    0
Prac_1  5/1/2017    0
Prac_1  6/1/2017    0
Prac_1  7/1/2017    0
Prac_1  8/1/2017    0
Prac_1  9/1/2017    0
Prac_1  10/1/2017   75
Prac_1  12/1/2017   8
Prac_1  1/1/2018    0
Prac_1  2/1/2018    0
Prac_1  3/1/2018    0
Prac_1  4/1/2018    0

Context: The reason I need to update my query is because this data is being pulled by an SSRS report and I want to show the peaks/valleys of this practice where they have not sent records using a Sparkline.

jujulalu
  • 97
  • 1
  • 1
  • 9
  • look at this [answer](https://stackoverflow.com/a/17529962/5308054) – Dzmitry Paliakou May 10 '18 at 16:38
  • The answer you posted looks to grab all dates in a range. I updated my query to include something similar but I still cant get the result I am looking for. – jujulalu May 10 '18 at 18:04
  • you should generate a range of dates and then join it using a construction similar to `RIGHT JOIN range ON Dates.Daily_date = range.Date` – Dzmitry Paliakou May 11 '18 at 10:49
  • In order to get 0 counts for specific months, I had to cross join two tables. One which had a daily range of service_dates from 1950-2015. The other table had the Practice_Name and TIN. I had to throw out my initial query in the question and had to start all over. Thanks for the help though. – jujulalu May 21 '18 at 20:07

0 Answers0