I run into two problems. First of all, the subquery already gives me an error that I am not able to solve. Secondly, once the first point has been solved, I am not very clear on how to calculate the percentage either.
Table [InParts].[Web].[Request]
Id RequestDate
1 2022-08-02 07:36:20.000
2 2022-08-02 07:36:21.000
3 2022-08-02 07:44:05.000
4 2022-08-02 08:07:21.000
5 2022-08-02 08:07:22.000
Table [InParts].[Web].[RequestArticle]
Id RequestId ManufacturerCode ManufacturerName
1 1 340 PRASCO
2 1 340 PRASCO
3 1 4965 JUMASA
4 1 4965 JUMASA
5 2 340 PRASCO
Table [InParts].[Web].[ResponseArticle]
Id RequestId ManufacturerCode ManufacturerName Status
1 1 340 PRASCO Available
2 1 340 PRASCO Available
3 1 4965 JUMASA Unavailable
4 1 4965 JUMASA Available
5 2 340 PRASCO Unavailable
These are the two separate queries I have that return correct results
-- NUMBER OF PARTS NOT FOUND GROUPED BY MANUFACTURER
select resp.[ManufacturerName], COUNT(resp.[ManufacturerName]) as 'TotalPiezasPorFabricante'
from [InParts].[Web].[ResponseArticle] as resp
JOIN [InParts].[Web].[Request] as req on req.[Id] = resp.[RequestId]
where resp.[Status] <> 'Available' and
req.[RequestDate] between '2022-08-01' and '2022-08-30'
group by resp.[ManufacturerName]
order by TotalPiezasPorFabricante desc
Result:
--PARTS GROUPED BY MANUFACTURER
select ra.[ManufacturerCode], ra.[ManufacturerName], COUNT(ra.Id) as "total"
from [InParts].[Web].[RequestArticle] as ra
join [InParts].[Web].[Request] as req
on req.Id = ra.RequestId
where req.RequestDate between '2022-08-01' and '2022-08-30'
group by ra.[ManufacturerCode], ra.[ManufacturerName]
order by total desc
Result:
What is sought is that the second query is a subquery of the first in order to add a column indicating the % of parts not found over the total number of parts requested.