How to set the flag in header table on the basis of lines-CodePudding

I have got sales header and sales line table. e.g. SalesID = 1 have got 2 sales lines in sales line table.

SalesHeader:
Sales ID = 1, Active = 1

I would like to set Active flag to 0 if all line's sales status is invoiced

SalesLines
SalesID = 1 , lineNum = 1, SalesStatus = Invoiced
SalesID = 1 , lineNum = 2, SalesStatus = Open Order

In this case I do not want to change the Active flag to 0 in header table as 1 line in same sales order is Open order.

If all lines invoiced than i would like to change the active flag = 0.

CodePudding user response：

1- Find the IDs you need to update. You can do it checking the number of all available salesLines of an ID and comparing it the total of "Invoiced" count, which should be matching to its flag get updated. You can save those ids to a separate temp table as you may also use table type of variable of "with cte" structure.

SELECT SalesId
into #SalesHavingAllInvoicedLines
from 
   (
      SELECT SalesId,
             COUNT(*) totCount,
             SUM(CASE WHEN SalesStatus ='in' THEN 1 ELSE 0 END) InCount 
      FROM SalesLines 
      GROUP BY SalesId
   ) x 
WHERE x.totCount=x.InCount

2- Do the update using "inner join" to the temp table you've created on Step 1.

update s
set Active=0
from SalesHeader s
inner join #SalesHavingAllInvoicedLines s2 on s.SalesId=s2.SalesId