Facebook
From Paweł, 2 Years ago, written in Plain Text.
This paste is a reply to Re: Re: Untitled from Reliable Owl - view diff
Embed
Download Paste or View Raw
Hits: 203
  1. CREATE PROCEDURE webApi.spGetPatientsWithMedicamentsFromProductGroup @ProductGroupId INT
  2. AS
  3.  
  4. ;WITH cte AS (
  5.         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
  6.         FROM dbo.PatientProductGive ppg            
  7.                 JOIN vwProduct p ON ppg.ProductId = p.ProductId            
  8.                 LEFT JOIN dbo.PatientProductGiveDone ppgd ON ppg.PatientProductGiveId = ppgd.PatientProductGiveId            
  9.         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))
  10.         GROUP BY ppg.PatientId,            
  11.                                   ppg.PatientProductGiveId,            
  12.                                   p.ProductId,            
  13.                                   p.FullName,            
  14.                                   ppg.QuantityGive    
  15. )
  16. SELECT c.PatientProductGiveId, vppg.PatientId, vppg.FirstName, vppg.LastName, c.ProductId, c.FullName, (c.QuantityGive - c.QuantityDoneGive - c.QuantityDoneLost) as Quantity
  17. FROM cte c
  18.         JOIN dbo.vwPatientProductGroup vppg ON c.PatientId = vppg.PatientId
  19. WHERE vppg.ProductGroupId = @ProductGroupId AND vppg.DischargeDate IS NULL;