MySQL, get date where row sum = 3

Question

I want to find the 3rd available appointment from a list of appointments.

MySql database has rows with RotaType, Date and UnusedSlots as columns

I want to do something like

 $sql3 = "SELECT * FROM `UsageDailyUnusedSlots` WHERE Date >= '$Today' AND RotaType LIKE '%Derm%' AND UnusedSlots > 0 AND RotaType NOT LIKE '%- Y%' AND RotaType NOT LIKE '%- y%' ORDER BY Date LIMIT 1 OFFSET 2;";

however this would find me the 3rd day with available slots, not the 3rd slot.

I could do a complicated loop of each row, adding up the value with each iteration/loop and echoing the value when the total >= 3, however I feel there must be a better way.

Here is a scenario

My sql code above would select 31/01/2020 as the 3rd day with UnusedSlots on, however I want it to select 30/01/2020 as the date as that's when the 3rd next unused appointment is

Any thoughts?

Thanks

Sample data and desired results would help. What do YOU mean by "3d available appointment"? — Gordon Linoff, Jan 27 '20 at 15:36
What is RotaType? What type column and also what does it mean in your data? — davidgamero, Jan 27 '20 at 15:42
sample scenario added above for clarity... actually running MariaDB 10.3 — Henry Aspden, Jan 27 '20 at 16:02
Please provide a fiddle, or at least CREATE TABLE + INSERT INTO textual scripts. — Akina, Jan 27 '20 at 19:19

score 0 · Answer 1 · answered Jan 27 '20 at 16:09

WITH cte AS (
    SELECT *,
           SUM(UnusedSlots) OVER (ORDER BY Date) cnt
    FROM `UsageDailyUnusedSlots` 
    WHERE Date >= '$Today' 
    AND RotaType LIKE '%Derm%' 
    AND UnusedSlots > 0 
    AND RotaType NOT LIKE '%- Y%' 
    AND RotaType NOT LIKE '%- y%' )
SELECT *
FROM cte
WHERE cnt >= 3
ORDER BY cnt
LIMIT 1

The Impaler · Answer 2 · 2020-01-27T23:51:13.167

0

You can use windows functions to find the specific location of the slot you want. For example:

select *
from (
  select
    *,
    sum(`UnusedSlots`) over(order by Date 
      rows between unbounded preceding and 1 preceding) as previous_slots,
    sum(`UnusedSlots`) over(order by Date) as current_slots
  from `UsageDailyUnusedSlots`
  where Date >= '2020-01-15' 
    and RotaType like '%Derm%' 
    and RotaType not like '%- Y%' 
) x
where previous_slots < 3 and current_slots >= 3

edited Jan 27 '20 at 23:51

answered Jan 27 '20 at 16:11

The Impaler

45,731
9
39
76

this doesn't quite look like it does the required, although it's along the right lines I feel – Henry Aspden Jan 27 '20 at 19:08
@HenryAspden It works as expected. See SQLFiddle: https://www.db-fiddle.com/f/bqteJ2PN2UgzUD8ceXn4Gx/0 – The Impaler Jan 27 '20 at 23:50

MySQL, get date where row sum = 3

2 Answers2