| Author |
Message |
mrstacy
Newbie
Joined: 04 Oct 2012
Online Status: Offline
Posts: 9
|
  
Topic: Cumulative Days With Multiple Begin End Dates
Posted: 04 Oct 2012 at 5:00am |
|
|
I use Crystal Reports 11.
What I'd like to do is get a count of the unique days a patient was enrolled in one of our many programs. if a client was enrolled in 3 programs in which the dates overlapped, i'd just want to count each day once and get a number.
Example using a student:
Algebra Jan 1 to Jan 10: 10 days Science Jan 4 to Jan 11: 8 days English Jan 9 to Jan 13: 4 days
I'd want the answer to be 13. |
|
 IP Logged |
|
|
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 04 Oct 2012 at 11:49pm |
Hi In this case need to find the difference between start date and end date. Use the following function to find between days. datediff("d", {StartDate},{EndDate})+1 or You can directly subtract start date from end date.
|
|
Thanks,
Sastry
|
| IP Logged |
|
mrstacy
Newbie
Joined: 04 Oct 2012
Online Status: Offline
Posts: 9
|
Posted: 05 Oct 2012 at 7:36am |
My example was too easy. There will often be gaps in the dates.
New example:
Algebra Jan 1 to Jan 4: 4 days Science Jan 7 to Jan 11: 5 days English Jan 9 to Jan 13: 4 days ---> 11 days total
|
| IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 06 Oct 2012 at 2:40am |
Hope this is possible by using distinctcount of dates
|
|
Thanks,
Sastry
|
| IP Logged |
|
mrstacy
Newbie
Joined: 04 Oct 2012
Online Status: Offline
Posts: 9
|
Posted: 08 Oct 2012 at 4:42am |
|
That sounds intriguing. how would the code look though?
|
| IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 08 Oct 2012 at 6:37pm |
HI Insert group on student and distinct count the days. Ex : Distinctcount({Table.DateField},{Student}) Place the above formula in student group footer.
|
|
Thanks,
Sastry
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Oct 2012 at 4:21am |
if you can create a calendar table and join your enrollment table to it so you can get one row per day then the distinct count woud be the way to go. In may ways this would be the easiest solution.
Otherwise I am not seeing a very good solution.
|
| IP Logged |
|
mrstacy
Newbie
Joined: 04 Oct 2012
Online Status: Offline
Posts: 9
|
Posted: 09 Oct 2012 at 4:45am |
|
This is what i was thinking to, but how to link to enrollment table when i only have a beginning and end date in that table.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Oct 2012 at 4:53am |
use 2 links
enrollment.startdate<=calender.date and enrollment.enddate>=calendar.date
|
| IP Logged |
|
mrstacy
Newbie
Joined: 04 Oct 2012
Online Status: Offline
Posts: 9
|
Posted: 09 Oct 2012 at 5:33am |
Yes DBlank. That will work will. I didn't know you could link like that. Using greater than and less than links it works.
Put calendar date as first field and studentid as next field. Then add a crosstab in the footer with studentid as row and do a distinct count on calendar date.
i had almost given up. thanks so much!
|
| IP Logged |
|
|
|
