Hi,
I have a requirement to get latest record based on a group and updated datetime.
Attached screenshot of the requirement:
Query of the requirement:
--CNSMR
DECLARE @CNSMR TABLE(
cnsmr_id INT,
cnsmr_nm_lst_txt VARCHAR(100),
cnsmr_nm_first_txt VARCHAR(100)
)
INSERT INTO @CNSMR
SELECT '1','Test1','Test1' UNION ALL
SELECT '2','Test2','Test2' UNION ALL
SELECT '3','Test3','Test3' UNION ALL
SELECT '4','Test4','Test4'
--SELECT *FROM @CNSMR
--CNSMR_PHN
DECLARE @CNSMR_PHN TABLE(
cnsmr_phn_id INT,
cnsmr_id INT,
phn_typ VARCHAR(10),
phn_nmbr VARCHAR(20),
upsrt_dttm DATETIME
)
INSERT INTO @CNSMR_PHN
SELECT '1','1','Home','989282938','2021-01-01 12:00:00' UNION ALL
SELECT '2','1','Home','989284444','2021-03-01 12:00:00' UNION ALL
SELECT '3','1','Work','989281111','2021-02-12 12:10:00' UNION ALL
SELECT '4','1','Work','989284444','2021-02-12 12:20:00' UNION ALL
SELECT '5','1','Work','989281222','2021-01-12 06:10:00' UNION ALL
SELECT '6','1','Cell','989281999','2021-04-09 06:10:00' UNION ALL
SELECT '7','2','Home','989280001','2021-04-09 06:10:00' UNION ALL
SELECT '8','2','Work','989280002','2021-04-09 06:10:00' UNION ALL
SELECT '9','2','Cell','989280003','2021-04-08 06:10:00' UNION ALL
SELECT '10','2','Cell','989280004','2021-04-09 06:10:00' UNION ALL
SELECT '11','4','Home','989280005','2021-04-09 06:10:00'
--SELECT *FROM @CNSMR_PHN
--EXPECTED OUPTUT
SELECT '1' cnsmr_id,'Test1' cnsmr_nm_lst_txt,'Test1' cnsmr_nm_first_txt,'989284444' home_phn_nmbr,'989284444' work_phn_nmbr,'989281999' cell_phn_nmbr UNION ALL
SELECT '2','Test2','Test2','989280001','989280002','989280004' UNION ALL
SELECT '3','Test3','Test3','','','' UNION ALL
SELECT '4','Test4','Test4','989280005','',''
Requirement is there are cnsmr and cnsmr_phn table and I need to get latest phone number for each type for each cnsmr_id based on upsrt_dttm, if there is no phone number for a consumer/type then populate blank but every cnsmr_id should be in the output.
This has to be written without cte as complex query is not supported by tool that I am using
I have written like this:
SELECT c.cnsmr_id,
c.cnsmr_nm_lst_txt,
c.cnsmr_nm_first_txt,
ISNULL(cpH.home_phn_nmbr,'') home_phn_nmbr,
ISNULL(cpW.work_phn_nmbr,'') work_phn_nmbr,
ISNULL(cpC.cell_phn_nmbr,'') cell_phn_nmbr
FROM @cnsmr c
LEFT JOIN (
SELECT cp.cnsmr_id,cp.phn_nmbr home_phn_nmbr,ROW_NUMBER() OVER(PARTITION BY cp.cnsmr_id,cp.phn_typ ORDER BY upsrt_dttm DESC) ranking1
FROM @cnsmr_phn cp
WHERE cp.phn_typ = 'HOME'
) cpH ON c.cnsmr_id = cpH.cnsmr_id
AND cpH.ranking1 = 1
LEFT JOIN (
SELECT cp.cnsmr_id,cp.phn_nmbr work_phn_nmbr,ROW_NUMBER() OVER(PARTITION BY cp.cnsmr_id,cp.phn_typ ORDER BY upsrt_dttm DESC) ranking1
FROM @cnsmr_phn cp
WHERE cp.phn_typ = 'WORK'
) cpW ON c.cnsmr_id = cpW.cnsmr_id
AND cpW.ranking1 = 1
LEFT JOIN (
SELECT cp.cnsmr_id,cp.phn_nmbr cell_phn_nmbr,ROW_NUMBER() OVER(PARTITION BY cp.cnsmr_id,cp.phn_typ ORDER BY upsrt_dttm DESC) ranking1
FROM @cnsmr_phn cp
WHERE cp.phn_typ = 'CELL'
) cpC ON c.cnsmr_id = cpC.cnsmr_id
AND cpC.ranking1 = 1