I'm using SQL Server 2012 (v11.0.7001.0).
I am tasked with calculating automagically the hourly production target for one assembly line. Any guidance is much appreciated, as I am banging my head against a wall.
This assembly line can build a mix of three different units, each with a different takt time to meet demand.
Takt Time = Total Available Production Time / Customer Demand
The units are produced serially and for each unit produced, a datetime is wrote to a table with the unit number. The takt times are stored in another table.
I am able to query each datetime a unit was produced and that unit's takt time.
SELECT
p.Timestamp AS ProducedDT,
(SELECT TaktTime
FROM skutakttime
WHERE Material = p.SKU) AS TaktTime
FROM
productionLog p
WHERE
p.Timestamp BETWEEN '2022-07-20 06:00:00'
AND (SELECT MAX([Timestamp])
FROM productionLog
WHERE [CostCenter] = 'Line1' AND [Station] = 3)
AND [CostCenter] = 'Line1'
AND [Station] = 3
My query returns this:
ProducedDT TaktTime
-------------------------------------
2022-07-20 06:17:19.303 98
2022-07-20 06:20:03.507 98
2022-07-20 06:21:31.263 98
2022-07-20 06:23:42.313 98
2022-07-20 06:25:07.040 98
2022-07-20 06:29:38.893 98
2022-07-20 06:31:09.333 98
2022-07-20 06:32:47.533 98
2022-07-20 06:34:19.613 98
2022-07-20 06:35:54.930 98
2022-07-20 06:38:36.850 98
2022-07-20 06:40:17.380 98
2022-07-20 06:41:38.297 98
2022-07-20 06:43:03.930 98
2022-07-20 06:44:39.800 98
2022-07-20 06:46:31.313 98
2022-07-20 06:48:21.487 98
2022-07-20 06:51:25.537 98
2022-07-20 06:53:02.043 98
2022-07-20 06:57:32.623 98
2022-07-20 06:59:02.220 98
2022-07-20 07:00:29.117 98
2022-07-20 07:03:04.730 98
2022-07-20 07:05:33.073 98
2022-07-20 07:07:34.690 98
2022-07-20 07:08:58.740 98
2022-07-20 07:10:25.113 98
2022-07-20 07:11:53.813 98
2022-07-20 07:13:24.540 98
2022-07-20 07:14:44.077 98
2022-07-20 07:16:18.870 98
2022-07-20 07:17:48.630 98
2022-07-20 07:19:21.657 98
2022-07-20 07:21:00.763 98
2022-07-20 07:22:52.070 98
2022-07-20 07:25:28.013 98
2022-07-20 07:27:19.447 98
2022-07-20 07:29:03.943 98
2022-07-20 07:30:39.520 98
2022-07-20 07:32:36.923 98
2022-07-20 07:34:44.183 98
2022-07-20 07:36:16.870 98
2022-07-20 07:37:49.780 98
2022-07-20 07:39:40.923 98
2022-07-20 07:41:33.030 98
2022-07-20 07:43:03.460 98
2022-07-20 07:55:06.543 98
2022-07-20 07:56:50.200 98
2022-07-20 07:58:23.000 98
2022-07-20 08:18:41.510 98
2022-07-20 08:22:47.150 98
2022-07-20 08:24:24.610 98
2022-07-20 08:25:59.843 98
2022-07-20 08:27:35.233 98
2022-07-20 08:29:12.917 98
2022-07-20 08:31:00.070 98
2022-07-20 08:32:44.520 98
2022-07-20 08:34:29.833 98
2022-07-20 08:36:13.337 98
2022-07-20 08:38:33.440 98
2022-07-20 08:40:13.183 98
2022-07-20 08:41:49.987 98
2022-07-20 08:43:29.773 98
2022-07-20 08:45:18.027 98
2022-07-20 08:47:00.277 98
2022-07-20 08:48:48.383 98
2022-07-20 08:50:26.653 98
2022-07-20 08:52:03.557 98
2022-07-20 08:54:06.760 98
2022-07-20 08:55:47.360 98
2022-07-20 08:57:24.213 98
2022-07-20 08:59:09.997 98
2022-07-20 09:00:46.923 98
2022-07-20 09:04:29.803 98
2022-07-20 09:06:06.763 98
2022-07-20 09:07:39.797 98
2022-07-20 09:09:12.967 98
2022-07-20 09:12:56.330 98
2022-07-20 09:14:46.083 98
2022-07-20 09:16:33.160 98
2022-07-20 09:18:27.647 98
2022-07-20 09:20:21.643 98
2022-07-20 09:22:17.887 98
2022-07-20 09:28:53.380 98
2022-07-20 09:31:55.660 98
2022-07-20 10:24:05.053 98
2022-07-20 10:27:41.350 98
2022-07-20 10:29:17.557 98
2022-07-20 10:31:08.093 98
2022-07-20 10:32:33.980 98
2022-07-20 10:34:03.780 98
2022-07-20 10:35:41.880 98
2022-07-20 10:37:23.760 98
2022-07-20 10:39:08.803 98
2022-07-20 10:41:18.667 98
2022-07-20 10:45:44.873 98
2022-07-20 10:47:05.563 98
2022-07-20 10:48:36.183 98
2022-07-20 10:51:30.743 98
2022-07-20 10:52:45.307 98
2022-07-20 10:54:10.483 98
2022-07-20 10:56:47.223 98
2022-07-20 10:58:41.080 98
2022-07-20 10:59:55.767 98
2022-07-20 11:01:10.773 98
2022-07-20 11:03:35.827 98
2022-07-20 11:05:14.577 98
2022-07-20 11:06:36.073 98
2022-07-20 11:08:00.840 98
2022-07-20 11:09:31.973 98
2022-07-20 11:11:19.847 98
2022-07-20 11:12:51.637 98
2022-07-20 11:14:18.457 98
2022-07-20 11:15:44.730 98
2022-07-20 11:22:20.520 98
2022-07-20 11:26:20.517 98
2022-07-20 11:27:43.163 98
2022-07-20 11:30:12.613 98
2022-07-20 11:34:13.937 98
2022-07-20 11:35:48.040 98
2022-07-20 11:37:27.660 98
2022-07-20 11:38:56.573 98
2022-07-20 11:40:22.260 98
2022-07-20 11:41:47.933 98
2022-07-20 11:43:24.913 98
2022-07-20 11:45:15.350 98
2022-07-20 11:46:34.750 98
2022-07-20 11:57:22.407 75
2022-07-20 11:58:54.587 75
2022-07-20 12:33:57.977 75
2022-07-20 12:37:44.883 75
2022-07-20 12:39:19.243 75
2022-07-20 12:40:41.180 75
2022-07-20 12:41:58.140 75
2022-07-20 12:43:26.903 75
2022-07-20 12:45:01.270 75
2022-07-20 12:46:17.687 75
2022-07-20 12:47:35.107 75
2022-07-20 12:51:01.370 75
2022-07-20 12:52:32.433 75
2022-07-20 12:55:19.240 75
2022-07-20 12:56:43.793 75
2022-07-20 12:58:09.890 75
2022-07-20 12:59:30.660 75
2022-07-20 13:00:51.493 75
2022-07-20 13:02:12.290 75
2022-07-20 13:03:34.977 75
2022-07-20 13:04:55.207 75
2022-07-20 13:06:16.070 75
2022-07-20 13:07:44.260 75
2022-07-20 13:09:04.610 75
2022-07-20 13:10:31.490 75
2022-07-20 13:16:50.060 75
2022-07-20 13:18:22.540 75
2022-07-20 13:20:08.420 75
2022-07-20 13:21:24.263 75
2022-07-20 13:22:54.507 75
2022-07-20 13:24:16.277 75
2022-07-20 13:25:34.363 75
2022-07-20 13:27:52.450 85
2022-07-20 13:29:27.670 85
2022-07-20 13:30:57.497 85
2022-07-20 13:33:51.703 85
2022-07-20 13:35:18.760 85
2022-07-20 13:36:46.613 85
2022-07-20 13:38:12.270 85
2022-07-20 13:39:39.317 85
2022-07-20 13:41:17.830 85
2022-07-20 13:42:34.783 85
2022-07-20 13:44:07.710 85
2022-07-20 13:45:37.210 85
2022-07-20 13:47:04.050 85
2022-07-20 13:48:24.860 85
2022-07-20 13:50:00.310 85
2022-07-20 13:51:21.963 85
2022-07-20 13:53:10.090 85
2022-07-20 13:54:33.530 85
2022-07-20 13:55:54.050 85
2022-07-20 13:57:18.107 85
2022-07-20 13:58:58.043 85
2022-07-20 14:00:17.117 85
2022-07-20 14:01:40.283 85
2022-07-20 14:03:09.720 85
2022-07-20 14:05:20.477 85
2022-07-20 14:07:15.293 85
2022-07-20 14:08:50.077 85
2022-07-20 14:10:22.320 85
2022-07-20 14:12:05.843 85
To complicate things more, I am to calculate the target between distinct time blocks.
1 - 6:00 to 7:00
2 - 7:00 to 8:00
3 - 8:15 to 9:00
4 - 9:00 to 10:00
5 - 10:15 to 11:00
6 - 11:00 to 12:00
7 - 12:30 to 13:30
8 - 13:30 to 14:30
9 - 14:45 to 15:30
10 - 15:30 to 16:30
Calculation Example From Block 6.
I am leaving the decimal point in place until I am told whether to round up or down.
11:00:00 - 11:46:34 Ran 23 units
- Run Time 2794s / 98s Takt Time = 28.52 Units Should Have Been Produced
11:46:34 - 12:00:00 Ran 2 units
- Run Time 806s / 75s Takt Time = 10.74 Units Should Have Been Produced
- Target For Block 6 = 39.26 Units
Expected Result *Corrected Group 7 & 8
TimeBlock Target
----------------------
1 36.73
2 36.73
3 27.55
4 36.73
5 27.55
6 39.25
7 47.58
8 42.35
9 0
10 0
I have a stored procedure already that gets the total produced per time block, average actual cycle time between entries, and calculates performance based on actual cycle time vs target. However, I am having a hard time visualizing a way to do this in SQL.
CodePudding user response：
I hope you have the time block information in a table. You can then join to it and make the grouping easier.
The query uses window function lead() or lag() to group those rows with same TaktTime together as a group Grp
After Grp information is available, next is to get the first ProducedDT and last ProducedDT of the group to calculate the datediff() in seconds. However, for your requirement, it is not simply that way. It might be the start or end time of the time block. That is what the CTE cte is doing. start_tm and end_tm is the start and end time used for calculation
CTE pl consists of other value of the Produced Date & Time to make subsequent calculation and comparison of date and time easier. ProducedD is converted back to datetime instead of just date to save some typing futher on when need to add a date and a time together.
The solution is based on following table schema.
create table productionLog
(
ProducedDT datetime,
TaktTime int
);
create table time_block
(
id int,
time_start time(0),
time_end time(0)
);
The query
with pl as
(
select ProducedDT,
ProducedD = convert(datetime, convert(date, ProducedDT)),
ProducedT = convert(time(0), ProducedDT),
TaktTime,
g = case when TaktTime
<> lag(TaktTime) over (order by ProducedDT)
then 1
else 0
end
from productionLog
),
cte as
(
select *,
start_tm = coalesce(lag(ProducedDT) over (partition by pl.ProducedD, tb.id
order by pl.ProducedDT),
pl.ProducedD convert(datetime, tb.time_start)),
end_tm = case when lead(ProducedDT) over (partition by pl.ProducedD, tb.id
order by pl.ProducedDT) is not null
then ProducedDT
else pl.ProducedD convert(datetime, tb.time_end)
end
from (
select ProducedDT, ProducedD, ProducedT, TaktTime,
Grp = sum(g) over (order by ProducedDT)
from pl
) pl
inner join time_block tb on tb.time_start <= pl.ProducedT
and tb.time_end > pl.ProducedT
)
select ProducedD, id, sum(Diff * 1.0 / TaktTime)
from
(
select ProducedD, id, TaktTime, Grp,
Diff = datediff(second, min(start_tm), max(end_tm))
from cte
group by ProducedD, id, TaktTime, Grp
) t
group by ProducedD, id;