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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment