我如何基于另一个表中的值创建重复记录 [英] How can i create duplicate records based the value in another table
问题描述
我的数据库中有两个表, Work_Order
表是存储工作订单信息的源表,我也有 Work_Schedule
表,其中包含工作时间表,这些工作表告诉人们生产车间要建造什么,何时建造以及要建造多少.
Work_Order表看起来像
工单ItemCode大小数量Qty_per_HR41051 600111 14L-16.1 55 10
我想根据每小时工时在上面的工作单表中复制上述工作单行,并自动创建工作计划程序,如下所示.
其中 TARGET = Work_Order.Qty/Work_Order.Qty_per_HR
工作时间表表
Id开始日期/时间结束日期/时间工作订单工作中心TARGET ACTUAL1001 2019-07-22 7:00 AM 2019-07-22 8:00 AM 41051 1 101001 2019-07-22 8:00 AM 2019-07-22 9:00 AM 41051 1 101001 2019-07-22 9:00 AM 2019-07-22 10:00 AM 41051 1 101001 22019-07-22 10:15 AM 2019-07-22 11:00 AM 41051 1 101001 22019-07-22 11:00 AM 2019-07-22 12:00 PM 41051 1 101001 2019-07-22 1:30 PM 2019-07-22 2:30 PM 41051 1 5
我的计划是在用户工作单创建重复项后立即使用 AfterInsert
触发器.
计划窗口
对于递归CTE,这似乎很自然:
,cte为(选择convert(datetime,'2019-07-22 7:00 AM')作为dt,工作单,1作为工作中心,qtyperh作为目标,itemcode,size,(qty-qtyperh)as qty,qtyperh从T全部合并选择dateadd(hour,1,dt),工作单,工作中心,(如果qty> qtyperh然后qtyperh否则qty结束)作为目标,项目代码,大小,(数量-qtyperh),qtyperh来自cte数量>0)选择cte.*,dateadd(second,60 * 60 * target/qtyperh,dt)作为end_dt来自cte按工作单排序,dt;
这里是db< fiddle.
I have two tables in my database, Work_Order
table which is the source table where work order information's stored i also have Work_Schedule
table which contains work schedules which tell peoples in the production floor what to build, when and how much to build.
Work_Order Table looks like
Work order ItemCode Size Qty Qty_per_HR
41051 600111 14L-16.1 55 10
I want duplicate the above work order line in work order table above based on the Qty per hour and automatically create a work scheduler as shown below.
where TARGET = Work_Order.Qty/Work_Order.Qty_per_HR
Work_Schedule Table
Id Start Date/Time End Date/Time Work Order Work Center TARGET ACTUAL
1001 2019-07-22 7:00AM 2019-07-22 8:00AM 41051 1 10
1001 2019-07-22 8:00AM 2019-07-22 9:00AM 41051 1 10
1001 2019-07-22 9:00AM 2019-07-22 10:00AM 41051 1 10
1001 22019-07-22 10:15AM 2019-07-22 11:00AM 41051 1 10
1001 22019-07-22 11:00AM 2019-07-22 12:00PM 41051 1 10
1001 2019-07-22 1:30PM 2019-07-22 2:30PM 41051 1 5
My plan is to use AfterInsert
trigger as soon as the user a work order create the duplicates.
Schedule windows
This seems like a natural for a recursive CTE:
with cte as (
select convert(datetime, '2019-07-22 7:00AM') as dt, workorder, 1 as workcenter, qtyperh as target,
itemcode, size, (qty - qtyperh) as qty, qtyperh
from t
union all
select dateadd(hour, 1, dt), workorder, workcenter,
(case when qty > qtyperh then qtyperh else qty end) as target,
itemcode, size, (qty - qtyperh), qtyperh
from cte
where qty > 0
)
select cte.*,
dateadd(second, 60 * 60 * target / qtyperh, dt) as end_dt
from cte
order by workorder, dt;
Here is a db<>fiddle.
这篇关于我如何基于另一个表中的值创建重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!