0

I have a query which I then usu to populate a graph to give a graphical representation of number of jobs booked per day

SELECT COUNT(DateBooked) AS jobs, DATE_FORMAT(DateBooked, '%d-%M') AS date,  '#00b300' as colour 
FROM Table1
WHERE DATE_FORMAT(DateBooked, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') AND JobStatus != 'Quoted' and JobStatus != 'Archived' and JobStatus != 'Sourcing'
GROUP BY DateBooked
Order By DateBooked ASC

Works great and shows me how many jobs have been booked.

The thing it doesnt show me is days where no jobs have been booked. Ideally, I would like the output to display all dates up to and including the current date even if no jobs have been booked.

The part I am struggling with, is... if no job has been booked on a day then that days date will not be in the table

For example is table1 looked like

id DateBooked Job Details
1 01/04/2023 .....
2 01/04/2023 .....
3 02/04/2023 .....
4 04/04/2023 .....
5 04/04/2023 .....
6 04/04/2023 .....

I would like the query to return

DateBooked COUNT(DateBooked
01/04/2023 2
02/04/2023 1
03/04/2023 0
04/04/2023 3

04/04/2023 was not listed in Table1 but still appears in the output because it falls within the date sequence

Is there a way to achieve this?

2 Answers 2

0

MariaDB has a simple way to build a list of the desired dates. https://mariadb.com/kb/en/sequence-storage-engine/

It would go something like this for the current month.

SELECT ...
    FROM seq_1_to_31
    LEFT JOIN Table1
      ON CONCAT(LEFT(CURDATE(), 7), '-', seq) = Table1.DateBooked
    WHERE DateBooked > LEFT(CURDATE(), 7)

For last month, change CURDATE() to CURDATE() - INTERVAL 1 MONTH .

0

In MariaDB you can use recursive common table expressions to generate a list of dates. The following example generate missing dates for every month.

Query

with recursive all_dates(dates) as (
    select date_add(date_add(LAST_DAY(current_date()),interval 1 DAY),interval -1 MONTH) dates
    union all 
    select dates + interval 1 day from all_dates where dates < LAST_DAY(CURRENT_DATE())
 )
select * from all_dates order by dates

The table refreshes at the start of each month, so it only shows data for the current month

Change the where condition to

WHERE DateBooked >= date_add(date_add(LAST_DAY(current_date()),interval 1 DAY),interval -1 MONTH) and DateBooked <= LAST_DAY(CURRENT_DATE())

Change those repeating AND conditions to

AND JobStatus not in ('Quoted','Archived','Sourcing')

Final query

with recursive fill_dates(dates) as (
        select date_add(date_add(LAST_DAY(current_date()),interval 1 DAY),interval -1 MONTH) dates
        union all 
        select dates + interval 1 day from fill_dates where dates <  LAST_DAY(CURRENT_DATE())
    )
    select DATE_FORMAT(dates, '%d-%M') AS date_,
           COUNT(DateBooked) AS jobs 
    FROM fill_dates 
    LEFT JOIN table1 on fill_dates.dates=table1.DateBooked and JobStatus not in ('Quoted','Archived','Sourcing')
    GROUP BY date_

Consider the following data examples which can be found here as well

create table table1(
  id int,
  DateBooked date ,
  JobDetails varchar(25),
  JobStatus varchar(25) );

insert into table1 (id,DateBooked,JobStatus) values
(1,'2023-04-01','Live'),
(2,'2023-04-01','Live'),
(3,'2023-04-02','Live'),
(4,'2023-04-04','Test'),
(5,'2023-04-04','Quoted'),
(6,'2023-04-04','Quoted'),
(7,'2023-05-02','Live');

Result

date_ jobs
01-April  2
02-April  1
03-April  0
04-April  1
05-April  0
06-April  0
07-April  0
08-April  0
09-April  0
10-April  0
11-April  0
12-April  0
13-April  0
14-April  0
15-April  0
16-April  0
17-April  0
18-April  0
19-April  0
20-April  0
21-April  0
22-April  0
23-April  0
24-April  0
25-April  0
26-April  0
27-April  0
28-April  0
29-April  0
30-April  0
7
  • The table refreshes at the start of each month, so it only shows data for the current month. Would this still work for that case? Or would I need to generate a new list of dates each month?
    – PaulMcF87
    Apr 21, 2023 at 14:59
  • Thanks a lot, Ill give that a try
    – PaulMcF87
    Apr 21, 2023 at 15:17
  • Im not getting the expected outcome What I am getting is |affectedRows| |---------------| | 30 |
    – PaulMcF87
    Apr 21, 2023 at 15:26
  • The filter DATE_FORMAT(DateBooked, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') is used because that shows only entries in the current month, without. From the table it pulls only data which matched the current Month & Year. Without that filter it was giving me outputs from all months
    – PaulMcF87
    Apr 21, 2023 at 15:34
  • Why would it return affectedRows if it was not an update query. thats given me a slight fear
    – PaulMcF87
    Apr 21, 2023 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.