Untitled

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