Solved

capture data for each quarter

Posted on 2014-03-06
6
369 Views
Last Modified: 2014-03-07
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: ?
0
Comment
Question by:tips54
  • 4
  • 2
6 Comments
 

Author Comment

by:tips54
ID: 39909882
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39909983
Please provide the structure of the tables.
0
 

Author Comment

by:tips54
ID: 39910005
Invoice_date           Customer_ ID               total_amount
1/1/13                      1                                   100
3/31/13                    1                                   500
4/1/13                       2                                  100
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 39910194
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
0
 

Author Comment

by:tips54
ID: 39912375
That did it. Thanks Harish.
0
 

Author Closing Comment

by:tips54
ID: 39912376
perfect.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question