sum(case when datepart(month, date) = datepart(month, @DateFrom  then gross else 0 end) as quantity1

sum(case when datepart(month, date) = datepart(month, dateadd(month, 1, @DateFrom)  then gross else 0 end) as quantity2

declare @Mytable table (Mydate datetime,gross money)

insert Into @MYtable(Mydate,Gross)
select '06/12/2009',11 union all
select '05/12/2009',11 union all
select '07/12/2009',21 union all
select '06/22/2009',51 union all
select '07/13/2009',71 union all
select '01/21/2009',21 union all
select '07/20/2009',11 union all
select '08/12/2009',2 union all
select '06/5/2009',5 union all
select '06/5/2009',3 union all
select '08/2/2009',1 
--select datediff(month,'7/1/2009','06/2/2009')
-- Current month
declare @Datefrom datetime
set @Datefrom = '7/5/2009'
select 
sum(case when datediff(month, @datefrom,mydate ) = 0 then gross else 0 end) as CurrentMonth
,sum(case when datediff(month,@datefrom,mydate) = 1 then gross else 0 end) as NextMonth
,sum(case when datediff(month,@datefrom,mydate) = -1 then gross else 0 end) as PriorMonth
from
@mytable a

--or you can do something like

select month(mydate) as [Month],sum(Gross)
from 
@mytable a
where month(mydate) between month(@DateFrom) -1 and month(@DateFrom) +1
group by month(mydate)

quote:

---

Originally posted by snufse

(hmmmm working Saturday night, thought you would be out having a Pripps...)

---

declare @DateFrom datetime
declare @DateTo datetime  

set @DateFrom	= dateadd(month, datediff(month, 0, getdate()) - 13, 0) -- start date 
set	@DateTo 	= dateadd(month, datediff(month, 0, getdate()) - 1, 30) -- end date

[code]insert into #Table1
                   (
                    petroex_code,	
                    product_code,
                    product_name,
                    qty_month_1,
                    qty_month_2,
                    qty_month_3,
                    qty_month_4,
                    qty_month_5,
                    qty_month_6,
                    qty_month_7,
                    qty_month_8,
                    qty_month_9,
                    qty_month_10,
                    qty_month_11,
                    qty_month_12
                                                       )
select	petroexcode as PetroEXCode,
        branded as ProductCode,
        substring(name,1,30) as ProductName, 
        sum(case when datepart(month, date) = datepart(month, @DateFrom)                      then gross else 0 end) as quantity1,
	sum(case when datepart(month, date) = datepart(month, dateadd(month, 1,  @DateFrom))  then gross else 0 end) as quantity2,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 2,  @DateFrom))  then gross else 0 end) as quantity3,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 3,  @DateFrom))  then gross else 0 end) as quantity4,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 4,  @DateFrom))  then gross else 0 end) as quantity5,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 5,  @DateFrom))  then gross else 0 end) as quantity6,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 6,  @DateFrom))  then gross else 0 end) as quantity7,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 7,  @DateFrom))  then gross else 0 end) as quantity8,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 8,  @DateFrom))  then gross else 0 end) as quantity9,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 9,  @DateFrom))  then gross else 0 end) as quantity10,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 10, @DateFrom))  then gross else 0 end) as quantity11,
        sum(case when datepart(month, date) = datepart(month, dateadd(month, 11, @DateFrom))  then gross else 0 end) as quantity12

	from      [SFM-TP6000-1].TP6000.dbo.product as p
                  inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
	where     (@DateFrom is null or	date >= @DateFrom) and
	  	  (@DateTo is null or date	< dateadd(day, 1, @DateTo))
	group by  petroexcode, branded, name