Show customer's invoice total per year, with variance computation from another year | Collection of Useful Microsoft Dynamics GP Sql Commands, Tips and Tricks

	GP-DYNAMICS
	Custom Programming

Collection of Useful Microsoft Dynamics GP Sql Scripts, Tips and Tricks

Show customer's invoice total per year, with variance computation from another year

Long Description:
Let us take the invoice year summary query one step further. Wouldn't it be quite useful to see it compared to another fiscal year's sales?

This query gets the invoice year total of each customer between the current and previous year and presents the variance figure between the 2 summary amounts.

Hope you find it useful!

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 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
Viewed: 846 times

Submitted by: WTF

About WTF

View complete list

previous script

next script

Did you find this script useful?
Please reciprocate by submitting your own script
or any proven advice regarding Dynamics GP use. Click here.

Send the script above to a friend

Send to EMAIL: *
Short MESSAGE: *
Sender NAME: *
Sender EMAIL:
Validation code: *	Enter 032

Trademarks: Microsoft™, Microsoft Dynamics™ and/or Microsoft logos, products, and services referenced in this site are either trademarks or registered trademarks of Microsoft Corporation.

Disclaimer: This product is not officially endorsed by Microsoft. Furthermore, this website does not imply representation of Dynamics™ or relationship with Microsoft in any form.