Filtering rows that have same value in column but different max date

Question

I have a query that returns results.

    declare @value varchar(500) = 1

    SELECT cnb.ndc_id
        ,cnb.contract_num_val
        ,cnb.quote_price
        ,CONVERT(VARCHAR(10), cnb.eff_dt, 101) AS 'eff_dt'
        ,cnb.discount_pct
        ,cnb.rebate_pct
        ,cnb.award_type
        ,cnb.contract_ndc_brg_id
        ,(
            SELECT SUBSTRING((
                        SELECT ',' + gm.item_name AS [text()]
                        FROM GM_PROGRAM gm
                        JOIN [CONTRACT_ATTR] ca ON ca.[ATTR_VAL] = gm.Item_Id
                        WHERE field_id = 239
                            AND ca.item_id = (
                                SELECT item_id
                                FROM [CONTRACT_ATTR]
                                WHERE attr_val = cnb.contract_num_val
                                    AND field_id = 413
                                )
                        FOR XML PATH('')
                        ), 2, 1000)
            ) AS 'programs'
    FROM [ndc_attr] ndc
    INNER JOIN contract_ndc_brg cnb ON ndc.attr_val = cnb.ndc_id
    WHERE ndc.field_id = 69

I am trying to add a filter where for each unique contract_num_val it will only return the one with the highest date.

First I tried to add MAX() around CONVERT(VARCHAR(10), cnb.eff_dt, 101) and then add a group by clause. But it wouldn't run unless I added everything to group by then it just returned the same results.

Then looking here I tried to make a simpler select that I could use to get back the keys I need to filter. Based on this: SQL Select only rows with Max Value on a Column

This once again doesn't run:

select contract_ndc_brg_id, max(eff_dt) from contract_ndc_brg group by contract_num_val

I have to add contract_ndc_brg_id to the group by then it jusyt returns everything.

What am I doing wrong and how do I fix it?

What data type is eff_dt? – DJSweetness Jun 09 '17 at 16:40 — DJSweetness, Jun 09 '17 at 16:40
@DJSweetness It's a date. – David Tunnell Jun 09 '17 at 16:42 — David Tunnell, Jun 09 '17 at 16:42

score 3 · Accepted Answer · answered Jun 09 '17 at 16:42

3

Add this inner join

inner join
(select contract_num_val, max(eff_dt) dt 
from  contract_ndc_brg 
group by contract_num_val) cnb2 on cnb2.dt = cnb.eff_dt and cnb2.contract_num_val =cnb.contract_num_val

answered Jun 09 '17 at 16:42

S3S

24,809
5
26
45

score 3 · Answer 2 · answered Jun 09 '17 at 16:44

Using cross apply():

declare @value varchar(500) = 1

select cnb.ndc_id
  , cnb.contract_num_val
  , cnb.quote_price
  , convert(varchar(10), cnb.eff_dt, 101) as 'eff_dt'
  , cnb.discount_pct
  , cnb.rebate_pct
  , cnb.award_type
  , cnb.contract_ndc_brg_id
  , (
  select substring((
     select ',' + gm.item_name as [text()]
     from gm_program gm
     inner join [contract_attr] ca on ca.[attr_val] = gm.Item_Id
     where field_id = 239 and ca.item_id = (
       select item_id
       from [contract_attr]
       where attr_val = cnb.contract_num_val and field_id = 413
       )
     for xml path('')
     ), 2, 1000)
  ) as 'programs'
from [ndc_attr] ndc
  cross apply (
    select top 1 *
    from contract_ndc_brg i 
    where ndc.attr_val = i.ndc_id
    order by i.eff_dt desc
    ) cnb
where ndc.field_id = 69

Filtering rows that have same value in column but different max date

2 Answers2