Most recent (not null) over multiple tables/fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Most recent (not null) over multiple tables/fields

Author

Topic

LaurieCox

158 Posts

Posted - 2011-08-23 : 09:28:44

(DDL and sample data are at the end of the post).

I have three tables. The tables share some fields but not all. There can be zero or more records in each table for any given PATID. To make things more interesting each record has a datetime field (with zero time) and a varchar field to hold the time (formatted hh:mm PM or hh:mm AM).

What I need is a query that will take the most recent record (for a given PATID) in each table and then for each field return the most recent not null value.

I am currently working on a two step solution. If you think there is a better way let me know. With that said …

My work so far

I have written a Union query (see code below) that gets the most recent record for each table including null results for any fields that do not reside in the given table. I have also "fixed" the annoying two fields for date/time problem with this query.

The UNION query returns the following rows:


TabSrc PATID  data_entry_date_time    EmpStat PrimLang  County EngProf VetStat  AdvName
====== =====  ======================= ======= ========  ====== ======= =======  =======
RFS    28405  2011-08-15 14:39:00.000 5       spa       NULL   N       N        NULL
PAS    28405  2011-08-08 09:52:00.000 NULL    eng       39     Y       NULL     SMITH
SRC    4722   2011-08-08 13:06:00.000 4       eng       15     NULL    NULL     NULL
RFS    4722   2011-08-08 11:55:00.000 3       spa       NULL   NULL    Y        NULL
PAS    4722   2011-08-08 11:44:00.000 NULL    NULL      39     N       NULL     NULL
SRC    5025   2011-08-10 12:47:00.000 2       NULL      39     Y       Y        NULL
RFS    5025   2011-08-10 11:17:00.000 3       eng       NULL   N       NULL     NULL

Now I need to write a query that returns the most recent not null value for each field for a given PATID. This is the part where I need help.

For example for PATID 4722:


TabSrc PATID  data_entry_date_time    EmpStat PrimLang  County EngProf VetStat  AdvName
====== =====  ======================= ======= ========  ====== ======= =======  =======
SRC    4722   2011-08-08 13:06:00.000 4       eng       15     NULL    NULL     NULL
RFS    4722   2011-08-08 11:55:00.000 3       spa       NULL   NULL    Y        NULL
PAS    4722   2011-08-08 11:44:00.000 NULL    NULL      39     N       NULL     NULL

The most recent record is in the SRC table so I would want the query to return values from the three fields where the value is not null (EmpStat, PrimLang and County) from this record.

The next most recent record is the RFS table so I would want the query to return any not null values that I have not already gotten from the first record. In this case the value of the VetStat field. But not EmpStat and PrimLang as I already have values from the SRC table.

The oldest record is the PAS table and the query should pick up any not null values in any field I have not yet found a value for. In this case the only field I will get data from this record is the EngProf field.

So the query should return the following record for PATID 4722:


PATID EmpStat PrimLang County EngProf VetStat AdvName
===== ======= ======== ====== ======= ======= =======
4722  4       eng      15     N       Y       NULL

I have color coordinated the data source.
Note that no record had a value for AdvName and therefore the field is null in the final results.

Expected Results (given the sample data below)


PATID EmpStat PrimLang County EngProf VetStat AdvName
===== ======= ======== ====== ======= ======= =======
4722  4       eng      15     N       Y       NULL
5025  2       eng      39     Y       Y       NULL
28405 5       spa      NULL   N       N       SMITH

Thanks,

Laurie

DDL, Sample data and UNION query


CREATE TABLE #SRC (
       PATID varchar(40),
       data_entry_date datetime,
       data_entry_time varchar(40),
       EmpStat varchar(40),
       PrimLang varchar(40),
       County varchar(40),
       EngProf varchar(40),
       VetStat varchar(40))

INSERT INTO #SRC
SELECT '4722', '2011-08-08', '1:06 PM', '4', 'eng', '15', NULL, NULL UNION ALL
SELECT '4722', '2011-02-14', '1:06 PM', '6', 'YYY', '12', 'M', 'D' UNION ALL
SELECT '5025', '2011-08-10', '12:47 PM', '2', NULL, '39', 'Y', 'Y' UNION ALL
SELECT '5025', '2011-08-10', '11:50 AM', '6', 'YYY', '20', 'Z', 'Z' 

CREATE TABLE #RFS (
       PATID varchar(40),
       data_entry_date datetime,
       data_entry_time varchar(40),
       EmpStat varchar(40),
       PrimLang varchar(40),
       EngProf varchar(40),
       VetStat varchar(40))

INSERT INTO #RFS
SELECT '4722', '2011-08-08', '11:52 AM', '5', NULL, 'X', 'N' UNION ALL
SELECT '4722', '2011-08-08', '11:55 AM', '3', 'spa', NULL, 'Y' UNION ALL
SELECT '5025', '2011-08-10', '11:17 AM', '3', 'eng', 'N', NULL UNION ALL
SELECT '5025', '2011-08-02', '8:00 AM', '5', 'ZZZ', 'X', 'D' UNION ALL
SELECT '28405', '2011-08-15', '2:39 PM', '5', 'spa', 'N', 'N' 

CREATE TABLE #PAS (
       PATID varchar(40),
       data_entry_date datetime,
       data_entry_time varchar(40),
       PrimLang varchar(40),
       County varchar(40),
       EngProf varchar(40),
       AdvName varchar(40))

INSERT INTO #PAS
SELECT '4722', '2011-08-08', '11:44 AM', NULL, '39', 'N', NULL UNION ALL
SELECT '4722', '2011-08-08', '10:00 AM', 'zzz', '2', 'Y', 'jones' UNION ALL
SELECT '28405', '2011-08-08', '9:52 AM', 'eng', '39', 'Y', 'SMITH'

select *
into #MostRecent
from (
--Get most recent SRC record
select *
  from (select row_number() over 
         (partition by PATID order by data_entry_date + 
                                      data_entry_time DESC) rownum
         , PATID
         , Data_entry_date
         , data_entry_time
         , EmpStat
         , PrimLang
         , County
         , EngProf
         , VetStat
         , NULL as AdvName
         from #SRC
        ) scr
        where rownum = 1
union all
--Get most recent RFS record
select *
  from (select row_number() over 
         (partition by PATID order by data_entry_date + 
                                      data_entry_time DESC) rownum
         , PATID
         , Data_entry_date
         , data_entry_time
         , EmpStat
         , PrimLang
         , NULL as County
         , EngProf
         , VetStat
         , NULL as AdvName
         from #RFS
       ) rfs 
      where rownum = 1
union all
--Get most recent PAS record
select *
  from (select row_number() over 
         (partition by PATID order by data_entry_date +
                                      data_entry_time DESC) rownum
         , PATID
         , Data_entry_date
         , data_entry_time
         , null as EmpStat
         , PrimLang
         , County
         , EngProf
         , NULL as VetStat
         , AdvName
         from #pas
        ) pas
      where rownum = 1
)mr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:51:17

[code]
SELECT *
FROM (SELECT DISTINCT PATID FROM #MostRecent) m
OUTER APPLY (SELECT TOP 1 PrimLang
FROM #MostRecent
WHERE PATID = m.PATID
AND PrimLang IS NOT NULL
ORDER BY CAST(Data_entry_date+ ' ' + Data_entry_time AS datetime) DESC)p
OUTER APPLY (SELECT TOP 1 County
FROM #MostRecent
WHERE PATID = m.PATID
AND County IS NOT NULL
ORDER BY CAST(Data_entry_date+ ' ' + Data_entry_time AS datetime) DESC)c
OUTER APPLY (SELECT TOP 1 EngProf
FROM #MostRecent
WHERE PATID = m.PATID
AND EngProf IS NOT NULL
ORDER BY CAST(Data_entry_date+ ' ' + Data_entry_time AS datetime) DESC)e
OUTER APPLY (SELECT TOP 1 VetStat
FROM #MostRecent
WHERE PATID = m.PATID
AND VetStat IS NOT NULL
ORDER BY CAST(Data_entry_date+ ' ' + Data_entry_time AS datetime) DESC)v
OUTER APPLY (SELECT TOP 1 AdvName
FROM #MostRecent
WHERE PATID = m.PATID
AND AdvName IS NOT NULL
ORDER BY CAST(Data_entry_date+ ' ' + Data_entry_time AS datetime) DESC)a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LaurieCox

158 Posts

Posted - 2011-08-23 : 12:11:41

Thank you visakh16 that works perfectly

I did not know about the outer apply clause but it seems to be exactly what I wanted for this problem. I am off to read more.

Thanks Again,

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 12:19:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources