Generic report of a pair of tables in SQL Server

Question

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

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

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

• The location . Location of trucks with at least one camp.
• Trucks : the number of trucks at a location with at least one settlement.
• TotalOfCampaings : The total number of campaigns by location and truck.
• CampaingsWithCompleteStatus : total number of completed campaigns, the status is in the Campaing table.
• CampaingsWithInProcessStatus : total number of campaigns with process status, the status is in the Campaing table.

Campaing = Order to install one or more trucks.

, , .

, !

Answer 1

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;