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

    Thank you in advance

  • 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?

    Please read this following article:

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

    How to post your question to get the best and quick help[/url]

  • 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?

    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

  • 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

    thank you in advance

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

    How to post your question to get the best and quick help[/url]

  • 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)

You must be logged in to reply to this topic. Login to reply