Group by date ranges

Hi..
I have a Sales table with dates (Sale_Date).
I also have a table of Qtrs  (1st,2nd,3rd,4th) - Business Qtrs

The table of Qtrs looks something like this

START            END
1/1/2015        3/31/2015          1ST
4/1/2015        6/30/2015          2ND
....

How can I group and sum my Sales data by QTR?     Sales date must fall in the Qtr date range.

thx
LVL 1
JElsterAsked:
Who is Participating?
 
Mark ElySenior Coldfusion DeveloperCommented:
Using Datepart you can get the quarter the date falls within.  Therefore you group by the quarter by using the date of the sales was placed.

SELECT DATEPART(q, YourSalesDate) AS Quarter, SUM(Sales)
FROM NameOfTable
GROUP BY DATEPART(q, YourSalesDate)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in short

select q.name
, sum(s.sales)
from sales s
join quarters q
  on s.saledate between q.startdate and q.enddate
group by q.name
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.