georgesOne
Technical User
Hi All:
I thought I made it, but... here is another problem (and some SQL).
I have this crosstab query:
TRANSFORM IIf(IsNull(ztbl_Tool2.Tool),365.25/4,Format((365.25/4-Sum(DateDiff("n",tbl_DownTime2.Startdate+tbl_DownTime2.StartTime,tbl_DownTime2.EndDate+tbl_DownTime2.EndTime)/60/24))/(Count(StartDate)+1),"0.00")) AS MTBF
SELECT ztbl_Tool2.Tool
FROM ztbl_Tool2 INNER JOIN tbl_DownTime2 ON ztbl_Tool2.ToolID=tbl_DownTime2.Tool
GROUP BY ztbl_Tool2.Tool
PIVOT "Quarter" & Format([StartDate],"q") In ("Quarter1","Quarter2","Quarter3","Quarter4");
giving the following table
Tool Quarter1 Quarter2 Quarter3 Quarter4
A 27.47 45.31 21.79 91.3125
B 14.23 45.63 17.61 91.3125
C 91.3125 91.3125 45.54 91.3125
D 91.3125 91.3125 26.63 91.3125
E 22.37 91.3125 17.50 91.3125
F 44.83 91.3125 22.39 91.3125
G 91.3125 45.57 45.50 91.3125
H 45.12 45.52 45.50 91.3125
I 91.3125 91.3125 27.14 91.3125
J 17.74 45.27 17.32 91.3125
K 91.3125 45.35 20.89 91.3125
L 27.97 91.3125 30.19 91.3125
As an explanation: the data reflect the mean time between failure (MTBF) of the tool, that means the average days during the quarter the tool was working.
For example: Tool A had in quarter1 three failures and was about 8.9 days down, so that (91.3125 days - 8.9)/3 = 27.47.
In the following quarter tool A had only 1 failure with a downtime of 0.6925 days, so the MTBF is 45.31. So far, so good.
However, I would prefer to have the value for the second quarter to be the average of quarter1 and 2: total failures is 4, total days 91.3125*2 and total downtime 8.9 + 0.6925 = 9.5925 and the result should be
(91.3125*2 - 9.5925)/4 = 43.25 days.
If there is no failure the MTBF should be 91.3125 days in the first quarter and 182.625 days in the second and so on.
Does anybody have a tip how to do that?
On a side note: I currently use 91.3125 days for a quarter. That is ok for the quarter, but I also would like to do the same for months and then I would prefer not to use 365.25/12 but the real month days, e.g. February has only 28 days. I think it is a similar problem, but I have no idea how to tackle this.
Any suggestions are welcome - I wrestling since days with this without progress.
I thought I made it, but... here is another problem (and some SQL).
I have this crosstab query:
TRANSFORM IIf(IsNull(ztbl_Tool2.Tool),365.25/4,Format((365.25/4-Sum(DateDiff("n",tbl_DownTime2.Startdate+tbl_DownTime2.StartTime,tbl_DownTime2.EndDate+tbl_DownTime2.EndTime)/60/24))/(Count(StartDate)+1),"0.00")) AS MTBF
SELECT ztbl_Tool2.Tool
FROM ztbl_Tool2 INNER JOIN tbl_DownTime2 ON ztbl_Tool2.ToolID=tbl_DownTime2.Tool
GROUP BY ztbl_Tool2.Tool
PIVOT "Quarter" & Format([StartDate],"q") In ("Quarter1","Quarter2","Quarter3","Quarter4");
giving the following table
Tool Quarter1 Quarter2 Quarter3 Quarter4
A 27.47 45.31 21.79 91.3125
B 14.23 45.63 17.61 91.3125
C 91.3125 91.3125 45.54 91.3125
D 91.3125 91.3125 26.63 91.3125
E 22.37 91.3125 17.50 91.3125
F 44.83 91.3125 22.39 91.3125
G 91.3125 45.57 45.50 91.3125
H 45.12 45.52 45.50 91.3125
I 91.3125 91.3125 27.14 91.3125
J 17.74 45.27 17.32 91.3125
K 91.3125 45.35 20.89 91.3125
L 27.97 91.3125 30.19 91.3125
As an explanation: the data reflect the mean time between failure (MTBF) of the tool, that means the average days during the quarter the tool was working.
For example: Tool A had in quarter1 three failures and was about 8.9 days down, so that (91.3125 days - 8.9)/3 = 27.47.
In the following quarter tool A had only 1 failure with a downtime of 0.6925 days, so the MTBF is 45.31. So far, so good.
However, I would prefer to have the value for the second quarter to be the average of quarter1 and 2: total failures is 4, total days 91.3125*2 and total downtime 8.9 + 0.6925 = 9.5925 and the result should be
(91.3125*2 - 9.5925)/4 = 43.25 days.
If there is no failure the MTBF should be 91.3125 days in the first quarter and 182.625 days in the second and so on.
Does anybody have a tip how to do that?
On a side note: I currently use 91.3125 days for a quarter. That is ok for the quarter, but I also would like to do the same for months and then I would prefer not to use 365.25/12 but the real month days, e.g. February has only 28 days. I think it is a similar problem, but I have no idea how to tackle this.
Any suggestions are welcome - I wrestling since days with this without progress.
