March 19, 2012 at 5:21 am
how to calculate YTD sales values
Here is a sample query and not sure how to get last year sales values for the same date as current year
SELECT TRANDATE,
SUM(CASE WHEN TXTP='POSCSR' THEN (totalexcl - totalexempt)*-1 ELSE (totalexcl - totalexempt) END) as ExclAndExempt,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALEXEMPT*-1 ELSE TOTALEXEMPT END) as TOTALEXEMPT,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALVAT*-1 ELSE TOTALVAT END) as TOTALVAT,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALCOSTEXCL*-1 ELSE TOTALCOSTEXCL END) as TOTALCOSTEXCL,
SUM(CASE WHEN TXTP='POSCSR' THEN totalincl*-1 ELSE totalincl END) as TotalInclVat,
SUM(CASE WHEN TXTP='POSCSR' THEN PROFIT*-1 ELSE PROFIT END) AS PROFIT,
(SUM(CASE WHEN TXTP='POSCSR' THEN PROFIT*-1 ELSE PROFIT END)/
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALCOSTEXCL*-1 ELSE TOTALCOSTEXCL END)
)*100 as GPPERC:(
from TABLE_NAME
WHerE TxtP in('POSCSH','POSASL','POSCSR')
AND TRANDATE >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
GROUP BY TRANDATE
ORDER BY TRANDATE
Thank you in advance
March 19, 2012 at 6:20 am
gerald.hoole (3/19/2012)
how to calculate YTD sales valuesHere is a sample query and not sure how to get last year sales values for the same date as current year
...
AND TRANDATE >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
...
You question is not very clear. Current WHERE clause checks for dates to be equal to or greater than the first day of the current month.
Do you want to filter dates for the last year as from 2nd March 2011 to 1 March 2012? Or something else?
Please read this following article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2012 at 12:22 am
Posting some DDL and sample data would be helpful, but if I understand right you want to create a set of prior year columns (not sure which is sales) based on the transaction date being between the first of the month (last year) and the current date of last year.
You can try something like this but without the DDL I can't syntax check it so you may need to work it a bit to get it where you need it to be.
WITH CTE AS (
SELECT TRANDATE,
SUM(CASE WHEN TXTP='POSCSR' THEN (totalexcl - totalexempt)*-1 ELSE (totalexcl - totalexempt) END) as ExclAndExempt,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALEXEMPT*-1 ELSE TOTALEXEMPT END) as TOTALEXEMPT,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALVAT*-1 ELSE TOTALVAT END) as TOTALVAT,
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALCOSTEXCL*-1 ELSE TOTALCOSTEXCL END) as TOTALCOSTEXCL,
SUM(CASE WHEN TXTP='POSCSR' THEN totalincl*-1 ELSE totalincl END) as TotalInclVat,
SUM(CASE WHEN TXTP='POSCSR' THEN PROFIT*-1 ELSE PROFIT END) AS PROFIT,
(SUM(CASE WHEN TXTP='POSCSR' THEN PROFIT*-1 ELSE PROFIT END)/
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALCOSTEXCL*-1 ELSE TOTALCOSTEXCL END)
)*100 as GPPERC
from TABLE_NAME
WHerE TxtP in('POSCSH','POSASL','POSCSR')
GROUP BY TRANDATE
)
SELECT MAX(TRANDATE) AS TRANDATE
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN ExclAndExempt ELSE 0 END) AS ExclAndExempt
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN TOTALEXEMPT ELSE 0 END) AS TOTALEXEMPT
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN TOTALVAT ELSE 0 END) AS TOTALVAT
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN TOTALCOSTEXCL ELSE 0 END) AS TOTALCOSTEXCL
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN TotalInclVat ELSE 0 END) AS TotalInclVat
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN ExclAndExempt ELSE 0 END) AS PROFIT
,SUM(CASE WHEN DATEPART(year, TRANDATE) = DATEPART(year, GETDATE()) THEN PROFIT ELSE 0 END) AS GPPERC
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN ExclAndExempt ELSE 0 END) AS PY_ExclAndExempt
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN TOTALEXEMPT ELSE 0 END) AS PY_TOTALEXEMPT
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN TOTALVAT ELSE 0 END) AS PY_TOTALVAT
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN TOTALCOSTEXCL ELSE 0 END) AS PY_TOTALCOSTEXCL
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN TotalInclVat ELSE 0 END) AS PY_TotalInclVat
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN ExclAndExempt ELSE 0 END) AS PY_PROFIT
,SUM(CASE WHEN DATEPART(year, TRANDATE) <> DATEPART(year, GETDATE()) THEN PROFIT ELSE 0 END) AS PY_GPPERC
FROM (
SELECT TRANDATE, ExclAndExempt, TOTALEXEMPT, TOTALVAT, TOTALCOSTEXCL, TotalInclVat, PROFIT, GPPERC
FROM CTE
WHERE TRANDATE >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
UNION ALL
SELECT TRANDATE, ExclAndExempt, TOTALEXEMPT, TOTALVAT, TOTALCOSTEXCL, TotalInclVat, PROFIT, GPPERC
FROM CTE
WHERE TRANDATE >= DATEADD(year, -1, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) AND
TRANDATE <= DATEADD(year, -1, GETDATE())
) x
GROUP BY DATEPART(day, TRANDATE)
Note that prior year columns are all prefixed with PY_
And by the way, this part is really dangerous if the denominator happens to be zero.
(SUM(CASE WHEN TXTP='POSCSR' THEN PROFIT*-1 ELSE PROFIT END)/
SUM(CASE WHEN TXTP='POSCSR' THEN TOTALCOSTEXCL*-1 ELSE TOTALCOSTEXCL END)
)*100 as GPPERC
Edited to a cleaner version of the code.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 20, 2012 at 1:25 am
Thank you for the example code, i want to display previous year sales for the same date as as current date but only 1 year back
thank you in advance
March 20, 2012 at 3:59 am
If you need help and want to get relevant one and quick, you will need to follow forum etiquette as described here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Otherwise, people here left to guess what exactly you want.
Of cause is up to you. There is a probability that some one here will guess it right from what you posted and you will get the answers. However, based on my experience here, more likely, you will receive some irrelevant code as posted by previous man, who tried to guess your requirements, but wasn't much successful in it, looks like ...
Can you please answer questions I've posted in the first reply? At least it will be more precise...
March 21, 2012 at 1:49 pm
Gerald,
If you want something like comparing the current year to the same period in the prior year, setting up a proper calendar table would immensely help out with the heavy lifting.
Here's an example of using a monthly type calendar table that compares one year against the prior year:
http://www.sqlservercentral.com/articles/T-SQL/70482/
Todd Fifield
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply