逐一将时间表逐一将时间表拉成1行，而不是许多

发布于 2025-01-31 10:10:21 字数 1507 浏览 4 评论 0原文

我正在尝试将每个功能的时间表拉到每辆车的1行中。汽车在工厂开始功能 - 开始日期 - 结束日期 - 人该框架建于05/23/2022 04:16 AM 05/23/2022 06:16 AM Joe，Joe，挡风玻璃安装在2022年5月23日9:18 PM 05/23/2022 10:18 PM Suzy，Suzy，电动机于05/25/2022 01:14 PM 05/25/2022 03:23 PM Harry，Harry，框架于2022年5月28日绘制9:45 PM 05/29/2022 9:45 PM由Joe绘制。

对于每辆汽车，我想将上面的各个记录放入1行以显示此1张卡的时间表，最终将帮助我找到瓶颈和处理时间。我正在尝试构建两组不同的数据。 1 Mayne挡风玻璃是如何做的，Joe在每个挡风玻璃之间的停机时间，我们可以制造汽车花了多长时间，我可以做一些日期，但试图将每辆车在1线上置于不同的条目。我认为我必须做一些铅 /滞后。认为我的数据将是1行：

型号T1-帧-05/23/2022 04:16 AM 05/23/2022 06:16 AM -JOE -JOE -WINDSHEILD -05/23/2022 9:18 PM -05/23/23/ 2022 10:18 PM- SUZY-安装 - 5/25/2022 01:14 PM -05/25/2022 03:23 PM -HARRY -HARRY -FRAME绘制05/28/2022 9：45-pm 05/29/2022222222222222222222222222222222222年9：45-乔总时间组装5:16 HH：MM 选择插入日期时间作为生产开始dateTime，startdate，startdate，sartdate，sindate，nim（startdate）（通过agissedto分区，启动订单分配，分配给startdate desc）作为framebuilt，lag（startdate），lag（startdate）vose（通过分配订单分配订单，startdate，startdate，startdate，startdate，startdate，startdate） desc）作为挡风玻璃的安装，铅（末端）上的（通过分配的订单分配，端达德desc）为windsheildinstalledStarted，为windsheildinstallcompleted，lag（enddate），末端（由dissed deSc. （端代表日期） + 1 = cast（lag_next_start as date），然后0 else 1结束为标志，case（endDate as date as date）= cast（startDate as date），然后1 else 0 end 0 as count2，count2，enddate = startDate = startDate的情况然后1 else 0 end as count1，taskname，（max_start -min_start）小时（4）to sectim htm，（max_start -min_start -min_start）小时（4）the as tbt，（tbt =任务之间的tbt =时间）从workflowlog wfl ffl ffl左加入functions_filtered在wfl.internalId上为pdf = pdf。

原文

I'm trying to pull the timeline for each function into 1 line for each car.
The car started in the factory,
Function - Start Date - End Date - Person
the frame was built on 05/23/2022 04:16 AM 05/23/2022 06:16 AM By Joe,
the Windshield was installed on 05/23/2022 9:18 PM 05/23/2022 10:18 PM By Suzy,
the Motor was installed on 05/25/2022 01:14 PM 05/25/2022 03:23 PM By Harry,
the Frame was Painted on 05/28/2022 9:45 PM 05/29/2022 9:45 PM by Joe.

for each car I want to take the individual records above and join into 1 row to show the timelines for this 1 card, which eventually will lead to help me find my bottle necks and processing time.
I'm trying to build two different sets of data. 1 how mayne windshields did joe do and whats joes downtime in between each windsheild, how long did it take us to build the car, which i can do some datediffs, but trying to get each car on 1 line form the different entry's.
i'm thinking i have to do some Lead / Lag.
Thinking my data would be 1 line:

Model T1 - frame - 05/23/2022 04:16 AM 05/23/2022 06:16 AM - Joe - Windsheild - 05/23/2022 9:18 PM - 05/23/2022 10:18 PM - Suzy - Motor Installed - 5/25/2022 01:14 PM - 05/25/2022 03:23 PM - Harry - Frame Painted 05/28/2022 9:45 - PM 05/29/2022 9:45 - Joe Total Time to Assemble 5:16 HH: MM
Select Insert DateTime as Production Start DateTime, StartDate as StartDate, Min(StartDate) Over (PARTITION BY ASSIGNEDTO, StartDate ORDER BY ASSIGNEDTO, StartDate DESC) AS FrameBuilt, Lag(StartDate) Over (PARTITION BY ASSIGNEDTO ORDER BY ASSIGNEDTO, StartDate DESC) AS WindsheildInstalled, Lead(EndDate) Over (PARTITION BY ASSIGNEDTO ORDER BY ASSIGNEDTO, EndDate DESC) AS WindsheildInstalledStarted, EndDate as WindsheildInstallCompleted, Lag(EndDate) Over (PARTITION BY ASSIGNEDTO ORDER BY ASSIGNEDTO, EndDate DESC) AS MotorInstalled, CASE WHEN Cast(ENDDATE AS DATE) + 1 = Cast(Lag_next_start AS DATE) THEN 0 ELSE 1 END AS flag, Case when Cast(EndDate as Date) = Cast(Startdate as Date) then 1 Else 0 End as count2, Case When EndDate = StartDate then 1 Else 0 End as count1,TaskName, (max_start - min_start) Hour(4) TO SECOND as htm, (max_start - min_start) Hour(4) TO SECOND as tbt,(tbt = Time between Task) From WorkflowLog as wfl left join functions_FILTERED as pdf on wfl.INTERNALID = pdf.INTERNALID QUALIFY (ROW_NUMBER() OVER(PARTITION BY wfl.INTERNALID ORDER BY STARTDATE DESC))=1) as a

分享到QQ

分享到微博