# How to get YTD & Quarterly sales??? Any help please?

• gerald.hoole

SSC Enthusiast

Points: 100

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

• Eugene Elutin

SSC Guru

Points: 59322

gerald.hoole (3/19/2012)

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

...

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?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

• Dwain Camps

SSC Guru

Points: 86893

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

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

• gerald.hoole

SSC Enthusiast

Points: 100

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

• Eugene Elutin

SSC Guru

Points: 59322

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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

• tfifield

SSCrazy Eights

Points: 9655

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 6 (of 6 total)