Query Syntax Help Group By Join

Transact-SQL
1

Hello all,

Thanks for any help with. I have the following tables:

Table: Invoice
Fields:
InvoiceId int
InvoiceNumber varchar(50)
InvoiceRunComplete bit

Table: InvoiceRun
Fields:
RunId int
InvoiceId int
State int

What I need to do is the the InvoiceRun table will have multiple records for an InvoiceId that will have the State from 0-4. If an Invoice from the Invoice Table has records in the InvoiceRun table joined on InvoiceId with the State of (3 or 4) for all the records for that InvoiceId in the InvoiceRun table return the Invoice record with the InvoiceRunComplete flag as 1. If any of the records for an InvoiceId in the InvoiceRun table are null or not in 3,4 then that flag should be false.

Hope this makes sense.

Return would just be the Invoice table fields.

Thanks for any help all the grouping together check of InvoiceRun table is confusing me.

2 
select 
	max(case when state in (3,4) then 1 else o end) as InvoiceRunCompleteFlag 
from 
    invoice a join invoicerun b on a.invoiceid = b.invoiceid
1 Like
3 

/* to just list the results */
SELECT
    I.InvoiceId, I.InvoiceNumber, IR.InvoiceRunComplete
FROM Invoice I
INNER JOIN (
    SELECT 
        InvoiceId,
        CASE WHEN SUM(CASE WHEN State IN (3, 4) THEN 0 
            ELSE 1 END) = 0 THEN 1 ELSE 0 END AS InvoiceRunComplete
    FROM dbo.InvoiceRun
    GROUP BY InvoiceId
) AS IR ON IR.InvoiceId = I.InvoiceId

/* to update the Invoice table with the results */
UPDATE I
SET InvoiceRunComplete = IR.InvoiceRunComplete
FROM Invoice I
INNER JOIN (
    SELECT 
        InvoiceId,
        CASE WHEN SUM(CASE WHEN State IN (3, 4) THEN 0 
            ELSE 1 END) = 0 THEN 1 ELSE 0 END AS InvoiceRunComplete
    FROM dbo.InvoiceRun
    GROUP BY InvoiceId
) AS IR ON IR.InvoiceId = I.InvoiceId
1 Like
4

Thank you. How will this work though when you are at the end of that WHEN saying = 0 at this part:
SUM(CASE WHEN State IN (3, 4) THEN 0
ELSE 1 END) = 0

5

If every State is 3 or 4, then the total will be 0, which means IRC should be set to 1; if any State is not 3 or 4 (including if it's NULL), then the IRC should be 0.

I think the code does that. although you didn't provide any usable data to test it with, so I'm not 100% sure.