May 26, 2017 at 4:13 am
I have some data that contains a startdate column, enddate column and a price. I want to be able to group it by month where the start date & end date are in the same month and have a sum of the price. for example
startdate enddate price
01/01/2017 15/01/2017 100
05/01/2017 22/01/2017 200
01/03/2017 10/03/2017 25
22/03/2017 19/03/2017 45
10/04/2017 12/04/2017 50
etc, etc
so Id get
jan 2017 300
mar 2017 70
apr 2017 50
etc, etc
whats the best way to go about this ?
May 26, 2017 at 4:24 am
...
WHERE MONTH(startdate) = MONTH(enddate)
AND YEAR(startdate) = YEAR(enddate)
GROUP BY
YEAR(startdate)
, MONTH(startdate)
May 26, 2017 at 4:27 am
What about those records where startdate is after enddate?
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 26, 2017 at 4:33 am
Good spot. A well-designed table would have a CHECK constraint to stop that from happening. (Un)fortunately, though, it doesn't change the logic of this query, since we're only need to concern ourselves with one of the dates, once we've established that both are in the same month.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply