use AdventureWorks2008R2
go

-- Find which products were the most heavily discounted, and on which months
select
	MonthDate=
	convert(datetime,
		convert(nvarchar,DATEPART(MM, TransactionDate))
		+'-01-'
		+convert(nvarchar,DATEPART(YYYY, TransactionDate))
	)
	,p.Name
	,avg(th.ActualCost / th.Quantity) as AvgSoldCost
	,avg(p.StandardCost) as StandardCost
	,avg(p.ListPrice) as ListPrice
	,AvgPercentDiscountFromListPrice = avg(1.0-((th.ActualCost / th.Quantity) / p.ListPrice))*100.0
from Production.TransactionHistory th
inner join Production.Product p
on p.ProductID=th.ProductID
where th.TransactionType='S' --sales
group by convert(datetime,
		convert(nvarchar,DATEPART(MM, TransactionDate))
		+'-01-'
		+convert(nvarchar,DATEPART(YYYY, TransactionDate))
	)
	,p.Name

--intersect

select
	MonthDate=DATEADD(MM,DATEDIFF(MM, '2000-01-01',th.TransactionDate),'2000-01-01')
	,p.Name
	,avg(th.ActualCost / th.Quantity) as AvgSoldCost
	,avg(p.StandardCost) as StandardCost
	,avg(p.ListPrice) as ListPrice
	,AvgPercentDiscountFromListPrice = avg(1.0-((th.ActualCost / th.Quantity) / p.ListPrice))*100.0
from Production.TransactionHistory th
inner join Production.Product p
on p.ProductID=th.ProductID
where th.TransactionType='S' --sales
and th.TransactionDate > '2006-01-01'
group by DATEADD(MM,DATEDIFF(MM, '2000-01-01',th.TransactionDate),'2000-01-01')
	,p.Name