PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
SELECT DateValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))) AS [Date], TimeValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))) AS [Time], Count(qryCmbDate.OOS_ID) AS CountOfOOS_ID, Count(qryCmbDate.Status) AS CountOf_X, Count(qryCmbDate.Status) AS [Count)f_Y]
FROM qry_Days, qry_Hours, qryCmbDate
WHERE (((DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])))>=[InDT] And (DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])))<[OutDT]))
GROUP BY DateValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))), TimeValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])));
LOCO-DB-Exchange4.zip
PARAMETERS [StartDay] DateTime, [EndDay] Date/Time;
SELECT DateValue(DateAdd("d",[intDay],[StartDay])) AS DateRange, qry_Hours.intHour AS HourStart, DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) AS DTHR
FROM qry_Days, qry_Hours
ORDER BY DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0);
WHERE DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) < DateAdd("d", 1, [EndDay])
PARAMETERS [StartDay] DateTime, [EndDay] DateTime;
TRANSFORM Count(qryCmbDate.OOS_ID) AS CountOfOOS_ID
SELECT T.DateRange, T.HourStart
FROM (SELECT DateValue(DateAdd("d",[intDay],[StartDay])) AS DateRange, qry_Hours.intHour AS HourStart, DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) AS DTHR
FROM qry_Days, qry_Hours
WHERE DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) < DateAdd("d", 1, [EndDay])
ORDER BY DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0)
) AS T, qryCmbDate
WHERE (((T.DTHR)<[OUTDT]) AND ((DateAdd("h",1,[DTHR]))>=[InDT]))
GROUP BY T.DateRange, T.HourStart, T.DTHR, DateAdd("h",1,[DTHR])
PIVOT qryCmbDate.Status;
TRANSFORM Nz(Count(qryCmbDate.OOS_ID),0) AS CountOfOOS_ID
PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour
FROM (
SELECT DateValue([DTHR]) AS OOSDate
, Hour([DTHR]) AS OOSHour
, DateValue([StartDayTime])+[intDay]+ [intHour]/24 AS DTHR
FROM qry_Days, qry_Hours
WHERE (DateValue([StartDayTime])+[intDay] + [intHour]/24) >=DateValue([StartDayTime]) + Hour([StartDayTime])/24
AND (DateValue([StartDayTime])+[intDay]+ [intHour]/24) <=DateValue([StartDayTime]) + 1 + Hour([StartDayTime])/24
ORDER BY DateValue([StartDayTime])+[intDay]+ [intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;
To explain, lets start with the subquery, which generates the day/time combinations starting with the date and hour of the time you enter, and continuing for the next 24 hourly values, to give you a total of 25 records. The core of the subquery is the calculation of the each hourly increment of the day and time. This is done by taking the DateValue() of the date/time passed into the query and incrementing that by every value of [intDay] and intHour. The WHERE clause ensures that those combinations are >= the Day/hour passed (if you pass in 11:15, it will start at 11:00) and <= the time 24 hours from that value. If you only want 24 hourly values change the <= to <.SELECT DateValue([DTHR]) AS OOSDate
, Hour([DTHR]) AS OOSHour
, DateValue([StartDayTime])+[intDay]+ [intHour]/24 AS DTHR
FROM qry_Days, qry_Hours
WHERE (DateValue([StartDayTime])+[intDay] + [intHour]/24) >=DateValue([StartDayTime]) + Hour([StartDayTime])/24
AND (DateValue([StartDayTime])+[intDay]+ [intHour]/24) <=DateValue([StartDayTime]) + 1 + Hour([StartDayTime])/24
ORDER BY DateValue([StartDayTime])+[intDay]+ [intHour]/24
Private Sub Form_Timer
Dim dtLastUpdated as date
if Hour(dtLastUpdate) = Hour(Now()) Then Exit Sub
dtLastUpdated = Now()
me.subformName.form.Requery
End Sub
Adding another computed column to the query above can get convoluted. If you can explain what you are trying to add to these results, maybe I can point you in the right direction.
PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Total
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([StartDayTime])+[intDay]+[intHour]/24)>=DateValue([StartDayTime])+Hour([StartDayTime])/24 And (DateValue([StartDayTime])+[intDay]+[intHour]/24)<=DateValue([StartDayTime])+1+Hour([StartDayTime])/24 ORDER BY DateValue([StartDayTime])+[intDay]+[intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;
If you use this as the RecordSource for a form, you could set the conditional formatting of the Total control so that it is highlighted as appropriate.
10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Total_InService
PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([StartDayTime])+[intDay]+[intHour]/24)>=DateValue([StartDayTime])+Hour([StartDayTime])/24 And (DateValue([StartDayTime])+[intDay]+[intHour]/24)<=DateValue([StartDayTime])+1+Hour([StartDayTime])/24 ORDER BY DateValue([StartDayTime])+[intDay]+[intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;
TempVars.Add StartDayTime, #5/10/2014 7:00#
?[Tempvars]![StartDayTime]
10/05/2014 7:00:00 AM
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_OOS, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24)>=DateValue([Tempvars]![StartDayTime])+Hour([StartDayTime])/24 And (DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24)<=DateValue([Tempvars]![StartDayTime])+1+Hour([Tempvars]![StartDayTime])/24 ORDER BY DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;
Public Function fnTempvars(VarName as string) as Variant
fnTempvars = TempVars(VarName)
End Function
Then replace the [Tempvars]![StartDayTime] references in the query with:?Tempvars("StartDayTime")
5/10/2014 7:00:00 AM
?([tempvars]![StartDayTime])
5/10/2014 7:00:00 AM
?fnTempVars ("StartDayTime")
5/10/2014 7:00:00 AM
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_OOS, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE
(DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24)>=DateValue(fnTempvars("StartDayTime"))+Hour(fnTempvars("StartDayTime"))/24 And (DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24)<=DateValue(fnTempvars("StartDayTime"))+1+Hour(fnTempvars("StartDayTime"))/24 ORDER BY DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.