Solved

capture data for each quarter

Posted on 2014-03-06
6
377 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

679 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