Better way of calculating percentages of orders with ship times of over X days?

user_846984 0
Viewed 155

I'm writing a T-SQL query to calculate percentages of orders that have a ship time of over X days.

What I have now works but it feels clunky and I'm always looking to improve my query writing skills.

The first CTE is getting total orders from a time frame. The second CTE is getting any orders that have a ship date that is 3 days older (or less) than the order date.

;WITH TotalOrders
AS (
SELECT COUNT(*) AS TotalOrders
     , MONTH(OrderDate) AS Month
     , YEAR(OrderDate) AS Year
  FROM Orders
 WHERE OrderDate >= '2020-05-01'
GROUP BY MONTH(OrderDate), YEAR(OrderDate)
),
Under3
AS (
SELECT COUNT(*) AS Under3Days
     , MONTH(OrderDate) AS Month
     , YEAR(OrderDate) AS Year
  FROM SorMaster SM
 WHERE OrderDate >= '2020-05-01'
   AND DATEDIFF(D, OrderDate, ShipDate) <= 3
GROUP BY MONTH(OrderDate), YEAR(OrderDate)

)

SELECT ROUND(Under3Days * 100.0 / TPO.TotalOrders, 1) AS 'PercentOfTotal'
     , TPO.Month
     , TPO.Year
  FROM Under3
  JOIN TotalOrders TPO
    ON TPO.Month = Under3.Month
   AND TPO.Year = Under3.Year

I know I can do this in temp tables too but wondering if there's a best practice to follow when doing these calculations

pivot sql-server-2016 datetime tsql sql-server
1 Answers

You can use conditional aggregation:

SELECT 
   ROUND(AVG(CASE WHEN ShipDate <= DATEADD(day, 3, OrderDate) THEN 100.0 ELSE 0 END), 1) AS PercentOfTotal
 , MONTH(OrderDate) AS Month
 , YEAR(OrderDate) AS Year
FROM Orders
WHERE OrderDate >= '2020-05-01'
GROUP BY MONTH(OrderDate), YEAR(OrderDate)
user_106767160
Related