http://coldfusion.com logo
Join Slack
Powered by
I'm trying to work out some stats from a single ta...
# sql
n
I'm trying to work out some stats from a single table. i.e, time between appointments etc. Any pointers? Example: https://www.db-fiddle.com/f/91awRABDMGdjqLTFNvSvAJ/0
c
Thought it might involve 
LAG
this looks like it might help - https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows
n
Huh.. never come across LAG() before... interesting - thank you!
c
as long as its available in MySQL - TSQL background here
n
c
ah awesome
never had the chance to use ityet
by 'rates' do you just want to calculate a percentage?
n
Ah, it's MySQL8
Still on 5.7
c
some sort of horrible recursive join ordered by a ranked row number?
they make my head hurt
n
Re: Rates, I think so; basically given 100 appointments, how many appointments are cancelled (so that's easy); It's the "of those cancelled or rebooked appointments, how many go on to book again?" bit...
I'm useless at SQL
c
you could always just pull the data and work it out in CF, sometimes if its going to be SQL you barely understand and have to maintain when the requirements turn on the head thats the simpler solution
n
Yeah it's true... (and kinda tempting) but it feels like cheating 🙂
a
Do you have CTE's in MySQL 5?
or maybe something like:
Copy code
select type, max(appointmentdate), min(appointmentdate), count(type)
from appointments
group by type
so the mean would be the datediff / count per type?
s
Validate it though, think it seems correct?
n
OMG wizardry. I shall have to play with that - thank you 🙂
s
you can mess around with those case statements to do more complex logic if needed, think you mentioned something more complex
3 Views
Open in Slack
PreviousNext
Powered by