Between statement ?

[Guest]

Is there a way to compare a range to a between statement?


Example:

Our A shift starts at 6:00 AM and end at 6:00 PM. B shift is 6:00 PM and
ends 6:00 AM

I have a table with a start time and end time, both separate fields with a
date/time stamp in them that pertain to a wave start and stop time. What I
need to know is if a wave was ran during anytime of the shift.


Wave: 1111
Start time: 10/27/06 05:00 AM
End Time: 10/27/06 10:00 AM

1 hour of this ran on B shift and the remaining 4 hours was ran on A shift.

So if I wanted to query for waves ran on A shift how could I do this?

 

[kingston via AccessMonster.com]

Could you please rephrase your question? Do you mean waves that started and
finished entirely within a shift?

[WaveStart] >= [ShiftStart] AND [WaveEnd] <=[ShiftEnd]

Or do you mean waves that happened during a shift?

[WaveStart] >= [ShiftStart] AND [WaveStart] <= [ShiftEnd]
OR
[WaveEnd] >= [ShiftStart] AND [WaveEnd] <= [ShiftEnd]

I'm assuming that a wave will not exceed the duration of a shift (does not
last over 12 hours).

 

[David F Cox]

If I understand you correctly it is as simple as [datwavetime BETWEEN
[datshiftstart] AND [datshiftend]

 

[Guest]

I'm assuming that a wave will not exceed the duration of a shift (does not
last over 12 hours).

Yes, the wave can exceed the shift. This is where my problem lies, most are
within a shift, but I have some that can run over.

Could you please rephrase your question? Do you mean waves that started and
finished entirely within a shift?

[WaveStart] >= [ShiftStart] AND [WaveEnd] <=[ShiftEnd]

Or do you mean waves that happened during a shift?

[WaveStart] >= [ShiftStart] AND [WaveStart] <= [ShiftEnd]
OR
[WaveEnd] >= [ShiftStart] AND [WaveEnd] <= [ShiftEnd]

I'm assuming that a wave will not exceed the duration of a shift (does not
last over 12 hours).

Is there a way to compare a range to a between statement?

Example:

Our A shift starts at 6:00 AM and end at 6:00 PM. B shift is 6:00 PM and
ends 6:00 AM

I have a table with a start time and end time, both separate fields with a
date/time stamp in them that pertain to a wave start and stop time. What I
need to know is if a wave was ran during anytime of the shift.

Wave: 1111
Start time: 10/27/06 05:00 AM
End Time: 10/27/06 10:00 AM

1 hour of this ran on B shift and the remaining 4 hours was ran on A shift.

So if I wanted to query for waves ran on A shift how could I do this?

 

[Guest]

If I understand you correctly it is as simple as [datwavetime BETWEEN
[datshiftstart] AND [datshiftend]

But, the wave may start on one shift, but end on another. So the
datwavetime is really 2 fields one a starttime and the other a endtime.

 

[Guest]

If you have a table of wave occurances how will you be entering the shift
data for comparrison?
Are do you want the results to give you the date and shift that a wave
occured?

Below is a query that extracts the number od days of a given month (entered
at prompt) that someone was in a facility. They could have been there before
the month started or entered in the middle of the month. They also could
have left during or after end of the month. I think this could adapted by
entering shift information.

SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, DateDiff("d",IIf(CVDate([Enter month and
year MMM YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month
and year MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1 AS [Number of days stayed]
FROM [Client Services]
WHERE (((DateDiff("d",IIf(CVDate([Enter month and year MMM
YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month and year
MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1)>0));

 

[Guest]

Not all the bugs out yet ---
SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, [Enter period start] AS [Period Start],
[Enter period end] AS [Period end], IIf([Actual_Start_Date]<[Enter period
start],[Enter period start],[Actual_Start_Date]) AS [Match Start],
IIf([Actual_Close_Date]>[Enter period end],[Enter period
end],[Actual_Close_Date]) AS [Match end], IIf([Actual_Close_Date]>[Enter
period end],[Enter period
end],[Actual_Close_Date])-IIf([Actual_Start_Date]<[Enter period start],[Enter
period start],[Actual_Start_Date]) AS Occured
FROM [Client Services];

If you have a table of wave occurances how will you be entering the shift
data for comparrison?
Are do you want the results to give you the date and shift that a wave
occured?

Below is a query that extracts the number od days of a given month (entered
at prompt) that someone was in a facility. They could have been there before
the month started or entered in the middle of the month. They also could
have left during or after end of the month. I think this could adapted by
entering shift information.

SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, DateDiff("d",IIf(CVDate([Enter month and
year MMM YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month
and year MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1 AS [Number of days stayed]
FROM [Client Services]
WHERE (((DateDiff("d",IIf(CVDate([Enter month and year MMM
YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month and year
MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1)>0));

Is there a way to compare a range to a between statement?


Example:

Our A shift starts at 6:00 AM and end at 6:00 PM. B shift is 6:00 PM and
ends 6:00 AM

I have a table with a start time and end time, both separate fields with a
date/time stamp in them that pertain to a wave start and stop time. What I
need to know is if a wave was ran during anytime of the shift.


Wave: 1111
Start time: 10/27/06 05:00 AM
End Time: 10/27/06 10:00 AM

1 hour of this ran on B shift and the remaining 4 hours was ran on A shift.

So if I wanted to query for waves ran on A shift how could I do this?

 

[David F Cox]

So, I did not understand it correctly. Nothing new there ( :-<)

([datstartwavetime BETWEEN [datshiftstart] AND [datshiftend]) OR
([datendwavetime BETWEEN
[datshiftstart] AND [datshiftend])

If I understand you correctly it is as simple as [datwavetime BETWEEN
[datshiftstart] AND [datshiftend]

But, the wave may start on one shift, but end on another. So the
datwavetime is really 2 fields one a starttime and the other a endtime.

 

[kingston via AccessMonster.com]

Use the equations I provided as your criteria in a select query:

SELECT [WaveID] WHERE
([WaveStart] >= [ShiftStart] AND [WaveStart] <= [ShiftEnd])
OR
([WaveEnd] >= [ShiftStart] AND [WaveEnd] <= [ShiftEnd])
OR
([WaveStart] < [ShiftStart] AND [WaveEnd] > [ShiftEnd]);

The first criteria picks shifts where a wave started during that shift. The
second criteria picks shifts where a wave ended during that shift. The last
criteria picks shifts where the wave started before the shift and ended after
the shift. Thus, all cases are covered.
Move the equal signs to the correct positions depending on whether you count
a wave as occuring during a shift when a wave starts or ends exactly at shift
start or shift end.

I'm assuming that a wave will not exceed the duration of a shift (does not
last over 12 hours).

Yes, the wave can exceed the shift. This is where my problem lies, most are
within a shift, but I have some that can run over.

Could you please rephrase your question? Do you mean waves that started and
finished entirely within a shift?

[quoted text clipped - 28 lines]