Extract QTD data

I need to help with the following syntax.  I need to capture sales for the current  Quarter To Date and sales for the same quarter To Date last year.

Current calender  QTD : ?

QTD  Last year: ?

Invoice_date           Customer_ ID               total_amount
1/1/13                      1                                   100
2/11/14                    1                                   200
3/31/13                    1                                   500
4/1/13                       2                                  100

I thought the thread below resolved it,  But it's giving full quarter for last year not last year QTD.

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28381957.html
tips54Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT
    --CustomerId,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, invoice_date), 0) AS Date,
    SUM(total_amount) AS QTD_Total
FROM dbo.tablename
WHERE
    --current qtd OR ...
    ((invoice_date >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)) OR
    --... prev year qtd thru today's date.
     (invoice_date >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 4, 0) AND
      invoice_date < DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0))))
GROUP BY
    --CustomerId,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, invoice_date), 0)
ORDER BY
    --CustomerId,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, invoice_date), 0)
0
 
tips54Author Commented:
Scott,
Could this same code be used to capture MTD,  YTD?
Expected outcome:

Weekly,  Weekly LY,  MTD, MTD LY, QTD ,  QTD LY, YTD,  YTD LY.
0
 
Scott PletcherSenior DBACommented:
Sure.  You just adjust the DATEADD(...) computations accordingly.

The MTD, QTD and YTD are easy.

Would need slightly more details on weekly/wtd.
0
 
tips54Author Commented:
I'm making progress. I'm not certain of the results yet.
0
 
tips54Author Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.