SELECT * FROM 
(
select h.invoice_id, h.invoice_no, h.invoice_balance AS header from txn_invoice_history_header h
inner join txn_invoice_history_detail d ON d.invoice_id = h.invoice_id
WHERE h.invoice_status like '%Approve%' AND CONVERT(date, invoice_date) >= '2024-05-03' AND CONVERT(date, invoice_date) <= '2024-05-30'
GROUP BY h.invoice_id, h.invoice_no, h.invoice_balance
) x
INNER JOIN 
(
select d.invoice_id, SUM(d.line_total) as line_total, cast(sum(ROUND(web_price_before_discount *  total_uom3_qty, 2)) as decimal(18,2)) as line_total_before_discount, cast(sum(ROUND(mprice * total_uom3_qty, 2)) as decimal(18,2)) as line_total_after_discount from txn_invoice_history_header h
inner join txn_invoice_history_detail d ON d.invoice_id = h.invoice_id
WHERE h.invoice_status like '%Approve%' AND CONVERT(date, invoice_date) >= '2024-05-03' AND CONVERT(date, invoice_date) <= '2024-05-30'
GROUP BY d.invoice_id
) y ON x.invoice_id = y.invoice_id
WHERE header <> line_total

--select * into _txn_invoice_history_header_20240601_SY_header_detail_not_tally from txn_invoice_history_header

select * from _txn_invoice_history_header_20240601_SY_header_detail_not_tally

select currency_value, invoice_balance, detail_total from txn_invoice_history_header ih
inner join (select invoice_id, SUM(line_total) as detail_total from txn_invoice_history_detail group by invoice_id) id on ih.invoice_id = id.invoice_id  
where invoice_status like '%Approve%' and CONVERT(date, invoice_date) >= '2024-05-03' AND CONVERT(date, invoice_date) <= '2024-05-30' and invoice_balance <> detail_total

update ih set currency_value = detail_total, invoice_balance = detail_total, updated_by = 'SHAN YOU', updated_date = GETDATE() from txn_invoice_history_header ih
inner join (select invoice_id, SUM(line_total) as detail_total from txn_invoice_history_detail group by invoice_id) id on ih.invoice_id = id.invoice_id  
where invoice_status like '%Approve%' and CONVERT(date, invoice_date) >= '2024-05-03' AND CONVERT(date, invoice_date) <= '2024-05-30' and invoice_balance <> detail_total

select * from txn_invoice_history_header where updated_by = 'SHAN YOU'

select order_web_net_amount, detail_total from txn_sales_order_header ih
inner join (select ord_hdr_id, SUM(web_product_net_amount) as detail_total from txn_sales_order_details group by ord_hdr_id) id on ih.ord_hdr_id = id.ord_hdr_id  
where status like '%approve%' and CONVERT(date, order_date) >= '2024-05-03' AND CONVERT(date, order_date) <= '2024-05-30'