3 Months sales average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

3 Months sales average

Author

Topic

rusmanicai
Starting Member

13 Posts

Posted - 2011-12-29 : 12:59:11

Hello,
I need to find a way to get the sales for 13,12 and 11 months ago.
I wrote a little query that works only for month of December.
The user will be asked to type the start and end date for the current month, and the report I am writing need to show the user the sales for selected period and the sales goal which is the average I am trying to find.
Please tell me what am I missing.

declare
@startdate as varchar(50),
@enddate as varchar(50)
set @startdate='2011-11-01'
set @enddate='2011-11-30'
select
(
--sum of all line items from invoices (except die) for Dec 2010
(select SUM(artransactionline.netval) from ARTransactionLine
inner join ARTransaction on ARTransaction.TransactionNumber=ARTransactionLine.TransactionNumber
where artransactionline.JobNumber=left(ARTransaction.DocumentNumber,6)
and year(artransaction.TransactionDate)>=DATEPART(yy,@startdate)-1
and year(artransaction.TransactionDate)<=DATEPART(yy,@enddate)-1
and month(artransaction.TransactionDate)>=DATEPART(mm,@startdate)
and month(artransaction.TransactionDate)<=DATEPART(mm,@enddate)
and day(artransaction.TransactionDate)>=DATEPART(dd,@startdate)
and day(artransaction.TransactionDate)<=DATEPART(dd,@enddate)
and TransactionType='invoice' and Voided=0
and ARTransactionLine.Description not like '%die%') +
--sum of all line items from invoices (except die) for Nov 2010
(select SUM(artransactionline.netval) from ARTransactionLine
inner join ARTransaction on ARTransaction.TransactionNumber=ARTransactionLine.TransactionNumber
where artransactionline.JobNumber=left(ARTransaction.DocumentNumber,6)
and year(artransaction.TransactionDate)>=DATEPART(yy,@startdate)-1
and year(artransaction.TransactionDate)<=DATEPART(yy,@enddate)-1
and month(artransaction.TransactionDate)>=DATEPART(mm,@startdate)-1
and month(artransaction.TransactionDate)<=DATEPART(mm,@enddate)-1
and day(artransaction.TransactionDate)>=DATEPART(dd,@startdate)
and day(artransaction.TransactionDate)<=DATEPART(dd,@enddate)
and TransactionType='invoice' and Voided=0
and ARTransactionLine.Description not like '%die%') +
--sum of all line items from invoices (except die) for Jan 2011
(select SUM(artransactionline.netval) from ARTransactionLine
inner join ARTransaction on ARTransaction.TransactionNumber=ARTransactionLine.TransactionNumber
where artransactionline.JobNumber=left(ARTransaction.DocumentNumber,6)
and year(artransaction.TransactionDate)>=DATEPART(yy,@startdate)
and year(artransaction.TransactionDate)<=DATEPART(yy,@enddate)
and month(artransaction.TransactionDate)>=DATEPART(mm,@startdate)-11
and month(artransaction.TransactionDate)<=DATEPART(mm,@enddate)-11
and day(artransaction.TransactionDate)>=DATEPART(dd,@startdate)
and day(artransaction.TransactionDate)<=DATEPART(dd,@enddate)
and TransactionType='invoice' and Voided=0
and ARTransactionLine.Description not like '%die%')
)/3
as DecemberAverage

Thank you,
Julia.

RJulia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 13:40:53

It may be easier to find the date range first and then limit the query to that range rather than trying to compare days and months and years.

For example, if you want to find the dates 11 months ago, you could do this:

DECLARE @startdate  AS VARCHAR(50),
        @enddate    AS VARCHAR(50)
SET @startdate = '2011-11-01'
SET @enddate = '2011-11-30'

DECLARE @months INT; SET @months = 11;

-- find the start of the month 11 months prior to end date
SELECT dateadd(mm,datediff(mm,0,@enddate)-@months,'19000101')
-- find the start of the month following the month 11 months prior.
SELECT dateadd(mm,datediff(mm,0,@enddate)-@months+1,'19000101')

Now you can use those dates in your query. Note that I am using >= on the lower side and strictly < on the upper side.

SELECT
	SUM(artransactionline.netval)
FROM
	ARTransactionLine
	INNER JOIN ARTransaction
		ON  ARTransaction.TransactionNumber = ARTransactionLine.TransactionNumber
WHERE
	artransactionline.JobNumber = LEFT(ARTransaction.DocumentNumber, 6)
	AND artransaction.TransactionDate >= dateadd(mm,datediff(mm,0,@enddate)-@months,'19000101')
	AND artransaction.TransactionDate < dateadd(mm,datediff(mm,0,@enddate)-@months+1,'19000101')
	AND TransactionType = 'invoice'
	AND Voided = 0
	AND ARTransactionLine.Description NOT LIKE '%die%'

If you wanted to find the sum for the 3 months, all you would have to do is replace the +1 with +3 (or an appropriate positive or negative number on one date or the other).

rusmanicai
Starting Member

13 Posts

Posted - 2011-12-29 : 15:08:00

Thanks!

RJulia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources