Don't know if this has already come through to everyone, if so I apologize.
Ok, here's the deal
Table1 is a master list holding ID numbers, and related information sent by
clients
Table2 is a queue that utilizes Table1.ID numbers, and updates other
information on the current state of a job associated with these ID numbers
within Table2. This table is used only as a queue, so the Table1.ID numbers
come and go as jobs complete. There can be multiple instances, or 0
instances of Table1.ID on Table2.
Table3 is a log that contains even more information concerning the Table1.ID
numbers, and associated processing information from Table2, and information
of further processing, shipping status, tracking numbers, etc. There can be
multiple instances, or 0 instances of the Table1.ID number on Table3.
So, what I am trying to do is this:
Table1 holds this info(simplified):
MasterID(Unique) int
NumberRequested int
Table2 holds this info(simplified):
MasterID int
OrderID(Unique) int
Table3 holds this info(simplified):
MasterID int
OrderID(Unique) int
JobState bool
What I need to extract, within a single statement, is this:
For each MasterID on Table1
What is the NumberRequested
Does the MasterID exist on Table2, if so,
how many times? (QUEUED)
Does the MasterID exist on Table3, and if so,
How many times with JobState TRUE (GOOD)
How many times with JobState FALSE (BAD)
If the NumberRequested is greater than
QUEUED+GOOD, then return a 1 as (UHOH)
So, looking for a return like:
MASTERID NUMBERREQUESTED QUEUED GOOD BAD UHOH
1 10 5 4 1 1
2 10 10 0 0 0
3 10 8 2 1 0
4 10 6 0 4 1
5 10 0 10 0 0
6 10 0 5 5 1
This is what I tried:
SELECT Table1.MasterID AS MasterID,
Table1.numberofcopies AS numberrequested,
SUM(CASE WHEN TABLE3.jobstate=0
AND TABLE3.MasterID=Table1.MasterID
THEN 1 END) AS good,
SUM(CASE WHEN Table3.jobstate=1
AND Table3.MasterID=Table1.MasterID
THEN 1 END) AS bad,
SUM(CASE WHEN Table2.MasterID=Table1.MasterID
THEN 1 END) AS queued,
(CASE WHEN numberrequested > queued+good
THEN 1 ELSE 0 END) AS uhoh
FROM Table1, Table2, Table3
GROUP BY Table1.MasterID;
Help please?
Thanks,
Mike
