CODEWITHSUNDEEP
mysqlsql
Shanmugam Sundaram
Shanmugam Sundaram

Reputation: 58

MySql - Loop through dates in a month for each row

I have 2 tables. One is Jobs (master) table & other is Allocations (transactions) table. For each Job, I need to print the number of allocations done on each day in a month.

I need to print the number of allocations in the following format.

Link to image

I tried this using while loop in a stored procedure. But it did not help.

BEGIN

SET @start_day = DATE('2018-11-01');
SET @end_day = DATE_ADD(@start_day, INTERVAL 30 DAY);

SELECT 
job_id into @job_id
FROM 
jobs
WHERE
job_post_date BETWEEN @start_day AND @end_day;

WHILE(@start_day < @end_day) DO
 SELECT COUNT(allocation_id) FROM allocations WHERE allocations_job_id = 
@job_id AND allocations_assigned_date = @start_day;
 SET @start_day = DATE_ADD(@start_day, INTERVAL 1 DAY);
END WHILE;

END

Upvotes: 0

Views: 2964

Answers (1)

bi_noob
bi_noob

Reputation: 107

If you can create dynamic sql by looping for all the number of days for the month in question, that should solve your problem. Dynamic sql should look like

select j.job_name,
       sum(case when a.allocations_assigned_date = '2019-01-01' 
                then 1 else 0 end) as "1-JAN-2019",
       sum(case when a.allocations_assigned_date = '2019-01-02' 
                then 1 else 0 end) as "2-JAN-2019",
       sum(case when a.allocations_assigned_date = '2019-01-31' 
                then 1 else 0 end) as "31-JAN-2019"
  from jobs j inner join allocations a 
    on j.job_id = a.allocations_job_id
 group by j.job_name

Upvotes: 1

Related Questions