We help IT Professionals succeed at work.

Group by date ranges

JElster
JElster asked
on
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
Comment
Watch Question

Senior Coldfusion Developer
Commented:
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

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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