Days Between Dates -- but day goes from noon to noon (1 Viewer)

[Matty]

Hey there,

I have a table full of data, containing date segments for therapy data over several nights for each patient. I'm trying to figure out the total number of days of usage (they could choose not to use the device for a night or two), but doing a simple DateDiff won't work because there could be several segments each night, i.e. from 9pm to 10pm and then from 10:30pm to 11:45pm.

I was going to just count the number of unique dates to get the total number of days, but I hit another problem. A therapy "day" is considered from Noon to Noon, not Midnight to Midnight like a normal day. So a patient who uses the device from 11:20pm on May 1 till 12:30am on May 2 would still be considered one day of usage, not two as I had calculated initially.

So a therapy day would be from 12:00pm on May 1 to 11:59am on May 2. Does anyone have any ideas how I could count therapy days with data like the following:

[b]ID[/b]     [b]Start Time[/b]       [b]End Time[/b] 

1     May 1 11:20pm     May 2 12:30am
1     May 2 12:35am     May 2 5:15am
1     May 2 10:30pm     May 3 1:00am
1     May 3 1:30am      May 3 7:00am

This example would result in 2 "therapy days" but three "normal days." How could I count the number of "therapy days" from this data?

 

[EMP]

<
I'm trying to figure out the total number of days of usage (they could choose not to use the device for a night or two),
>


If the total number of days should exclude the days of non-usage, you will need VBA code rather than queries, as you need to loop through the records for each patient to find the days of non-usage.

 

[raskew]

This code is not well commented (I'm headed out the door) but it apppears it'll work. The idea was to subtract 12 hours from both start and end dates, which allows you to work with straight date comparisons (midnight to midnight).

Function CountTherapy() As Integer
Dim db As DATABASE
Dim rs As Recordset
Dim strSQL As String
Dim dteHold As Date

Dim n As Integer

Set db = CurrentDb
'subtract 12 hours (720 minutes) from both StartDteTime & EndDteTime
strSQL = "SELECT PatientID, StartDteTime, EndDteTime, DateAdd('n',-720,[StartDteTime]) AS xStart, DateAdd('n',-720,[EndDteTime]) AS xEnd" _
    & " FROM tblTherapyTime" _
    & " ORDER BY PatientID, StartDteTime;"

Set rs = db.OpenRecordset(strSQL)
n = 1
dteHold = CDate(Int(CDbl(rs!xStart)))

Do
   n = n + IIf(CDate(Int(CDbl(rs!xStart))) = dteHold Or CDate(Int(CDbl(rs!xEnd))) = dteHold, 0, 1)
   dteHold = CDate(Int(CDbl(rs!xEnd)))
   rs.MoveNext
Loop While Not rs.EOF
rs.Close
db.Close
Set db = Nothing
CountTherapy = n
End Function

 

[Matty]

Thanx for the suggestions. I'm a coder at heart, so I was going to use code further down the line anyways. I was just trying to see if I could do most of the work in a query or two before I started playing around with recordsets.

I've actually got the total days by taking the minimum Start Date and the maximum End Date and doing a DateDiff on it. So I was going to figure out how many unique days there were between the Min and Max and then subtract that number to get my Non-Usage days. I need all three numbers (Total Days, Usage Days, Non-Usage days).

I'm thinking out loud right now, so I think I'm going to try to subtract 12hrs from the dates and see if I can count the unique days from that. I just drew it out on my trusty whiteboard and it looks like it may give me what I'm looking for.

I'll let ya know.

 

[Matty]

OK, I've figured it out and it seems to work.

I had a query that had grabbed the Start and End Dates in the first place, so I added two new fields into the query: xStart and xEnd. The xStart expression looks like this (it goes at the top of the grid, not at the bottom in the criteria part):

xStart: Format(DateAdd("h",-12[SessionStartTime]),"dd/mm/yyyy")

It takes the original Start Date/Time and subtracts 12 hours from it, and then converts it into just the date (for use in the later VBA function. The xEnd function looks the same, but with End in the place of Start.

Now for the function (modified from raskew's to fit my needs). I wanted to put the total number of Usage Days into a textbox on a form, so this function is behind the form. The query I had made in the first step is called "Sleep - Compliance Separate Sessions."

  Private Function intUsageDays() As Integer
'Loops thru all the Compliance Session records for the DownloadID on the form and keeps a running total of days of usage.
    Dim rstSession As ADODB.Recordset
    Dim strQuery As String
    Dim datCompareDate As Date
    Dim intCount As Integer
    
    intCount = 0
    Set rstSession = New ADODB.Recordset
    strQuery = "SELECT * FROM [Sleep - Compliance Separate Sessions] WHERE [DownloadID]=" & CLng(Me!txtDownloadLookup) & ""
    rstSession.Open strQuery, CurrentProject.Connection, adOpenKeyset
    
    If rstSession.BOF = False And rstSession.EOF = False Then
        datCompareDate = rstSession("xStart")
        intCount = 1
        
        Do While rstSession.EOF = False
'If Adjusted Start Date for current record is greater than the previously saved date, add one to the counter. Then set date comparison variable to
'    Adjusted Start Date of current record
            If rstSession("xStart") > datCompareDate Then
                intCount = intCount + 1
            End If
            datCompareDate = rstSession("xStart")
            
'If Adjusted End Date for current record is greater than the previously saved date, add one to the counter. Then set date comparison variable to
'   Adjusted End Date of current record
            If rstSession("xEnd") > datCompareDate Then
                intCount = intCount + 1
            End If
            datCompareDate = rstSession("xEnd")
            
            rstSession.MoveNext
        Loop
    End If
    
    rstSession.close
    Set rstSession = Nothing
    
    intUsageDays = intCount
End Function

I go thru all the dates from the query (both Start and End) and compares it to the previous date to see if it has changed. If it has, I add a day to my Usage Days counter. The returned value, intUsageDays, is then put into the textbox on the form. Thanx for the help, guys. The subtracting of 12 hours put me on the right track.