We are measuring how many deliveries are shipped the day after the order is taken by the week. I have been able to build an aggregate query that gives me the count of shipments shipped the next day and the total count of shipments. The problem is the Rollup needs to happen before the percentage calculation is made. I am having difficulty in getting this timing to work. My only promising attempt was to create a top level subquery with only the company table in it linked to the lower level query on company ID, selecting the rolled up fields, and performing the division. This still came out with 0s indicating that the timing is still not right. So I gave up on that idea. For those interested in the challenge, I have included my query below. In the meantime we can breakout a piece of paper and a pencil to calculate the percentage. Ultimately, it is simply a timing issue.
have fun!
select
(DatePart(year, ShipHead.ShipDate)) as [Calculated_Year],
(DatePart(week, ShipHead.ShipDate)) as [Calculated_WeekNum],
(count(ShipDtl.PackLine)) as [Calculated_ShippedLines],
(Sum((case when ShipHead.ShipDate = OrderHed.OrderDate then 1 else 0 end))) as [Calculated_SameDay],
(sum(case when Datediff(day,OrderHed.OrderDate,ShipHead.ShipDate) = 1 then 1 else 0 end)) as [Calculated_NextDay],
(NextDay/ShippedLines) as [Calculated_NextDayPercent]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderHed as OrderHed on
OrderDtl.Company = OrderHed.Company
And
OrderDtl.OrderNum = OrderHed.OrderNum
and ( OrderHed.OrderType_c = āPartsā )
inner join Erp.ShipDtl as ShipDtl on
OrderDtl.Company = ShipDtl.Company
And
OrderDtl.OrderNum = ShipDtl.OrderNum
And
OrderDtl.OrderLine = ShipDtl.OrderLine
inner join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
And
ShipDtl.PackNum = ShipHead.PackNum
where (OrderDtl.OpenLine = false)
group by ROLLUP( Year,WeekNum )