Untitled
plain_text
a month ago
7.0 kB
1
Indexable
Never
MERGE `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` t USING `tsum-dwh-users.tsum_collect.view_stream_seller_items_funnel_data` n ON t.Period = n.Period AND t.Department_main = n.Department_main AND t.Account = n.Account AND t.ManagerName = n.ManagerName AND t.CaseNumber = n.CaseNumber AND t.code = n.code AND t.invoice_number = n.invoice_number AND t.offer_code = n.offer_code AND t.seller_id = n.seller_id AND t.Warehouse_Actnumber = n.Warehouse_Actnumber WHEN MATCHED THEN UPDATE SET t.CP_item_created = n.CP_item_created, t.SF_CreatedDate = n.SF_CreatedDate, #t.SF_Auth_Status = n.SF_Auth_Status, t.CP_Auth_status = n.CP_Auth_status, t.Auth_dt = n.Auth_dt, #t.SF_Expert_Status = n.SF_Expert_Status, t.PRC_dt = n.PRC_dt, t.PRC_price = n.PRC_price, t.PRC_comment = n.PRC_comment, t.PRC_comment_dt = n.PRC_comment_dt, t.Price_estimated = n.Price_estimated, t.price = n.price, t.PriceRequest_send = n.PriceRequest_send, t.Price_agreed = n.Price_agreed, t.Agreement_status = n.Agreement_status, t.Sent_PDF = n.Sent_PDF, t.Agreement_dt = n.Agreement_dt, t.photo_visited = n.photo_visited, t.photo_leave = n.photo_leave, t.Photo_donwload_CP = n.Photo_donwload_CP, t.item_publish_cp = n.item_publish_cp, t.return_date = n.return_date, t.orderdatetime = n.orderdatetime, t.ordered_now = n.ordered_now, t.SF_CaseStatus= n.SF_CaseStatus, t.current_warehouse = n.current_warehouse, t.Status_Temporary = n.Status_Temporary, t.updated_datetime = n.updated_datetime, t.SF_CaseCreatedDate = n.SF_CaseCreatedDate, t.Week = n.Week, t.Year = n.Year, -- t.seller_id = n.seller_id, t.user_name = n.user_name, t.Warehouse_package_created_date = n.Warehouse_package_created_date, t.Warehouse_qty_fact= n.Warehouse_qty_fact, -- t.Warehouse_Actnumber = n.Warehouse_Actnumber, t.IS_Black_Account = n.IS_Black_Account, t.transit_time = n.transit_time, t.stock_unit_code = n.stock_unit_code, t.defects = n.defects, t.brand = n.brand, t.sub_brand = n.sub_brand, t.Price_estimated_draft = n.Price_estimated_draft, t.Price_estimated_draft_dt = n.Price_estimated_draft_dt, t.Wait_photo_pdf_dt = n.Wait_photo_pdf_dt, t.Defect_add_dt = n.Defect_add_dt, t.Defects_public_add_dt = n.Defects_public_add_dt, t.ReasonTransferToVipBlAcPreOwned = n.ReasonTransferToVipBlAcPreOwned, t.visited_remont_or_chimchistka_datetime = n.visited_remont_or_chimchistka_datetime, t.has_been_visited_warehouse_for_sellout = n.has_been_visited_warehouse_for_sellout, t.ClientTime = n.ClientTime, t.Status_for_work = n.Status_for_work, t.MoreDep = n.MoreDep, t.XCaseNumber = n.XCaseNumber, t.MoreNames = n.MoreNames, t.Status_order = n.Status_order, t.SLA_Auth = n.SLA_Auth, t.SLA_Price = n.SLA_Price, t.SLA_Price_SEND = n.SLA_Price_SEND, t.SLA_Price_AGREED = n.SLA_Price_AGREED, t.Photo_DONE = n.Photo_DONE, t.Publish_DONE = n.Publish_DONE, t.Publish_SLA = n.Publish_SLA, t.Hours_On_Stage = n.Hours_On_Stage, t.Hours_On_Stage_withoutClientTime = n.Hours_On_Stage_withoutClientTime, t.Hours_On_Stage_Photo = n.Hours_On_Stage_Photo, t.PRC_Status = n.PRC_Status, t.PRC_on_stage = n.PRC_on_stage, t.Photo_status = n.Photo_status, t.WorkSpace = n.WorkSpace, t.minutes_on_rem_chim = n.minutes_on_rem_chim, t.Return_status = n.Return_status, t.rem_chim_status = n.rem_chim_status, t.returned_to_seller = n.returned_to_seller, t.curr_return_status = n.curr_return_status, t.Warehouse_QUEUEDATETIME = n.Warehouse_QUEUEDATETIME, t.cur_defect_status = n.cur_defect_status, t.auth_to_defectTO_hours = n.auth_to_defectTO_hours, t.photo_visited_to_defectHUD_hours = n.photo_visited_to_defectHUD_hours, t.is_composition = n.is_composition, t.Measurements_dt = n.Measurements_dt WHEN NOT MATCHED BY TARGET THEN INSERT( Period, Department_main, Account, ManagerName, CaseNumber, code, invoice_number, offer_code, CP_item_created, SF_CreatedDate, #SF_Auth_Status, CP_Auth_status, Auth_dt, #SF_Expert_Status, PRC_dt, PRC_price, PRC_comment, PRC_comment_dt, Price_estimated, price, PriceRequest_send, Price_agreed, Agreement_status, Sent_PDF, Agreement_dt, photo_visited, photo_leave, Photo_donwload_CP, item_publish_cp, return_date, orderdatetime, ordered_now, SF_CaseStatus, current_warehouse, Status_Temporary, updated_datetime, SF_CaseCreatedDate, Week, Year, seller_id, user_name, Warehouse_package_created_date, Warehouse_qty_fact, Warehouse_Actnumber, IS_Black_Account, transit_time, stock_unit_code, defects, brand, sub_brand, Price_estimated_draft, Price_estimated_draft_dt, Wait_photo_pdf_dt, Defect_add_dt, Defects_public_add_dt, ReasonTransferToVipBlAcPreOwned, visited_remont_or_chimchistka_datetime, has_been_visited_warehouse_for_sellout, ClientTime, Status_for_work, MoreDep, XCaseNumber, MoreNames, Status_order, SLA_Auth, SLA_Price, SLA_Price_SEND, SLA_Price_AGREED, Photo_DONE, Publish_DONE, Publish_SLA, Hours_On_Stage, Hours_On_Stage_withoutClientTime, Hours_On_Stage_Photo, PRC_Status, PRC_on_stage, Photo_status, WorkSpace, minutes_on_rem_chim, Return_status, rem_chim_status, returned_to_seller, curr_return_status, Warehouse_QUEUEDATETIME, cur_defect_status, auth_to_defectTO_hours, photo_visited_to_defectHUD_hours, is_composition, Measurements_dt ) VALUES ( n.Period, n.Department_main, n.Account, n.ManagerName, n.CaseNumber, n.code, n.invoice_number, n.offer_code, n.CP_item_created, n.SF_CreatedDate, #n.SF_Auth_Status, n.CP_Auth_status, n.Auth_dt, #n.SF_Expert_Status, n.PRC_dt, n.PRC_price, n.PRC_comment, n.PRC_comment_dt, n.Price_estimated, n.price, n.PriceRequest_send, n.Price_agreed, n.Agreement_status, n.Sent_PDF, n.Agreement_dt, n.photo_visited, n.photo_leave, n.Photo_donwload_CP, n.item_publish_cp, n.return_date, n.orderdatetime, n.ordered_now, n.SF_CaseStatus, n.current_warehouse, n.Status_Temporary, n.updated_datetime, n.SF_CaseCreatedDate, n.Week, n.Year, n.seller_id, n.user_name, n.Warehouse_package_created_date, n.Warehouse_qty_fact, n.Warehouse_Actnumber, n.IS_Black_Account, n.transit_time, n.stock_unit_code, n.defects, n.brand, n.sub_brand, n.Price_estimated_draft, n.Price_estimated_draft_dt, n.Wait_photo_pdf_dt, n.Defect_add_dt, n.Defects_public_add_dt, n.ReasonTransferToVipBlAcPreOwned, n.visited_remont_or_chimchistka_datetime, n.has_been_visited_warehouse_for_sellout, n.ClientTime, n.Status_for_work, n.MoreDep, n.XCaseNumber, n.MoreNames, n.Status_order, n.SLA_Auth, n.SLA_Price, n.SLA_Price_SEND, n.SLA_Price_AGREED, n.Photo_DONE, n.Publish_DONE, n.Publish_SLA, n.Hours_On_Stage, n.Hours_On_Stage_withoutClientTime, n.Hours_On_Stage_Photo, n.PRC_Status, n.PRC_on_stage, n.Photo_status, n.WorkSpace, n.minutes_on_rem_chim, n.Return_status, n.rem_chim_status, n.returned_to_seller, n.curr_return_status, n.Warehouse_QUEUEDATETIME, n.cur_defect_status, n.auth_to_defectTO_hours, n.photo_visited_to_defectHUD_hours, n.is_composition, n.Measurements_dt ) WHEN NOT MATCHED BY SOURCE AND updated_datetime < ( SELECT MIN(updated_datetime) FROM `tsum-dwh-users.tsum_collect.view_stream_seller_items_funnel_data` ) OR t.Warehouse_Actnumber = Warehouse_Actnumber THEN DELETE