CODEWITHSUNDEEP
sqlsql-server
Scelo
Scelo

Reputation: 45

How to get the first and the last record of every month within a time range in sql

I am trying to get the first record and the last record of each and every month in a given date range using sql. I have a sample code where i have just selected everything within the date range now i have to extract the first and the last records of each and every month.

SELECT  PurOrderNum,
        OrderDate
FROM    Purchasing.PurOrder
WHERE   OrderDate >= '2013-02-28'
        AND OrderDate <= '2014-12-29'

Upvotes: 2

Views: 1202

Answers (1)

Jarle Bj&#248;rnbeth
Jarle Bj&#248;rnbeth

Reputation: 429

You could try row_number():

WITH FilterOrders AS (
    SELECT
    *
    , MinDatePrio = ROW_NUMBER() OVER (PARTITION BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY OrderDate, PurOrderNum)
    , MaxDatePrio = ROW_NUMBER() OVER (PARTITION BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY OrderDate DESC, PurOrderNum DESC)
    FROM Purchasing.PurOrder
    WHERE OrderDate BETWEEN '2013-02-28' AND '2014-12-29'
) SELECT PurOrderNum, OrderDate FROM FilterOrders
WHERE MinDatePrio = 1 OR MaxDatePrio = 1

Result:

enter image description here

Upvotes: 2

Related Questions