Pharmacy Statistics Analysis

analysis.sql

BEGIN
DECLARE @sql TABLE(DispensationID INT, Patient INT, ItemID INT, DispensedQuantity INT, BillingTypeItemPrice MONEY, TotalPrice MONEY)

INSERT INTO @sql
SELECT a.DispensationID, a.Patient, b.ItemID, b.DispensedQuantity, c.BillingTypeItemPrice,
(b.DispensedQuantity * c.BillingTypeItemPrice) AS TotalPrice FROM Pharmacy.Dispensation a
INNER JOIN Pharmacy.DispensedItems b ON a.DispensationID = b.DispensationID
INNER JOIN Accounts.BillingTypeItemsPrices c ON b.ItemID = c.BillingTypeItem
INNER JOIN Accounts.BillingTypeItems d ON b.ItemID = d.BillingTypeItemID
WHERE d.BillingType = 7

IF OBJECT_ID('tempdb.dbo.#analysis_table', 'U') IS NOT NULL
  DROP TABLE #analysis_table;
SELECT z.ItemID, 
SUM(z.TotalPrice) AS gTotal, 
SUM(z.DispensedQuantity) AS gDispensedQuantity, 
COUNT(z.Patient) AS gTotalPatients,
COUNT(DISTINCT z.Patient) AS gTotalDistinctPatients
INTO #analysis_table
FROM @sql z GROUP BY z.ItemID

SELECT a.*, b.BillingType, b.BillingTypeItemName FROM #analysis_table a INNER JOIN Accounts.BillingTypeItems b ON a.ItemID = b.BillingTypeItemID
ORDER BY b.BillingTypeItemName ASC
END