Date range question

G

Guest

I have database with medicare patients that needs to determine the length of
stay and number of patients by month. The data looks like this:
Pt# Start dt End dt
123 1/4/06 3/31/06
456 1/6/06 4/11/06
676 5/1/06 5/10/06

Whenever a patients stay is longer than a month need to count that patients
days in that month.
What the result should be is this:
Month #Patients Length of stay
Jan 06 2 52 (days) - both patients days to the end
of the month
Feb 06 2 56 (days)
Mar 06 2 62 (days)
Apr 06 1 11 (days)
May 06 1 10 (days)

Any suggestions ?
 
G

Guest

I think this will do it. I used three queries.

SELECT William.[Pt#], William.[Start dt], William.[End dt],
CVDate(Format(DateAdd("m",[CountNUM],[Start dt]),"yyyy/mm/""01""")) AS [Month
of stay]
FROM William, CountNumber
WHERE (((DateAdd("m",[CountNUM],[Start dt]))<=[End dt]))
ORDER BY William.[Pt#], DateAdd("m",[CountNUM],[Start dt]);

SELECT Q.[Pt#], Q.[Start dt], Q.[End dt], Q.[Month of stay], (SELECT
COUNT(*) FROM [William-1] Q1
WHERE Q1.[Pt#] = Q.[Pt#]
AND Q1.[Month of stay] < Q.[Month of stay])+1 AS [Month]
FROM [William-1] AS Q
ORDER BY Q.[Pt#], Q.[Month of stay];

SELECT [William-2].[Pt#], [William-2].[Start dt], [William-2].[End dt],
[William-2].[Month of stay], [William-2].Month,
IIf([Month]=1,DateDiff("d",[Start dt],IIf([End dt]<DateAdd("m",1,[Month of
stay])-1,[End dt],DateAdd("m",1,[Month of stay])-1)),IIf([End
dt]<DateAdd("m",1,[Month of stay])-1,DateDiff("d",[Month of stay],[End
dt]),DateDiff("d",[Month of stay],DateAdd("m",1,[Month of stay])))) AS [Days
stay]
FROM [William-2];
 
G

Guest

Karl,

Thank you for the quick response. I will try that and see what happens.
Again Thank you.
 
D

Dale Fye

William,

Are you counting the day they leave/arrive, it does not look like it. There
are 31 days in January, so a patient that arrives on January 4 and doesn't
leave until March 31st would be on site for 28 days, not 27.

I've got a slightly different technique than Karl,
1. Create a new table (I call it tbl_Numbers), with one field [Number]
which is defined as a long integer. Fill this table with the numbers 1-12
(for the months).

2. Create a couple of functions. These will be used in the query to
determine the number of days the patient is in the hospital during a given
month. These are functions I created a while ago to give me the minimum and
maximum from among a group of values passed to the function. The nice thing
is that they work just as well for dates as it does for numbers or strings.

Public Function Minimum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Minimum) Then
Minimum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) < Minimum Then
Minimum = MyArray(intLoop)
End If
Next

End Function

Public Function Maximum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Maximum) Then
Maximum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) > Maximum Then
Maximum = MyArray(intLoop)
End If
Next

End Function

3. Create the following query. This query gives you a record for each
month that each patient is present, the patients ID number, and the number
of days they were present during the month. You will have to play with the
parameters inside the DateDiff, Maximum, and Minimum functions to get the
values you want, which are determined by whether you are counting the start
and end dates of their stay. Once you have the durations working out the
way you want, then modify the query and group it by the Stay_Month, Count on
the Pt_ID column, and Sum on the Duration column

SELECT tbl_Numbers.Number AS Stay_Month
, tbl_Patient_Dates.Pt_ID
,
DateDiff("d",Maximum([Start_Date],DateSerial(2006,[Number],1)),
Minimum(DateSerial(2006,[Number]+1,1),[End_Date])) AS Duration
FROM tbl_Numbers, tbl_Patient_Dates
WHERE (((tbl_Numbers.Number) Between Month([Start_Date]) And
Month([End_Date]))
AND ((tbl_Patient_Dates.Start_Date)<DateSerial(2007,1,1))
AND ((tbl_Patient_Dates.End_Date)>DateSerial(2006,1,0)))
ORDER BY tbl_Numbers.Number;

Hope this helps.
 
G

Guest

Dale,

Your right in that we are not counting the day they leave/arrive. We are
interested in how many days the patient is in for the month. So, for a
patient that arrives on January 4, were interested in counting the number of
days that patient is in until January 31. Then, if their staying in
February, how many days in February in this case all 28 days. Then in March
how many more days until they leave. And this would be for all patients and
counting the number of days for each. Your technique is good and I will
have to try that one too.

Dale Fye said:
William,

Are you counting the day they leave/arrive, it does not look like it. There
are 31 days in January, so a patient that arrives on January 4 and doesn't
leave until March 31st would be on site for 28 days, not 27.

I've got a slightly different technique than Karl,
1. Create a new table (I call it tbl_Numbers), with one field [Number]
which is defined as a long integer. Fill this table with the numbers 1-12
(for the months).

2. Create a couple of functions. These will be used in the query to
determine the number of days the patient is in the hospital during a given
month. These are functions I created a while ago to give me the minimum and
maximum from among a group of values passed to the function. The nice thing
is that they work just as well for dates as it does for numbers or strings.

Public Function Minimum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Minimum) Then
Minimum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) < Minimum Then
Minimum = MyArray(intLoop)
End If
Next

End Function

Public Function Maximum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Maximum) Then
Maximum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) > Maximum Then
Maximum = MyArray(intLoop)
End If
Next

End Function

3. Create the following query. This query gives you a record for each
month that each patient is present, the patients ID number, and the number
of days they were present during the month. You will have to play with the
parameters inside the DateDiff, Maximum, and Minimum functions to get the
values you want, which are determined by whether you are counting the start
and end dates of their stay. Once you have the durations working out the
way you want, then modify the query and group it by the Stay_Month, Count on
the Pt_ID column, and Sum on the Duration column

SELECT tbl_Numbers.Number AS Stay_Month
, tbl_Patient_Dates.Pt_ID
,
DateDiff("d",Maximum([Start_Date],DateSerial(2006,[Number],1)),
Minimum(DateSerial(2006,[Number]+1,1),[End_Date])) AS Duration
FROM tbl_Numbers, tbl_Patient_Dates
WHERE (((tbl_Numbers.Number) Between Month([Start_Date]) And
Month([End_Date]))
AND ((tbl_Patient_Dates.Start_Date)<DateSerial(2007,1,1))
AND ((tbl_Patient_Dates.End_Date)>DateSerial(2006,1,0)))
ORDER BY tbl_Numbers.Number;

Hope this helps.

William said:
I have database with medicare patients that needs to determine the length
of
stay and number of patients by month. The data looks like this:
Pt# Start dt End dt
123 1/4/06 3/31/06
456 1/6/06 4/11/06
676 5/1/06 5/10/06

Whenever a patients stay is longer than a month need to count that
patients
days in that month.
What the result should be is this:
Month #Patients Length of stay
Jan 06 2 52 (days) - both patients days to the
end
of the month
Feb 06 2 56 (days)
Mar 06 2 62 (days)
Apr 06 1 11 (days)
May 06 1 10 (days)

Any suggestions ?
Click to expand...
Click to expand...
 
D

Dale Fye

William,

If you are not counting the day the arrive or the day they leave, I think
you need to relook at your example. The number of days in April would be
April 1 through April 10 (10 days), and the number in May would be 8 (May
2-May 9). You just need to add a day after [Start_Date] ([Start_Date] + 1)
and potentially subtract a date after the [End_Date] ([End_Date] - 1) in the
Maximum and Minimum functions.

Dale

William said:
Dale,

Your right in that we are not counting the day they leave/arrive. We are
interested in how many days the patient is in for the month. So, for a
patient that arrives on January 4, were interested in counting the number
of
days that patient is in until January 31. Then, if their staying in
February, how many days in February in this case all 28 days. Then in
March
how many more days until they leave. And this would be for all patients
and
counting the number of days for each. Your technique is good and I will
have to try that one too.

Dale Fye said:
William,

Are you counting the day they leave/arrive, it does not look like it.
There
are 31 days in January, so a patient that arrives on January 4 and
doesn't
leave until March 31st would be on site for 28 days, not 27.

I've got a slightly different technique than Karl,
1. Create a new table (I call it tbl_Numbers), with one field [Number]
which is defined as a long integer. Fill this table with the numbers
1-12
(for the months).

2. Create a couple of functions. These will be used in the query to
determine the number of days the patient is in the hospital during a
given
month. These are functions I created a while ago to give me the minimum
and
maximum from among a group of values passed to the function. The nice
thing
is that they work just as well for dates as it does for numbers or
strings.

Public Function Minimum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Minimum) Then
Minimum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) < Minimum Then
Minimum = MyArray(intLoop)
End If
Next

End Function

Public Function Maximum(ParamArray MyArray() As Variant) As Variant

Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)

If IsEmpty(Maximum) Then
Maximum = MyArray(intLoop)
ElseIf IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf MyArray(intLoop) > Maximum Then
Maximum = MyArray(intLoop)
End If
Next

End Function

3. Create the following query. This query gives you a record for each
month that each patient is present, the patients ID number, and the
number
of days they were present during the month. You will have to play with
the
parameters inside the DateDiff, Maximum, and Minimum functions to get the
values you want, which are determined by whether you are counting the
start
and end dates of their stay. Once you have the durations working out the
way you want, then modify the query and group it by the Stay_Month, Count
on
the Pt_ID column, and Sum on the Duration column

SELECT tbl_Numbers.Number AS Stay_Month
, tbl_Patient_Dates.Pt_ID
,
DateDiff("d",Maximum([Start_Date],DateSerial(2006,[Number],1)),
Minimum(DateSerial(2006,[Number]+1,1),[End_Date])) AS Duration
FROM tbl_Numbers, tbl_Patient_Dates
WHERE (((tbl_Numbers.Number) Between Month([Start_Date]) And
Month([End_Date]))
AND ((tbl_Patient_Dates.Start_Date)<DateSerial(2007,1,1))
AND ((tbl_Patient_Dates.End_Date)>DateSerial(2006,1,0)))
ORDER BY tbl_Numbers.Number;

Hope this helps.

William said:
I have database with medicare patients that needs to determine the
length
of
stay and number of patients by month. The data looks like this:
Pt# Start dt End dt
123 1/4/06 3/31/06
456 1/6/06 4/11/06
676 5/1/06 5/10/06

Whenever a patients stay is longer than a month need to count that
patients
days in that month.
What the result should be is this:
Month #Patients Length of stay
Jan 06 2 52 (days) - both patients days to the
end
of the month
Feb 06 2 56 (days)
Mar 06 2 62 (days)
Apr 06 1 11 (days)
May 06 1 10 (days)

Any suggestions ?
Click to expand...
Click to expand...
Click to expand...
 
B

ben

Please excuse me for busting in, but I believe you are possibly
discussing a similar problem to what I have, and I wanted to ask you
wether you might know how to solve this?

I have a table with 3 columns,
and about 10'000 lines with price data, like

Date Price Min/Max
1.1.95 1.20
1.2.95 1.19
1.3.95 1.17
1.4.95 1.20
etc.

I would like identify those that are the highest (lowest) over a
certain period of time
e.g. date +/- 15 days. and mark them in column Min/Max with either "T"
for the highest or "B" for the lowest.

So, on each date I want to calculate for the preceding 15 days and the
following 15 days, wether this date was the highest/lowest for that
overall period of 31 days.

Also, I need to be able to adjust these +/- n days.

Any idea how this could be done - is some of the code you mentioned in
this thread also suitable for this problem (sorry, I'm rather new to
coding) ?

Once again, please excuse the intrusion.
 
G

Guest

I'm not sure that this is the same problem, but I would try the techniques
here.
My mistake earlier in that we are counting both the day patient entered and
left.
I am having some difficulty with the minimum and maximum functions. In that,
its doing fine for the minimum, but not the maximum.
The query by Karl actually handles the situation perfectly, but takes a very
long time
to run. My situation is that I want to know how many patient days do
patients stay in
a month and how many patients are there. Thus, in my example
Pt# Start dt Stop dt
123 1/4/06 3/1/06
456 1/7/06 4/1/06

The results should look like this:
Month #pts #days (#of days by all patients)
Jan 06 2 53
Feb 06 2 56
Mar 06 2 32
Apr 06 1 1
 
D

Dale Fye

William,

I don't get it, in your previous post, you said you are not counting the
first or last day, then you give a different example than your original one,
and you count both the arrival and departure dates. Do you or don't you
want to count the first and last day of the patients stay?

If you want to count both of these days, try this (I aliased the table names
to make this a little shorter:

SELECT N.Number AS Stay_Month
, Count(P.Pt_ID) AS CountOfPt_ID
,
Sum(DateDiff("d",Maximum([Start_Date],DateSerial(2006,[Number],1)),Minimum(DateSerial(2006,[Number]+1,1),[End_Date]+1)))
AS Duration
FROM tbl_Numbers AS N, tbl_Patient_Dates AS P
WHERE P.Start_Date<DateSerial(2007,1,1)
AND P.End_Date>DateSerial(2006,1,0)
AND N.Number Between Month([Start_Date]) And Month([End_Date])
GROUP BY N.Number
ORDER BY N.Number;

HTH
Dale
 
D

Dale Fye

Ben,

In your case, I think I would create two queries, one to get the minimum and
maximum price within 15 days on either side of the date, a second one to
match this query up with your table to mark the Min/Max column with a B or
T.

BTW, Date is an access reserved word and should never be used as a column
name. Use something like PriceDate

1. qryPriceDateMinMax:
SELECT PD.PriceDate, PD.Price,
(SELECT MIN(Price) FROM tbl_Price_Data P
WHERE PriceDate Between PD.PriceDate - [Duration] and
PD.PriceDate + [Duration]) AS MinPrice,
(SELECT MAX(Price) FROM tbl_Price_Data P
WHERE PriceDate Between PD.PriceDate - [Duration] and
PD.PriceDate + [Duration]) AS MaxPrice
FROM tbl_Price_Data AS PD;

2.
SELECT PD.PriceDate,
PD.Price,
IIf([PD].[Price]=[qry].[MinPrice],"B",IIf([pd].[price]=[qry].[MaxPrice],"T",""))
AS [Min/Max]
FROM tbl_Price_Data AS PD
INNER JOIN qryPriceDateMinMax AS qry
ON PD.PriceDate = qry.PriceDate
ORDER BY PD.PriceDate;

The [Duration] is a parameter which will allow you to use any time period
for your comparison.

Hope this helps
Dale Fye
Developing Solutions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Brackets as an IIf statement 3
Amazon are rubbish 14
Min/Max Dates and Group By 2
repeating values 1
Qry Running Average 5
Queries: Dates-return only days served within the month reporting 3
Advanced problem using auto-number and datediff to find readmissio 1
Amount of time between records 4

Top