NEED MSSQL VIEW TWEAKED TO SUMMARIZE DATA

if object_id( 'tempdb..#tempPTHL' ) is not null
	drop table #tempPTHL;

select
	PTHL.BUS_DAT,
	PTHL.EXT_PRC,
	PTHL.QTY_SOLD,
	PTHL.QTY_NUMER,
	PTHL.QTY_DENOM,
	PTHL.TKT_NO,
	PTHL.ITEM_NO,
	VPPM.PriorMonths,
	II.DESCR_UPR,
	II.ITEM_VEND_NO,
	II.VEND_ITEM_NO,
	II.LST_COST,
	II.PRC_1,
	VPPM.MonthName
into
	#tempPTHL
from
	dbo.PS_TKT_HIST_LIN as PTHL
		inner join dbo.VI_PS_PRIOR_MONTHS as VPPM on PTHL.BUS_DAT >= VPPM.BOM and PTHL.BUS_DAT < VPPM.BONM
		inner join dbo.IM_ITEM as II on PTHL.ITEM_NO = II.ITEM_NO
where
	VPPM.PriorMonths <= 3 and
	PTHL.LIN_TYP in( 'R', 'S' );

-- select * from #tempPTHL;


SELECT     
	ITEM_NO, 
	MIN(MonthName) AS Month_and_Year, 
	SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) AS [Returns], 
	SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, 
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P3
		where
			P3.ITEM_NO = P1.ITEM_NO and
			P3.PriorMonths > 0
	) as Previous_Sales,
	COALESCE (SUM(QTY_SOLD * QTY_NUMER / QTY_DENOM), 0) AS Qty_Sold, 
    SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, 
	CAST(COALESCE (SUM(EXT_PRC) / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
	CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END), 0)), 0) AS decimal(15, 2)) AS Pct_Returns, 
	PriorMonths, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
from
	#tempPTHL as P1
GROUP BY 
	ITEM_NO, 
	PriorMonths, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
order by
	ITEM_NO,
	Month_and_Year;

Select allOpen in new window

if object_id( 'tempdb..#tempPTHL' ) is not null
	drop table #tempPTHL;

select
	PTHL.BUS_DAT,
	PTHL.EXT_PRC,
	PTHL.QTY_SOLD,
	PTHL.QTY_NUMER,
	PTHL.QTY_DENOM,
	PTHL.TKT_NO,
	PTHL.ITEM_NO,
	VPPM.PriorMonths,
	II.DESCR_UPR,
	II.ITEM_VEND_NO,
	II.VEND_ITEM_NO,
	II.LST_COST,
	II.PRC_1,
	VPPM.MonthName
into
	#tempPTHL
from
	dbo.PS_TKT_HIST_LIN as PTHL
		inner join dbo.VI_PS_PRIOR_MONTHS as VPPM on PTHL.BUS_DAT >= VPPM.BOM and PTHL.BUS_DAT < VPPM.BONM
		inner join dbo.IM_ITEM as II on PTHL.ITEM_NO = II.ITEM_NO
where
	VPPM.PriorMonths <= 3 and
	PTHL.LIN_TYP in( 'R', 'S' );

-- select * from #tempPTHL;


SELECT     
	ITEM_NO, 
	MIN(MonthName) AS Month_and_Year, 
	SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) AS [Returns], 
	SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, 
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P3
		where
			P3.ITEM_NO = P1.ITEM_NO and
			P3.PriorMonths > 0
	) as Previous_Sales,
	COALESCE (SUM(QTY_SOLD * QTY_NUMER / QTY_DENOM), 0) AS Qty_Sold, 
    SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, 
	CAST(COALESCE (SUM(EXT_PRC) / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
	CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END), 0)), 0) AS decimal(15, 2)) AS Pct_Returns, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
from
	#tempPTHL as P1
GROUP BY 
	ITEM_NO, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
order by
	ITEM_NO,
	Month_and_Year;

Select allOpen in new window

	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,

Select allOpen in new window

	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths <= 1
	) as Current_Sales,

Select allOpen in new window