Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Complicated SQL question

Posted on 2014-12-25
19
Medium Priority
?
298 Views
Last Modified: 2014-12-29
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..?
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
19 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517926
post a sample data & what you need from this sample

everything is possible :)
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40517928
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?
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517939
save this as a query, say q_Patient_ex

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 

Open in new window


test it by opening the query, or running this

select * from q_Patient_ex

Open in new window


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

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 4

Author Comment

by:bfuchs
ID: 40517941
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
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517944
what is this giving?

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

Open in new window


(combined my solution into one big chunk)
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40517948
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.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40517954
@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
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517975
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

Open in new window

0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517976
add a conditionbefore line 6 as

where @somedate between EffectiveFrom and EffectiveTo
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40517977
@HainKurt,
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

Open in new window

However the results is 5, (while the table attached only has 4 records..and expected results is 3)
Thanks,
Ben
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517980
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

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 40517983
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?
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40517986
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
0
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 40517988
after "from patients as P1"

select count(1) as PatientCount 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
      where [date] between EffectiveFrom and EffectiveTo
) as x
) as y

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 40517998
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 -:)
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40518000
i do like challenges :)
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40518009
Thanks HainKurt,
Great job, As said you like challenges, I will get more for you in the future:)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40518100
Hi HainKurt,
I need some modification on this query, posted another question, if you have a chance, please take a look at the following,
http://www.experts-exchange.com/Database/MS_Access/Q_28586854.html
Thanks
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40521008
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
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question