combining sql statements

I need to combine these three sql statements.


select a.CustomerID, PartNumber as [Part Number], SUM(Quantity) as Submitted
from ServiceRequest a , Frus b
where a.FrusID = b.FrusID and SRequestStatusID=1
group by a.CustomerID, PartNumber


select a.CustomerID, PartNumber as [Part Number], Count([Part #]) as [Due In]
from OutstandingEquipmentReport a, Frus b
where a.[Part #] = b.PartNumber
group by a.CustomerID, PartNumber


select a.[CustomerID]
      ,b.[PartNumber]
        ,a.[AWAITING REPAIR]
      ,a.[IN PROCESS]
      ,a.[AVAILABLE]
from HotSwapOnsiteInventoryReport a , Frus b
where a.[Part #] = b.PartNumber

The goal is to have the query to look like

Part Number        Submitted        Due In    Awaiting Repair    In Process     Available     Total(excluding submitted)

The information needs to reference the frus table because the other tables might not contain information.
JDay2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
This should be close.  I had to guess at which table some columns came from.  I also assumed that the OutstandingEquipment table had only a single row per part# -- if that's not true, that code will need adjusted.

SELECT
    f.PartNumber AS [Part Number],
    COALESCE(sr.CustomerID, oer.CustomerID, hso.CustomerID) AS CustomerID,
    SUM(sr.Quantity) AS Submitted,
    SUM(CASE WHEN oer.[Part #] IS NULL THEN 0 ELSE 1 END) AS [Due In],
    MAX(hso.[AWAITING REPAIR]) AS [AWAITING REPAIR],
    MAX(hso.[IN PROCESS]) AS [IN PROCESS],
    MAX(hso.[AVAILABLE]) AS [AVAILABLE]
FROM Frus f
LEFT OUTER JOIN ServiceRequest sr ON
    sr.FrusID = f.FrusID AND
    sr.SRequestStatusID = 1
FULL OUTER JOIN OutstandingEquipmentReport oer ON
    oer.[Part #] = f.PartNumber AND
    oer.CustomerID = sr.CustomerID
FULL OUTER JOIN HotSwapOnsiteInventoryReport hso ON
    hso.[Part #] = f.PartNumber AND
    hso.CustomerID = COALESCE(sr.CustomerID, oer.CustomerID)
GROUP BY
    f.PartNumber,
    COALESCE(sr.CustomerID, oer.CustomerID, hso.CustomerID)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
WITH Submitted_CTE (CustomerID, PartNumber,Submitted)
AS 
( select a.CustomerID, PartNumber, SUM(Quantity) 
 from ServiceRequest a , Frus b
 where a.FrusID = b.FrusID and SRequestStatusID=1 
 group by a.CustomerID, PartNumber),

Due_CTE (CustomerID, PartNumber,DueIn)
AS
( select a.CustomerID, PartNumber, Count([Part #]) 
 from OutstandingEquipmentReport a, Frus b
 where a.[Part #] = b.PartNumber
 group by a.CustomerID, PartNumber)

select a.[CustomerID]
       ,a.[Part #] [Part Number]
       , s.Submitted        
       ,d.DueIn [Due In]    
       ,a.[AWAITING REPAIR]
       ,a.[IN PROCESS]
       ,a.[AVAILABLE]
      ,d.DueIn+a.[AWAITING REPAIR]+a.[IN PROCESS]+a.[AVAILABLE] Total
from HotSwapOnsiteInventoryReport a
inner join Submitted_CTE s
      on a.[Part #] = s.PartNumber
inner join Due_CTE d
       on a.[Part #] = d.PartNumber

Open in new window

0
JDay2Author Commented:
Ok the second comes closer than the first.  I had to modify the inner joins to left outer but I need all the parts from the frus table.  There might be a scenario where there is no data in all three tables.  How do I accomplish that?  

The first query accomplishes that information but it does total the submitted and Due in columns correctly.

WITH Submitted_CTE (CustomerID, PartNumber,Submitted)
AS 
( select a.CustomerID, PartNumber, SUM(Quantity) 
 from ServiceRequest a , Frus b
 where a.FrusID = b.FrusID and SRequestStatusID=1 
 group by a.CustomerID, PartNumber),

Due_CTE (CustomerID, PartNumber,DueIn)
AS
( select a.CustomerID, PartNumber, Count([Part #]) 
 from OutstandingEquipmentReport a, Frus b
 where a.[Part #] = b.PartNumber
 group by a.CustomerID, PartNumber)

select a.[CustomerID]
       ,a.[Part #] [Part Number]
       ,s.Submitted        
       ,d.DueIn [Due In]    
       ,a.[AWAITING REPAIR]
       ,a.[IN PROCESS]
       ,a.[AVALIABLE]
      ,d.DueIn+a.[AWAITING REPAIR]+a.[IN PROCESS]+a.[AVALIABLE] Total
from HotSwapOnsiteInventoryReport a
left outer join Submitted_CTE s
      on a.[Part #] = s.PartNumber
left outer join Due_CTE d
       on a.[Part #] = d.PartNumber

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Then add the Frus table to the select:
WITH Submitted_CTE (CustomerID, PartNumber,Submitted)
AS 
( select a.CustomerID, PartNumber, SUM(Quantity) 
 from ServiceRequest a , Frus b
 where a.FrusID = b.FrusID and SRequestStatusID=1 
 group by a.CustomerID, PartNumber),

Due_CTE (CustomerID, PartNumber,DueIn)
AS
( select a.CustomerID, PartNumber, Count([Part #]) 
 from OutstandingEquipmentReport a, Frus b
 where a.[Part #] = b.PartNumber
 group by a.CustomerID, PartNumber)

select a.[CustomerID]
       ,b.PartNumber
       ,s.Submitted        
       ,d.DueIn [Due In]    
       ,a.[AWAITING REPAIR]
       ,a.[IN PROCESS]
       ,a.[AVALIABLE]
      ,d.DueIn+a.[AWAITING REPAIR]+a.[IN PROCESS]+a.[AVALIABLE] Total
from frus b
left outer join HotSwapOnsiteInventoryReport a
      on b.PartNumber =  a.[Part #]
left outer join Submitted_CTE s
      on b.PartNumber = s.PartNumber
left outer join Due_CTE d
       on b.PartNumber = d.PartNumber

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDay2Author Commented:
Is there a statement to grab everything from the left table even if it is not in the other three?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
LEFT OUTER JOIN do that. But if you want to see more data then add the columns from the left table in the SELECT statement. In your query only Frus.PartNumber is there so add the columns that you think are relevant to you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.