CREATE PROCEDURE webApi.spGetPatientsWithMedicamentsFromProductGroup @ProductGroupId INT
AS

;WITH cte AS (
	SELECT ppg.PatientProductGiveId, ppg.PatientId, p.ProductId, p.[FullName], ppg.QuantityGive, SUM(ISNULL(ppgd.QuantityDone,0)) as QuantityDoneGive, SUM(ISNULL(ppgd.QuantityDoneLost,0)) as QuantityDoneLost
	FROM dbo.PatientProductGive ppg            
		JOIN vwProduct p ON ppg.ProductId = p.ProductId            
		LEFT JOIN dbo.PatientProductGiveDone ppgd ON ppg.PatientProductGiveId = ppgd.PatientProductGiveId            
	WHERE ppg.ProductGroupId = @ProductGroupId AND (CAST(ppg.GiveDate AS DATE) = CAST(GETDATE() AS DATE) OR CAST(ppg.GiveDate AS DATE) = CAST(DATEADD(DAY, - 1, GETDATE()) AS DATE))
	GROUP BY ppg.PatientId,            
				  ppg.PatientProductGiveId,            
				  p.ProductId,            
				  p.FullName,            
				  ppg.QuantityGive     
)
SELECT c.PatientProductGiveId, vppg.PatientId, vppg.FirstName, vppg.LastName, c.ProductId, c.FullName, (c.QuantityGive - c.QuantityDoneGive - c.QuantityDoneLost) as Quantity
FROM cte c
	JOIN dbo.vwPatientProductGroup vppg ON c.PatientId = vppg.PatientId
WHERE vppg.ProductGroupId = @ProductGroupId AND vppg.DischargeDate IS NULL;