Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Red face Unanswered: Retrieve the las record for each month between two dates

    Hi,
    I need to retrieve the last record for each month between two given dates from a unique table that contains on record per day.

    Maybe is an easy sql sentence, but I really dont know how to do it.


    Someone can help me with that?
    I really apreciate it.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please consider to use ROW_NUMBER() function.

    ROW_NUMBER (Transact-SQL)
    Syntax

    ROW_NUMBER ( )
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:

    Code:
    DROP TABLE dbo.DaTable;
    CREATE TABLE dbo.DaTable(
    	id	int	not null,
    	my_date	date	NOT NULL
    )
    
    INSERT INTO dbo.DaTable(id, my_date) VALUES
    (1, '2012-12-01'),
    (2, '2012-12-02'),
    (3, '2012-12-03'),
    (4, '2012-12-04'),
    (5, '2012-12-05'),
    (6, '2012-12-27'),
    (7, '2012-12-28'),
    (8, '2012-12-29'),
    (9, '2012-12-30'),
    (10, '2012-12-31'),
    
    (111, '2013-01-01'),
    (112, '2013-01-02'),
    (113, '2013-01-03'),
    (114, '2013-01-04'),
    (115, '2013-01-05'),
    (116, '2013-01-27'),
    (117, '2013-01-28'),
    (118, '2013-01-29'),
    
    (219, '2013-02-01'),
    (220, '2013-02-02')
    
    --I need to retrieve the last record for each month between two given dates 
    -- from a unique table that contains on record per day.
    DECLARE @startDate	DATE;
    DECLARE @endDate	DATE;
    SET @startDate = '2012-12-25'
    SET @endDate = '2013-02-02'
    
    ;
    WITH CTE AS(
    SELECT id,
    	my_date,
    	ROW_NUMBER() over (PARTITION BY YEAR(my_date), MONTH(my_date) ORDER BY my_date DESC) as RowNum
    FROM DaTable
    WHERE my_date BETWEEN @startdate AND @EndDate
    )
    SELECT * 
    FROM CTE
    WHERE RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Oct 2013
    Posts
    2

    Smile

    Thanks a lot for your help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •