TimeToCode TimeToCode - 3 months ago 9
SQL Question

How to save in a variable the value of the following query? SQL Server

I need to implement two variables for save the data result in a query.

I have he following query:

SELECT * FROM
(SELECT location AS Location, COUNT(*) AS Trucks FROM Truck GROUP BY location) loc
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


This query shows the next table:

|Location|Trucks|TotalOfCampaings|CampaingsWithCompleteStatus|CampaingsWithInProcessStatus

I need to add a column at the end, in the new column i need to get the percent of campaings with complete status, i tried to do something like this:

Percent = (CampaingsWithCompleteStatus / TotalOfCamapings) * 100

But i dont know how to save the values of the query to do that.

Answer

Something like this:

SELECT
    loc.Location,
    loc.Trunks,
    stat.TotalOfCampaings,
    stat.CampaingsWithCompleteStatus,
    stat.CampaingsWithInProcessStatus,
    (1.0 * stat.CampaingsWithCompleteStatus /stat.TotalOfCampaings) * 100 as [Percent]
FROM 
(SELECT location AS Location, COUNT(*) AS Trucks FROM Truck GROUP BY location) loc
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