-1

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.

New contributor
Nicole is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3

0

Your Answer

Nicole is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.