SQL generate a table, count or function for graph

user_10619507 0
Viewed 33

Firstly I have no code to show (but for good reason). I need a pointer or direction before I try again as I have failed a few times already trying to create a recursive function and so on. Kind of given up and thought I would ask you experts as I am lost and stressed.

My Scenario is this.

Im creating a graph in PHP using Json and thats all fine. However the data I need is my issue.

I have records that have a start and an end date.

Example

ID  14
Start_Date  03/08/2021
End_Date  07/08/2021

Running a stored procedure to grab a records and count between 1 Aug to 10 Aug would display the above as a single record.

Im trying to create a line chart that would have 1 Aug to 2 Aug null then 3 Aug through 7 Aug displaying 1 and finally 8 to 10 Aug null.

1 Aug 2021    0  
2 Aug 2021    0  
3 Aug 2021    1  
4 Aug 2021    1  
5 Aug 2021    1  
6 Aug 2021    1  
7 Aug 2021    1  
8 Aug 2021    0  
9 Aug 2021    0  
10 Aug 2021    0

Is this possible as I have nearly given up.

The nearest I came was using a loop to create a temporary table and inserting records was NOT pretty and certain was embarrassing. If I recreated and posted it here I would die of shame for sure.

So if anyone can point me in the right direction, offer a suggestion or anything like this would be very much appreciated.

Thank you for reading.

recursive-query function sql
1 Answers

You need to start with a list of dates. There are many ways to generate such a list -- perhaps you have an existing table, or your database supports a function. SQL (in general) supports recursive CTEs, which is an alternative method.

Once you have the dates, you can use left join and group by to get the counts you want. Here is an example using MySQL syntax:

with recursive dates as (
      select date('2021-08-01') as dte
      union all
      select dte + interval 1 day
      from dates
      where dte < '2021-08-10'
     )
select d.dte, count(t.id)
from dates d left join
     t
     on d.dte between t.start_date and t.end_date
group by d.dte;

Here is a db<>fiddle.

user_11440350
Related