Post reply

SQL Query with latest record

  • January 22, 2020 at 10:40 am

    #3717304

    i have table please see the screen shot

     

    I want the CHAR_TYPE_CD "CNSMP " which ever is the latest one as per the "EFFDT" and CHAR_TYPE_CD "UNITS" which ever is the latest one.

     

    For example in this case

    Premise ID 1589612791

    CNSMP   : COM

    UNITS   :40

     

    This is the query I was working on

    select  PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL

    from CI_PREM_CHAR

    WHERE CHAR_TYPE_CD in ('CNSMP', 'UNITS') ;

     

    nut go stuck need help

     

     

     

  • January 22, 2020 at 10:42 am

    #3717305

    sorry attaching the screen shot of the table

    Attachments:
    You must be logged in to view attached files.

  • January 22, 2020 at 11:00 am

    #3717307

    Hello,

    It would help us if you could provide a create table and some example data as detailed here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

    With this we know we are using the same data types as you.

    I might know a way to get the result you need but I'd like to test it before posting.

  • January 22, 2020 at 11:02 am

    #3717308

    Not actually sure what the goal is, so i am guessing a bit, both queries should produce the same result

    1 Method is getting the Max  EEFDT values for CHAR_TYPE_CD in ('CNSMP', 'UNITS') and join the values with the whole table

    select  PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL
    from CI_PREM_CHAR AS UF
     inner join (
     select CHAR_TYPE_CD
       ,EFFDT=max(EFFDT)
     from CI_PREM_CHAR
     WHERE CHAR_TYPE_CD in ('CNSMP', 'UNITS')
     group by CHAR_TYPE_CD
    ) AS F

    on F.EFFDT = UF.EFFDT
       and F.CHAR_TYPE_CD = UF.CHAR_TYPE_CD

    2 Method getting them by ordering them and only outputting the one with the highest value

    select  top 1 PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL
    from CI_PREM_CHAR 
    WHERE CHAR_TYPE_CD in ('CNSMP')
    order by EFFDT desc

    UNION ALL

    select  top 1 PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL
    from CI_PREM_CHAR 
    WHERE CHAR_TYPE_CD in ( 'UNITS')
    order by EFFDT desc

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • January 22, 2020 at 11:03 am

    #3717309

    There are a few ways to approach this. I'd usually use a correlated sub-query as below:

    SELECT
      PremChar.PREM_ID AS PremiseID
    , MAX(CASE -- The MAX will ignore NULLs unless there are no non-NULL values
      WHEN PremChar.CHAR_TYPE_CD = 'CNSMP'
      THEN PremChar.CHAR_VAL -- so we only return CHAR_VAL when CHAR_TYPE_CD = 'CNSMP'
      ELSE NULL
      END) AS CNSMP
    , MAX(CASE
      WHEN PremChar.CHAR_TYPE_CD = 'UNITS'
      THEN PremChar.UNITS
      ELSE NULL
      END) AS CNSMP
    FROM CI_PREM_CHAR AS PremChar
    WHERE PremChar.CHAR_TYPE_CD IN ('CNSMP', 'UNITS')
    AND PremChar.EFFDT =
    ( -- this correlated sub-query means we only return rows where the EFFDT
    SELECT TOP 1 -- is the most recent EFFDT for that PREM_ID and CHAR_TYPE_CD
      csq_PremChar.EFFDT
    FROM CI_PREM_CHAR AS csq_PremChar
    WHERE csq_PremChar.PREM_ID = PremChar.PREM_ID
    AND csq_PremChar.CHAR_TYPE_CD = PremChar.CHAR_TYPE_CD
    ORDER BY
      csq_PremChar.EFFDT DESC
    )
    GROUP BY
      PremChar.PREM_ID

  • January 22, 2020 at 12:52 pm

    #3717352

    As per your other post, your best posting this to an ORACLE forum not a Microsoft SQL Server forum.

     

    Again ROW_NUMBER() and a CTE as per the "ORACLE TOP N" will do what you need here

  • January 26, 2020 at 9:04 pm

    This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply