Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Retrieve Data with the Earliest Date

    All,

    I'm trying to pull back all data records with the earliest date. I'm still getting all the rows.

    Query
    SELECT
    br.brokerid AS ProducerID, br.name AS "Producer Name",
    bd.brokertype AS "Producer Type", bd.match2 AS "Producer Region",
    bd.match1 AS "Producer Team", bl.licenseid AS "License ID",
    (CASE bl.licstatus
    WHEN 1 THEN 'ACTIVE'
    WHEN 2 THEN 'INACTIVE'
    WHEN 3 THEN 'PROVISIONAL'
    WHEN 4 THEN 'UNKNOWN' ELSE '' END) AS "License Status",
    bl.dateeff AS "Effective Date", bl.dateexp AS "Expiration Date"
    FROM BrokerLicense bl
    LEFT JOIN BrokerDetail bd ON bl.brokerno = bd.brokerno
    LEFT JOIN Broker br ON bd.brokerno = br.brokerno
    WHERE
    br.brokerid = 1111
    GROUP BY br.brokerid, br.name, bd.brokertype, bd.match2, bd.match1, bl.licenseid, bl.licstatus, bl.dateeff, bl.dateexp
    ORDER BY br.brokerid, br.name

    Results:
    ProdID Prod Name Prod Type Region Team Lic# Status EffDate ExpDate

    1111 Bill REP NORTH ATeam 1111 ACTIVE 20071119 20090430

    1111 Bill REP NORTH ATeam 1111 ACTIVE 20090501 20100430

    I need to only pull the second row by the latest EffDate

    Attempts:
    I tried a Left Join with a sub query:
    --LEFT JOIN (SELECT BR2.brokerid AS "BR2BrokerID", MAX(bl2.dateeff) AS "BL2MaxDateEff"
    -- FROM BrokerLicense BL2
    -- LEFT JOIN Broker BR2 ON BL2.brokerno = BR2.brokerno
    -- GROUP BY BR2.brokerid) TBL1
    -- ON br.brokerid = TBL1.BR2BrokerID
    -- AND bl.dateeff = TBL1.BL2MaxDateEff

    I have tried this in the WHERE clause:
    --bl.dateeff = (Select MAX(bl.dateeff) FROM BrokerLicense)

    Just need someone to point me in the right direction.

    I am using SQL SErver 2005

    Thanks.
    Last edited by cadm777; 05-13-09 at 16:26.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    Code:
    WITH TempTable AS
    (SELECT
    	br.brokerid AS ProducerID, 
    	br.name AS "Producer Name",
    	bd.brokertype AS "Producer Type", 
    	bd.match2 AS "Producer Region",
    	bd.match1 AS "Producer Team", 
    	bl.licenseid AS "License ID",
    	(CASE bl.licstatus
    		WHEN 1 THEN 'ACTIVE'
    		WHEN 2 THEN 'INACTIVE'
    		WHEN 3 THEN 'PROVISIONAL'
    		WHEN 4 THEN 'UNKNOWN' ELSE '' 
    	END) AS "License Status",
    	bl.dateeff AS "Effective Date", 
    	bl.dateexp AS "Expiration Date"
    	ROW_NUMBER() OVER(PARTITION BY br.brokerid, br.name, bd.brokertype, bd.match2, bd.match1, bl.licenseid, bl.licstatus
    			ORDER BY bl.dateeff ACS) AS "RowNumber"
    FROM BrokerLicense bl
    	LEFT JOIN BrokerDetail bd ON 
    		bl.brokerno = bd.brokerno
    	LEFT JOIN Broker br ON 
    		bd.brokerno = br.brokerno
    WHERE br.brokerid = 1111
    ) 
    SELECT * 
    FROM TempTable 
    WHERE RowNumber = 1
    ORDER BY ProducerID, "Producer Name"
    I don't have SQL Server at hand here to test my code ...
    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

  3. #3
    Join Date
    May 2009
    Posts
    3
    Wim-

    That did it. Thanks. I just had to ad a comma after
    bl.dateexp AS "Expiration Date"
    and before the ROW_NUMBER()


Posting Permissions

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