January 22, 2020 at 10:40 am
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 11:00 am
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
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
January 22, 2020 at 11:03 am
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
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