Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: Help with MAX date

    I have two tables that are joined by a version id. The information I need is the one row for MAX effective_date, Carrier_number, Procedure_code combination. This is a small sample of the data for just two carriers and one procedure code but I will need to run for the entire table returning one row for each carrier_number/procedure_code pair where Effective_date is greatest.
    Code:
    CARRIER_NUMBER PROCEDURE_CODE MAXRENTALMONTHS effective_date 
    11004                      K0826                       0                                    1990-01-01 00:00:00.000
    11004                      K0826                       0                                    2007-01-01 00:00:00.000
    11004                      K0826                       0                                    2007-11-06 00:00:00.000
    11004                      K0826                      13                                   2009-01-01 00:00:00.000
    11004                      K0826                      13                                   2010-01-01 00:00:00.000
    11057                      K0826                      0                                     1990-01-01 00:00:00.000
    11057                      K0826                      0                                     2010-01-01 00:00:00.000
    11057                      K0826                      0                                     2007-11-06 00:00:00.000
    11057                      K0826                      13                                   2009-01-01 00:00:00.000
    This is what I have so far:
    Code:
    SELECT p.[CARRIER_NUMBER]
    ,p.[PROCEDURE_CODE],p.[MAXRENTALMONTHS]
    ,MAX(v.effective_date) AS MaxEffDate
    FROM [fstrkdb_1].[dbo].[Profile] p INNER JOIN ProcCodeLinkVersion v ON p.version_id = v.version_id 
    WHERE CARRIER_NUMBER IN(11057, 11004) AND PROCEDURE_CODE = 'K0826'
    GROUP BY p.PROCEDURE_CODE,p.CARRIER_NUMBER,p.MAXRENTALMONTHS
    ORDER BY p.CARRIER_NUMBER, p.PROCEDURE_CODE
    Which gives me the following:
    Code:
     
    
    CARRIER_NUMBER PROCEDURE_CODE MAXRENTALMONTHS MaxEffDate 11004 K0826 0 2007-11-06 00:00:00.000 11004 K0826 13 2010-01-01 00:00:00.000 11057 K0826 0 2010-01-01 00:00:00.000 11057 K0826 13 2009-01-01 00:00:00.000
    BUT what I need is just this…..Please help!!
    Code:
     
    CARRIER_NUMBER PROCEDURE_CODE MAXRENTALMONTHS MaxEffDate
    11004           K0826           13           2010-01-01 00:00:00.000
    11057           K0826           0            2010-01-01 00:00:00.000
    Last edited by BigRedFish; 07-27-11 at 19:20.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Air coding, but should work:
    Code:
    ;with OrderedData as
    (SELECT	p.[CARRIER_NUMBER],
    	p.[PROCEDURE_CODE],
    	p.[MAXRENTALMONTHS],
    	v.effective_date,
    	ROW_NUMBER() over(Partition by p.[CARRIER_NUMBER], p.[PROCEDURE_CODE], p.[MAXRENTALMONTHS] order by v.effective_date desc) as RowNumber
    FROM	[fstrkdb_1].[dbo].[Profile] p
    	INNER JOIN ProcCodeLinkVersion v ON p.version_id = v.version_id
    WHERE	CARRIER_NUMBER IN(11057, 11004)
    	AND PROCEDURE_CODE = 'K0826')
    select	*
    from	OrderedData
    where	RowNumber = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2011
    Posts
    3

    Nope :(

    Sorry for the bad formatting I will try to fix so it is easier to see..
    but this will give me the same results as what I have by using Group By... two number "1"s

    Code:
    CARRIER_NUMBER  PROCEDURE_CODE  MAXRENTALMONTHS     effective_date     	RowNumber
    11004		K0826 		0		2007-11-06 00:00:00.000		1
    11004		K0826 		0		2007-01-01 00:00:00.000		2
    11004		K0826 		0		1990-01-01 00:00:00.000		3
    11004		K0826 		13		2010-01-01 00:00:00.000		1
    11004		K0826 		13		2009-01-01 00:00:00.000		2
    11057		K0826 		0		2007-11-06 00:00:00.000		2
    11057		K0826 		0		2010-01-01 00:00:00.000		1
    11057		K0826 		0		1990-01-01 00:00:00.000		3
    11057		K0826 		13		2009-01-01 00:00:00.000		1

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Since MaxRentalMonths doesn't seem to be used to determine the row you want, just remove " , p.[MAXRENTALMONTHS] " from the ROW_NUMBER() OLAP Function that Blindman supplied and you should get what you want.
    Code:
    with OrderedData as
    (SELECT	p.[CARRIER_NUMBER],
    	p.[PROCEDURE_CODE],
    	p.[MAXRENTALMONTHS],
    	v.effective_date,
    	ROW_NUMBER() over(Partition by p.[CARRIER_NUMBER], p.[PROCEDURE_CODE] order by v.effective_date desc) as RowNumber
    FROM	[fstrkdb_1].[dbo].[Profile] p
    	INNER JOIN ProcCodeLinkVersion v ON p.version_id = v.version_id
    WHERE	CARRIER_NUMBER IN(11057, 11004)
    	AND PROCEDURE_CODE = 'K0826')
    select	*
    from	OrderedData
    where	RowNumber = 1

  5. #5
    Join Date
    Jul 2011
    Posts
    3

    Thanks!

    Thanks to both of you! I think that will do it...
    I will verify with some specific carrier numbers and codes in the morning at work but just ran quick query and looks like it is giving me what I need.
    Code:
    with OrderedData as
    (SELECT	p.[CARRIER_NUMBER],
    	p.[PROCEDURE_CODE],
    	p.[MAXRENTALMONTHS],
    	v.effective_date,
    	ROW_NUMBER() over(Partition by p.[CARRIER_NUMBER], p.[PROCEDURE_CODE] order by v.effective_date desc) as RowNumber
    FROM	[fstrkdb_1].[dbo].[Profile] p
    	INNER JOIN ProcCodeLinkVersion v ON p.version_id = v.version_id)
    select	*
    from	OrderedData
    where	RowNumber = 1 AND MAXRENTALMONTHS != 13
    ORDER BY CARRIER_NUMBER, PROCEDURE_CODE

Posting Permissions

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