CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Jon295087
Jon295087

Reputation: 741

SQL: Count of records by consecutive date, even when no records exist for a date

I'm using SQL Server 2008 R2.

I’m querying a table of Hospital Appointment Slots and trying to return a list of how many appointment slots for a specific doctor are flagged as being booked, grouped by week number/year.

There are some instances of weeks that don’t have any booked appointments yet, but I want the result to list ALL the forthcoming weeks, even where the count of booked appointment slots is zero.

The output I’m looking for is along these lines:

-------------------------------------------
Year | Week Number | Number of Booked Slots
-------------------------------------------
2017 |     48      |        10            
2017 |     49      |         0
2017 |     50      |         4
2017 |     51      |         2
2017 |     52      |         0
2018 |      1      |         5

I understand that a standard select aggregating the results won’t show those weeks where there's a zero count of records, because there’s nothing to return – so I’ve tried to get around this by using a cte to first produce a list of all the forthcoming weeks.

However, try as I might, I can’t get the query to display the zero weeks...

I’ve seen a number of solutions to similar problems to this, but despite experimenting I haven’t been able to apply them to my particular problem (including SQL Count to include zero values)

This is the latest iteration of the query I’ve written so far. 

    WITH CTE_Dates AS
    (
    SELECT DISTINCT Slot_Start_Date AS cte_date
    FROM [Outpatients.vw_OP_Clinic_Slots WITH (NOLOCK)
    )

SELECT 
    DATEPART(year,OPCS.Slot_Start_Date) [Year]
    ,DATEPART(week,OPCS.Slot_Start_Date) [Week Number]
    ,count(OPCS.Slot_Start_Date) [Number of Booked Slots]

FROM 
    Outpatients.vw_OP_Clinic_Slots OPCS WITH (NOLOCK)
    LEFT OUTER JOIN CTE_Dates ON OPCS.Slot_Start_Date=CTE_Dates.cte_date
    LEFT OUTER JOIN Outpatients.vw_OP_Clinics CLIN ON OPCS.Clinic_Code=CLIN.Clinic_Code

WHERE
    OPCS.Slot_Start_Date >= '14/08/2017'
    AND OPCS.Booked_Flag = 'Y'
    AND CLIN.Lead_Healthcare_Professional_Name = 'Dr X'

GROUP BY
    DATEPART(year,OPCS.Slot_Start_Date)
    ,DATEPART(week,OPCS.Slot_Start_Date)

ORDER BY 
    DATEPART(year,OPCS.Slot_Start_Date)asc
    ,DATEPART(week,OPCS.Slot_Start_Date)asc

The result it’s returning me is correct, BUT I just need it to include those weeks in the list where the count is zero.

Please can anyone explain where I’m going wrong? I’m guessing I’m not joining the cte correctly, but I’ve tried both right and left joins which produce the same result. I’ve also tried inverting the query by swopping the above query statement and cte around, but this doesn’t work either.

Appreciate any guidance anyone can suggest.

Upvotes: 3

Views: 274

Answers (2)

Javlon Ismatov
Javlon Ismatov

Reputation: 194

IF OBJECT_ID(N'tempdb..##cal_weeks_temp', N'U') IS NOT NULL   
DROP TABLE ##cal_weeks_temp;  
create table ##cal_weeks_temp (date_of_week date, week_num int)
declare @start_date date 
declare @end_date date 
set @start_date='01/01/2017'
set @end_date='12/31/2018'
while @start_date<@end_date
begin
    set @start_date=dateadd(day,1,@start_date)  
    insert into ##cal_weeks_temp values (@start_date,DATEPART(week,@start_date))
end
select YEAR(t1.date_of_week) 'YEAR',t1.week_num,
sum(case convert(varchar,t2.BookedTime,105) when convert(varchar,t1.date_of_week,105)  then 1 else 0 end) 'count'
 from ##cal_weeks_temp t1
left join  Your_Table t2
on convert(varchar,t2.BookedTime,105)=convert(varchar,t1.date_of_week,105) 
group by YEAR(t1.date_of_week) ,t1.week_num
order by YEAR(t1.date_of_week) ,t1.week_num

Upvotes: 0

Bartosz X
Bartosz X

Reputation: 2798

Just RIGHT JOIN @ListOfWeeks table to the result set you have:

DECLARE @ListOfWeeks TABLE ([Week_No] int, [Year_Number] int);

DECLARE @i tinyint = 1, @y int = 2010;

WHILE @i <= 52 AND @y < 2018
BEGIN
    INSERT INTO @ListOfWeeks([Week_No], [Year_Number]) VALUES (@i, @y);

    IF @i = 52 BEGIN
       SET @i = 0
       SET @y +=1
       END
     SET @i += 1
END

SELECT * FROM @ListOfWeeks

WITH [Your_Part] AS(
SELECT 
    DATEPART(year,OPCS.Slot_Start_Date) [Year]
    ,DATEPART(week,OPCS.Slot_Start_Date) [Week Number]
    ,count(OPCS.Slot_Start_Date) [Number of Booked Slots]

FROM 
    Outpatients.vw_OP_Clinic_Slots OPCS WITH (NOLOCK)
    LEFT OUTER JOIN CTE_Dates ON OPCS.Slot_Start_Date=CTE_Dates.cte_date
    LEFT OUTER JOIN Outpatients.vw_OP_Clinics CLIN ON OPCS.Clinic_Code=CLIN.Clinic_Code

WHERE
    OPCS.Slot_Start_Date >= '14/08/2017'
    AND OPCS.Booked_Flag = 'Y'
    AND CLIN.Lead_Healthcare_Professional_Name = 'Dr X'

GROUP BY
    DATEPART(year,OPCS.Slot_Start_Date)
    ,DATEPART(week,OPCS.Slot_Start_Date)
),
SELECT xxx.[Year_Number], xxx.[Week_No], yp.[Number of Booked Slots]
FROM [Your_Part] yp
RIGHT JOIN @ListOfWeeks xxx ON  yp.[Year] = xxx.[Year_Number] AND yp.[Week Number] = xxx.[Week_No]

Upvotes: 1

Related Questions