Wednesday, August 6, 2008

GP invoice year total of each customer between the current and previous year

Declare
@CurrentYear_StartDate As DateTime,
@CurrentYear_EndDate As DateTime,
@PreviousYear_StartDate As DateTime,
@PreviousYear_EndDate As DateTime,
@TodaysDate As DateTime,
@CurrentYear As Int

set @CurrentYear = year(getdate())
set @CurrentYear_StartDate = convert(DateTime, '01/01/' + cast(@CurrentYear As varchar))
set @CurrentYear_EndDate = convert(DateTime, '12/31/' + cast(@CurrentYear As varchar))
set @PreviousYear_StartDate = DateAdd(year,-1, @CurrentYear_StartDate)
set @PreviousYear_EndDate = DateAdd(year,-1, @CurrentYear_EndDate)

Select Custnmbr, Custname, Sum(PreviousYear_Invoice) As PreviousYear_Invoice, Sum(CurrentYear_Invoice) As CurrentYear_Invoice,
Sum(CurrentYear_Invoice) - Sum(PreviousYear_Invoice) As Variance
From
(
Select Custnmbr, Custname, PreviousYear_Invoice=0, Sum(Docamnt) As CurrentYear_Invoice
From sop30200 c
Where DocDate >= @CurrentYear_StartDate and DocDate <= @CurrentYear_EndDate and SOPType=3 and voidstts=0
Group By Custnmbr, Custname
UNION
Select Custnmbr, Custname, Sum(Docamnt) As PreviousYear_Invoice, CurrentYear_Invoice=0
From sop30200 c
Where DocDate >= @PreviousYear_StartDate and DocDate <= @PreviousYear_EndDate and SOPType=3 and voidstts=0
Group By Custnmbr, Custname
) As Table_Union
Group By Custnmbr, Custname
Order By Custname

No comments: