CREATE PROCEDURE purchases_top_dollar_down_compare

	@beg_date datetime,
	@end_date datetime,
	@compare_start_dt datetime,
	@compare_end_dt datetime,
	@location char(1),
	@records_to_print int -- the number of records to print, 0 will return all rows.
AS

SET NOCOUNT ON

CREATE TABLE #top_dollar_down (generic_drug varchar(50), total_current_period money,
				total_compare_period money, variance money)

--get the drugs and the totals for the first period
INSERT INTO #top_dollar_down (generic_drug, total_current_period)

SELECT ECGNAME, SUM(EXTENDED) AS total
FROM reporting_view3 
WHERE (HDINVDATE >= @beg_date and HDINVDATE <= @end_date and in_pt_or_out_pt = @location)

GROUP BY ECGNAME

--SELECT * FROM #top_dollar_down
--Now add in the comparative period

Create Table #tdd_compare (gen_name varchar(50), compare_period money)
	INSERT INTO #tdd_compare (gen_name, compare_period)

SELECT ECGNAME, SUM(EXTENDED)
FROM reporting_view3 
WHERE (HDINVDATE >= @compare_start_dt and HDINVDATE <= @compare_end_dt and in_pt_or_out_pt = @location)
GROUP BY ECGNAME

--select * from #tdd_compare
--APPEND IN LAST YEAR'S DATA FOR EXPENSES
UPDATE #top_dollar_down
	SET #top_dollar_down.total_compare_period = #tdd_compare.compare_period from #tdd_compare where #tdd_compare.gen_name =#top_dollar_down.generic_drug

drop table #tdd_compare

--CALCULATE THE VARIANCE
UPDATE #top_dollar_down
	SET variance = total_current_period - total_compare_period

DECLARE @SQL nvarchar(500)

IF @records_to_print = 0 
	SELECT * FROM #top_dollar_down	ORDER BY variance desc
Else
BEGIN
SET @SQL = N'SELECT TOP ' + CAST(@records_to_print as varchar) + ' * from #top_dollar_down
	ORDER BY variance desc'

exec sp_executesql @SQL
END
DROP TABLE #top_dollar_down
GO
 

quote:

---

biggest pain has been doing cross-tab reports, need to hard code the tab matrix thingy which isnt nice

---