I’m using an old version of MySQL. I cannot upgrade it due to some strong reasons. I’ve a table PatientAppointment with only 2 columns. Here is the table:
+---------------------+-----------------+ | dateOfCheckup | duration | +---------------------+-----------------+ | 2020-05-28 | 30 min | | 2020-05-29 | 30 min | | 2020-05-30 | 1 hour | | 2020-06-03 | 1 hour | | 2020-06-05 | 1 hour 30 min | | 2020-07-21 | 1 hour 30 min | | 2020-07-22 | 1 hour | | 2020-07-28 | 30 min | +---------------------+-----------------+
I want 5 things:
- Create a new column in runtime monthOfCheckup from dateOfCheckup
- Convert duration into integer field as minutes
- Accumulating total for each month (not date)
I was able to achieve these 3 points with this query:
select dateOfCheckup, duration, @cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes, CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m') THEN @cum_sum := @cum_sum + @cur_dur ELSE @cum_sum := @cur_dur END total, @year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup from patient, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables ORDER BY dateOfCheckup
Here is the fiddle for the same.
Now the remaining 2 points are:
- BillElig1 This can be boolean or text, doesn’t matters. I just have to put ‘Y’ if the total for that particular month is >=20
- BillElig2 This can be boolean or text, doesn’t matters. I just have to put ‘Y’ if the total for that particular month is >=40
For these 2 points you can see this fiddle.
Please help me.
Advertisement
Answer
And again use UDV.
SELECT dateOfCheckup, duration, minutes, CASE WHEN @prev_month != monthOfCheckup THEN total >= 20 WHEN @prev_total < 20 THEN 1 ELSE 0 END 99457Elig, CASE WHEN @prev_month != monthOfCheckup THEN total >= 40 WHEN @prev_total < 40 THEN 1 ELSE 0 END 99458Elig, @prev_month := monthOfCheckup monthOfCheckup, @prev_total := total total FROM (select dateOfCheckup, duration, @cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes, CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m') THEN @cum_sum := @cum_sum + @cur_dur ELSE @cum_sum := @cur_dur END total, @year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup from patient, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables ORDER BY dateOfCheckup) subquery, (SELECT @prev_month:=0, @prev_total:=0) variable ORDER BY dateOfCheckup
I use 1
and 0
instead of Y
and NULL
.
Of course, the columns order in output list and ordering are critical again.