SQL Query with latest record

Question

Post reply

SQL Query with latest record

aznabeel

SSC Veteran

Points: 203
More actions
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 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

aznabeel SSC Veteran Points: 203 More actions · Answer 1

sorry attaching the screen shot of the table

Attachments:

You must be logged in to view attached files.

as_1234 SSCrazy Points: 2842 More actions · Answer 2

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.

ktflash Default port Points: 1400 More actions · Answer 3

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

chris.johnson.0120 SSC-Addicted Points: 491 More actions · Answer 4

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

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