capture data for each quarter

tips54
tips54 used Ask the Experts™
on
Hello experts,

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

Current calender  Quarter : ?

Current calender   Quarter  Last year: ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Harish VargheseProject Leader

Commented:
Please provide the structure of the tables.

Author

Commented:
Invoice_date           Customer_ ID               total_amount
1/1/13                      1                                   100
3/31/13                    1                                   500
4/1/13                       2                                  100
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Project Leader
Commented:
Hello,
Select CurrentQuarterSales = SUM(case when Year(Invoice_Date) = YEAR(GetDate()) and Ceiling(month (Invoice_Date)/3) = Ceiling(month (GETDATE())/3) then Total_Amount Else 0 End),
	LastYearSameQuarterSales = SUM(case when Year(Invoice_Date) = YEAR(GetDate()) - 1 and Ceiling(month (Invoice_Date)/3) = Ceiling(month (GETDATE())/3) then Total_Amount Else 0 End)
From Sales

Open in new window

Group By CUstomer_ID if required.

-Harish

Author

Commented:
That did it. Thanks Harish.

Author

Commented:
perfect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial