TimeToCode TimeToCode - 4 months ago 11
SQL Question

General report of couple tables with inner joins

I need to create a general report about of a trucks in a company.

I have this tables in my schema:

Schema image:

enter image description here

Basically, I need to create a table containing the following:

|Location|Trucks|TotalOfCampaings|CampaingsWithCompleteStatus|CampaingsWithInProcessStatus|


  • Location: Location of the trucks, are in the Truck table.

  • Trucks: Number of Trucks by Location.

  • TotalOfCampaings: Total Number of Campaings by the Location and Trucks.

  • CampaingsWithCompleteStatus: Total Number of Campaings Completed, the status are in the table Campaing Control.

  • CampaingsWithInProcessStatus: Total Number of Campaings not finished.



Campaing = Order to fix one or multiple trucks.

I tried with a inner joins querys, but i cant get what i expect for the general report.

I would appreciate help me with this!

Answer
SELECT *
FROM 
    -- Prepare the base data for the report
    (SELECT location, COUNT(*) AS Trucks FROM Truck GROUP BY location) loc
    -- The statistics needed, make sure it is 1 to 1
    -- The status value just guess as you did not mention in the question
    OUTER APPLY
    (
        SELECT 
            COUNT(*) AS TotalOfCampaings, 
            SUM(CASE WHEN cc.campaing_status = 'Complete' THEN 1 ELSE 0 END) AS CampaingsWithCompleteStatus, 
            SUM(CASE WHEN cc.campaing_status = 'InProcess' THEN 1 ELSE 0 END) AS CampaingsWithInProcessStatus
        FROM CampaingControl cc INNER JOIN Truck t ON cc.vin = t.vin 
        WHERE t.location = loc.location
    ) stat
Comments