TimeToCode TimeToCode - 3 months ago 18
SQL Question

General report of a couple tables in SQL Server

I need to create a general report of the following table schema:

Table Schema

I need to create the following table for the general report:

+----------+--------+------------------+-----------------------------+------------------------------+
| Location | Trucks | TotalOfCampaings | CampaingsWithCompleteStatus | CampaingsWithInProcessStatus |
+----------+--------+------------------+-----------------------------+------------------------------+
| | | | | |
+----------+--------+------------------+-----------------------------+------------------------------+
| | | | | |
+----------+--------+------------------+-----------------------------+------------------------------+



  • Location: Location of the trucks with at least one campaing.

  • Trucks: Number of Trucks by Location with at least one campaing.

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

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

  • CampaingsWithInProcessStatus: Total Number of Campaings with in process status, the status are in the Campaing Table.



Campaing = Order to fix one or multiple trucks.

I tried with a inner joins, but i can't get what i expect for the general report.

I would appreciate help me with this, any question post on comments!

Answer
SELECT Truck.location as Location,
    COUNT(Truck.vin) as Trucks,
    COUNT(Campaing.campaing_id) as TotalOfCampaings,
    sum(case when Campaing.campaing_estatus = 'Complete' then 1 else 0 end) as CampaingsWithCompleteStatus,
    sum(case when Campaing.campaing_estatus = 'InProcess' then 1 else 0 end) as CampaingsWithInProcessStatus
    FROM CampaingControl
        INNER JOIN Truck ON CampaingControl.vin = Truck.vin
        INNER JOIN Campaing ON CampaingControl.campaing_id = Campaing.campaing_id
    GROUP BY Truck.location;

Not tested, so please give feedback for furthur assistance.

Comments