bfuchs
asked on
Complicated SQL question
Hi Experts,
I have a table containing the following:
ID
PatientID
EffectiveFrom (DateTime) Start of service
EffectiveTo (DateTime) End of service
Now I need to get the total count of patients serviced between a given date range, however (here goes the catch..) if a patient has 30 days or more between the one end of service (EffectiveTo) and the next start of service (EffectiveFrom) it should be counted as 2.
My question is, is there a way to get this count from a query/sql view or there is a need to create a custom function to loop for all records etc..?
I have a table containing the following:
ID
PatientID
EffectiveFrom (DateTime) Start of service
EffectiveTo (DateTime) End of service
Now I need to get the total count of patients serviced between a given date range, however (here goes the catch..) if a patient has 30 days or more between the one end of service (EffectiveTo) and the next start of service (EffectiveFrom) it should be counted as 2.
My question is, is there a way to get this count from a query/sql view or there is a need to create a custom function to loop for all records etc..?
Hi bfuchs,
Are you looking for the number of visits or the number of unique patients? That is, if a patient visits multiple times during that date range, is he counted once or multiple times?
Are you looking for the number of visits or the number of unique patients? That is, if a patient visits multiple times during that date range, is he counted once or multiple times?
save this as a query, say q_Patient_ex
test it by opening the query, or running this
now run this
select PatientID, EffectiveFrom, EffectiveTo,
(select min(EffectiveFrom) from patients as P2 where P2.PatientID=P1.PatientID and P2.EffectiveFrom > P1.EffectiveTo) next_service
from patients as P1
test it by opening the query, or running this
select * from q_Patient_ex
now run this
select sum(iff(datediff(d, EffectiveTo, nz(next_service, EffectiveTo))>30, 2, 1))
from q_Patinets_X
where @somedate between EffectiveFrom and EffectiveTo
group by patientID
ASKER
Hi,
@HainKurt,
Attached sample data, The number of patients I'm expecting to get for year 2004 is 3, as patientID 100 should be counted twice since there were more then 30 days between end od 2nd service and beginning of 3rd.
@Kdo,
Lets put it this way, I need the number of visits allowances (service date ranges), however if there are less then 30 days from when a patients service date ended and started again then its counted as one.
Thanks,
Ben
Book1.xls
@HainKurt,
Attached sample data, The number of patients I'm expecting to get for year 2004 is 3, as patientID 100 should be counted twice since there were more then 30 days between end od 2nd service and beginning of 3rd.
@Kdo,
Lets put it this way, I need the number of visits allowances (service date ranges), however if there are less then 30 days from when a patients service date ended and started again then its counted as one.
Thanks,
Ben
Book1.xls
what is this giving?
(combined my solution into one big chunk)
select sum(iff(datediff(d, EffectiveTo, nz(next_service, EffectiveTo))>30, 2, 1)) as Patient_Count
from (
select PatientID, EffectiveFrom, EffectiveTo,
(select min(EffectiveFrom) from patients as P2 where P2.PatientID=P1.PatientID and P2.EffectiveFrom > P1.EffectiveTo) next_service
from patients as P1
) as q_Patinets_X
where @somedate between EffectiveFrom and EffectiveTo
(combined my solution into one big chunk)
That really needs to be recursive SQL. It could be that the visits chain to a length of 1 or many. When 3 or more visits chain, care must be taken that only the first visit is counted. Recursive SQL does that quite easily, but Access doesn't support it.
ASKER
@HainKurt,
See attached, (just replaced name of table to sheet1 as the one you gave exists).
@Kdo,
How about if I upload my data into a sql table would that help?
Untitled.png
See attached, (just replaced name of table to sheet1 as the one you gave exists).
@Kdo,
How about if I upload my data into a sql table would that help?
Untitled.png
ok, here it is:
select count(1) as PatientCount from (
select distinct PatientID, MoreThan30 from (
select PatientID, iif(datediff('d', EffectiveTo, nz(next_service,EffectiveTo))>30, 1, 0) as MoreThan30 from (
select PatientID, EffectiveFrom, EffectiveTo,
(select min(EffectiveFrom) from patients as P2 where P2.PatientID=P1.PatientID and P2.EffectiveFrom > P1.EffectiveTo) as next_service
from patients as P1
) as x
) as y
) as z
add a conditionbefore line 6 as
where @somedate between EffectiveFrom and EffectiveTo
where @somedate between EffectiveFrom and EffectiveTo
ASKER
@HainKurt,
Actually I fixed your version re the syntax as follows
Thanks,
Ben
Actually I fixed your version re the syntax as follows
SELECT Sum(IIf(DateDiff("d",[EffectiveTo],nz([next_service],[EffectiveTo]))>30,2,1)) AS Patient_Count
FROM (select PatientID, EffectiveFrom, EffectiveTo,
(select min(EffectiveFrom) from sheet1 as P2 where P2.PatientID=P1.PatientID and P2.EffectiveFrom > P1.EffectiveTo) as next_service
from sheet1 as P1
) AS q_Patinets_X
However the results is 5, (while the table attached only has 4 records..and expected results is 3)Thanks,
Ben
or maybe this is better :) previous one has one more redundant select
select count(1) from (
select distinct PatientID, iif(datediff('d', EffectiveTo, nz(next_service,EffectiveTo))>30, 1, 0) as MoreThan30 from (
select PatientID, EffectiveFrom, EffectiveTo,
(select min(EffectiveFrom) from patients as P2 where P2.PatientID=P1.PatientID and P2.EffectiveFrom > P1.EffectiveTo) as next_service
from patients as P1
) as x
) as y
ASKER
Ok your latest seems to work,
where do I add a date range to filter for, for example from 1/1/14 till 12/31/14?
where do I add a date range to filter for, for example from 1/1/14 till 12/31/14?
explanation:
line 3-5: I find result like
PatientID EffectiveFrom EffectiveTo next_service
100 01/01/2014 01/02/2014 15/02/2014
100 15/02/2014 28/02/2014 01/04/2014
100 01/04/2014 02/04/2014 05/05/2014
200 01/01/2014 15/01/2014
100 05/05/2014 15/05/2014
line 2-6: here I find data like
PatientID MoreThan30
100 0
100 1
200 0
line 1-7: here i get result
3
line 3-5: I find result like
PatientID EffectiveFrom EffectiveTo next_service
100 01/01/2014 01/02/2014 15/02/2014
100 15/02/2014 28/02/2014 01/04/2014
100 01/04/2014 02/04/2014 05/05/2014
200 01/01/2014 15/01/2014
100 05/05/2014 15/05/2014
line 2-6: here I find data like
PatientID MoreThan30
100 0
100 1
200 0
line 1-7: here i get result
3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi HainKurt,
It looks like working well, just waiting to get hold of production data in order to finalize..
(I can see from your icon pic how you're already smiling from this big achievement -:)
It looks like working well, just waiting to get hold of production data in order to finalize..
(I can see from your icon pic how you're already smiling from this big achievement -:)
i do like challenges :)
ASKER
Thanks HainKurt,
Great job, As said you like challenges, I will get more for you in the future:)
Great job, As said you like challenges, I will get more for you in the future:)
ASKER
Hi HainKurt,
I need some modification on this query, posted another question, if you have a chance, please take a look at the following,
https://www.experts-exchange.com/questions/28586854/Modify-SQL-Question.html
Thanks
I need some modification on this query, posted another question, if you have a chance, please take a look at the following,
https://www.experts-exchange.com/questions/28586854/Modify-SQL-Question.html
Thanks
ASKER
Hi HainKurt,
I am attaching a file with some data, according to the 30 days rule this should only return one record for year 2013, however your query its returning 2. (tested by running the middle part of your query), please let me know how to correct this.
Thanks,
Ben
Book1.xls
I am attaching a file with some data, according to the 30 days rule this should only return one record for year 2013, however your query its returning 2. (tested by running the middle part of your query), please let me know how to correct this.
Thanks,
Ben
Book1.xls
everything is possible :)