I am trying to summarize data entries by administrative level, let's say districts (in total 30) and day. Now every day data is collected in each district but we are summarizing in real time so at the start there might be some areas that have not started yet and we expect that one or more districts will be done before the others. I don't know the start date and I don't know the end date so these have to be retrieved from the collected data. The table shows about how I would like to have it: Each district should have each day of data collection listed, summing up the number of houses per day and then over the days accumulated. I manage to get the data summed up and accumulated per day and per district but only for those days that have data collected. So the row in bold would be missing in my query result table.
| listing_date | district_id | hh_completed | hh_cum |
|---|---|---|---|
| 2022-03-25 | 11 | 1 | 1 |
| 2022-03-26 | 11 | 3 | 4 |
| 2022-03-27 | 11 | 2 | 6 |
| 2022-03-28 | 11 | 3 | 9 |
| 2022-03-29 | 11 | 1 | 10 |
| 2022-03-25 | 21 | 2 | 2 |
| 2022-03-26 | 21 | 2 | 4 |
| 2022-03-27 | 21 | 2 | 6 |
| 2022-03-28 | 21 | 4 | 10 |
| 2022-03-29 | 21 | 0 | 10 |
I can't wrap my head around how I can make sure that each district has all dates listed.
This post here Iterate through dates in SQL Server query gets pretty close to what I need but I am incapable of applying it on my table.
The data table has the following (relevant) columns (with some sample data):
| district_id | household_no | listing_date |
|---|---|---|
| 11 | 101111 | 2022-03-25 |
| 11 | 101112 | 2022-03-25 |
| 21 | 201112 | 2022-03-25 |
| 21 | 201114 | 2022-03-25 |
| 11 | 101113 | 2022-03-26 |
| 11 | 101114 | 2022-03-26 |
| 31 | 301112 | 2022-03-26 |
| 31 | 301118 | 2022-03-26 |
| 31 | 301119 | 2022-03-27 |
The district table d has all the districts with their district_id (which is of type varchar).
This is what I have so far (already including mustaccio's suggestion):
DECLARE @s DATE=(Select MIN(listing_date) FROM listing_tbl WHERE household_no IS NOT NULL)
DECLARE @e DATE=(Select MAX(listing_date) FROM listing_tbl WHERE household_no IS NOT NULL)
SELECT d.district_id, l.listing_date,
CASE
WHEN l.hh_total>0 THEN l.hh_total
ELSE 0
END as hh_completed,l.hh_cum
FROM DISTRICTS as d
LEFT JOIN
(SELECT district_id,
COUNT(household_no) as hh_total, listing_date,
SUM(COUNT(household_no)) OVER (PARTITION BY district_id ORDER BY listing_date) AS hh_cum
FROM listing_tbl
GROUP BY district_id, listing_date) as l
ON l.district_id=d.district_id
LEFT OUTER JOIN
(SELECT Distinct lx.listing_date FROM listing_tbl lx WHERE lx.listing_date>= @s AND lx.listing_date < DATEADD(DAY, 1, @e)) as i
on l.listing_date=i.listing_date
WHERE
i.listing_date>= @s
AND i.listing_date < DATEADD(DAY, 1, @e)
ORDER BY l.listing_date
When I use the following query I get all dates within the range but starting with NULL dates for those districts that don't have data and NULL data for those dates that don't have any data collected.
DECLARE @s DATE=(Select MIN(listing_date) FROM listing_tbl WHERE household_no IS NOT NULL)
DECLARE @e DATE=(Select MAX(listing_date) FROM listing_tbl WHERE household_no IS NOT NULL)
SELECT Distinct lx.listing_date, i.district_id, i.hh_completed, i.hh_cum FROM listing_tbl lx
FULL OUTER JOIN
(SELECT d.district_id, l.listing_date,
CASE
WHEN l.hh_total>0 THEN l.hh_total
ELSE 0
END as hh_completed,
CASE
WHEN l.hh_cum IS NOT NULL THEN l.hh_cum
ELSE 0
END as hh_cum
FROM DISTRICT_WGS84 as d
FULL OUTER JOIN
(SELECT district_id,
COUNT(household_no) as hh_total, listing_date,
SUM(COUNT(household_no)) OVER (PARTITION BY district_id ORDER BY listing_date) AS hh_cum
FROM listing_tbl WHERE district_id is not null and district_id !='77' and listing_date >= @s AND listing_date < DATEADD(day, 1, @e)
GROUP BY district_id, listing_date) as l
ON l.district_id=d.district_id) as i
ON i.listing_date = lx.listing_date
ORDER BY lx.listing_date
Any help is highly appreciated! To clarify again: I need all districts to be listed at any date within the range. And I think the tricky part is here that I don't have a district that has all dates or a date that has all districts so somehow I will have to create that kind of matrix first I guess and then fill it with the calculated values.
