group data by date range

  • 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 ?

  • ...
    WHERE MONTH(startdate) = MONTH(enddate)
    AND YEAR(startdate) = YEAR(enddate)
    GROUP BY
         YEAR(startdate)
    ,    MONTH(startdate)

  • What about those records where startdate is after enddate?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • 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