IMPORTANT:
Commands with UPDATE, DELETE or DROP can ruin your data! Please use these queries with caution. We highly recommend that you backup your database before trying out these commands and to load them up first on a test server. Use at your own risk.

Associated SQL Command:
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 ITEMNMBR, ITEMDESC, Sum(PreviousYear_Sales) As PreviousYear_Sales, Sum(CurrentYear_Sales) As CurrentYear_Sales, Sum(CurrentYear_Sales) - Sum(PreviousYear_Sales) As Variance From ( Select ITEMNMBR, ITEMDESC, PreviousYear_Sales=0, Sum(XTNDPRCE) As CurrentYear_Sales From sop30200 m Inner Join sop30300 d on d.sopnumbe = m.sopnumbe and m.soptype=3 Where DocDate >= @CurrentYear_StartDate and DocDate <= @CurrentYear_EndDate and m.SOPType=3 and voidstts=0 Group By ITEMNMBR, ITEMDESC UNION Select ITEMNMBR, ITEMDESC, Sum(XTNDPRCE) As PreviousYear_Sales, CurrentYear_Sales=0 From sop30200 m Inner Join sop30300 d on d.sopnumbe = m.sopnumbe and m.soptype=3 Where DocDate >= @PreviousYear_StartDate and DocDate <= @PreviousYear_EndDate and m.SOPType=3 and voidstts=0 Group By ITEMNMBR, ITEMDESC ) As Table_Union Group By ITEMNMBR, ITEMDESC Order by CurrentYear_Sales DESC
Viewed: 802 times

Submitted by: Neville

About Neville

View complete list

previous script

next script