Grabbing the top date with nulls

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 2008 Forums

Transact-SQL (2008)

Grabbing the top date with nulls

Author

Topic

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-07 : 15:29:47

Hello,

I am having some issues running a query and getting the results I want. In the data I am getting multiple rows with the same 'cfsid' with one of them being a date and the others being null. I want to be able to run a query that is able to identify if there are two records with the same CFSID and two different dates, one of them being a null and the other being a date then selecting the one with the date. However, I also want to grab the data that has a unique cfsid with a null for the ArriveTime.

I know this is a fairly complicated and confusing way to say what I am trying to do so if you have any questions please let me know.

select
cData.CFSID
,cData.CompleteTime
,cData.OccuredStart
,cLog.ArriveTime

from NewClient.dbo.CFSData cData
left join NewClient.dbo.CFSOfficerLog cLog ON cLog.CFSID = cData.CFSID
where cLog.ArriveTime=
(
select top(1) cLog1.ArriveTime
from NewClient.dbo.CFSOfficerLog cLog1
where cLog1.CFSID = cData.CFSID
order by clog1.ArriveTime desc
)

or

clog.ArriveTime is null
order by cdata.CFSID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-07 : 15:59:43

Can you post sample data and expected output?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-07 : 16:13:51

Sorry, it is a bit jumbled... The first record, 1042 has a null value but no other row with that cfsid so that is okay. However, if you look at row 1073 (at the very end) you'll notice one row returns null and one row returns a date. I only want the one with the date if there are two or more rows that return but I don't want to completely throw out nulls all together because of rows like 1042.

CFSID CompleteTime OccuredStart ArriveTime
1042 2001-07-02 14:27:11.000 2001-07-02 14:23:00.000 NULL
1043 2001-07-02 14:27:01.000 2001-07-02 14:26:46.000 2001-07-02 14:26:46.000
1044 2001-07-02 14:47:05.000 2001-07-02 14:46:38.000 2001-07-02 14:46:38.000
1045 2001-07-02 15:09:58.000 2001-07-02 15:08:59.000 2001-07-02 15:08:59.000
1046 2001-07-02 15:16:18.000 2001-07-02 15:15:33.000 2001-07-02 15:15:33.000
1047 2001-07-02 15:27:49.000 2001-07-02 15:27:37.000 2001-07-02 15:27:37.000
1048 2001-07-02 15:47:22.000 2001-07-02 15:39:42.000 2001-07-02 15:39:42.000
1049 2001-07-02 16:00:09.000 2001-07-02 15:59:19.000 2001-07-02 15:59:19.000
1050 2001-07-02 16:27:04.000 2001-07-02 16:26:56.000 2001-07-02 16:26:56.000
1051 2001-07-02 16:42:30.000 2001-07-02 16:42:24.000 2001-07-02 16:42:24.000
1052 2001-07-02 16:44:33.000 2001-07-02 16:43:59.000 2001-07-02 16:43:59.000
1053 2001-07-02 16:48:45.000 2001-07-02 16:48:19.000 2001-07-02 16:48:19.000
1054 2001-07-02 17:48:04.000 2001-07-02 17:45:50.000 2001-07-02 17:45:50.000
1055 2001-07-02 17:50:35.000 2001-07-02 17:49:35.000 2001-07-02 17:49:35.000
1056 2001-07-02 17:56:30.000 2001-07-02 17:54:15.000 2001-07-02 17:54:15.000
1057 2001-07-02 17:58:01.000 2001-07-02 17:57:11.000 2001-07-02 17:57:11.000
1058 2001-07-02 18:09:38.000 2001-07-02 18:08:30.000 2001-07-02 18:08:30.000
1059 2001-07-02 18:36:02.000 2001-07-02 18:25:32.000 2001-07-02 18:25:32.000
1060 2001-07-02 18:31:04.000 2001-07-02 18:27:50.000 2001-07-02 18:27:50.000
1061 2001-07-02 19:43:06.000 2001-07-02 19:37:48.000 2001-07-02 19:37:48.000
1062 2001-07-02 19:57:22.000 2001-07-02 19:54:29.000 2001-07-02 19:54:29.000
1063 2001-07-02 20:07:50.000 2001-07-02 19:57:00.000 2001-07-02 19:57:00.000
1064 2001-07-02 20:28:39.000 2001-07-02 20:16:53.000 2001-07-02 20:16:53.000
1065 2001-07-02 20:41:34.000 2001-07-02 20:37:40.000 2001-07-02 20:37:40.000
1066 2001-07-02 20:47:13.000 2001-07-02 20:38:47.000 2001-07-02 20:38:47.000
1067 2001-07-02 21:10:28.000 2001-07-02 21:00:08.000 2001-07-02 21:00:08.000
1068 2001-07-02 21:10:16.000 2001-07-02 21:01:57.000 2001-07-02 21:01:57.000
1069 2001-07-02 21:18:34.000 2001-07-02 21:07:57.000 2001-07-02 21:07:57.000
1070 2001-07-02 21:54:44.000 2001-07-02 21:42:32.000 2001-07-02 21:42:32.000
1071 2001-07-02 21:50:27.000 2001-07-02 21:45:37.000 2001-07-02 21:45:37.000
1072 2001-07-02 21:53:56.000 2001-07-02 21:50:07.000 2001-07-02 21:50:07.000
1073 2001-07-02 22:09:46.000 2001-07-02 22:07:56.000 NULL
1073 2001-07-02 22:09:46.000 2001-07-02 22:07:56.000 2001-07-02 22:07:56.000

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-07 : 16:16:58

Expected output is the same without the extra row with 1073.

CFSID CompleteTime OccuredStart ArriveTime
1042 2001-07-02 14:27:11.000 2001-07-02 14:23:00.000 NULL
1043 2001-07-02 14:27:01.000 2001-07-02 14:26:46.000 2001-07-02 14:26:46.000
1044 2001-07-02 14:47:05.000 2001-07-02 14:46:38.000 2001-07-02 14:46:38.000
1045 2001-07-02 15:09:58.000 2001-07-02 15:08:59.000 2001-07-02 15:08:59.000
1046 2001-07-02 15:16:18.000 2001-07-02 15:15:33.000 2001-07-02 15:15:33.000
1047 2001-07-02 15:27:49.000 2001-07-02 15:27:37.000 2001-07-02 15:27:37.000
1048 2001-07-02 15:47:22.000 2001-07-02 15:39:42.000 2001-07-02 15:39:42.000
1049 2001-07-02 16:00:09.000 2001-07-02 15:59:19.000 2001-07-02 15:59:19.000
1050 2001-07-02 16:27:04.000 2001-07-02 16:26:56.000 2001-07-02 16:26:56.000
1051 2001-07-02 16:42:30.000 2001-07-02 16:42:24.000 2001-07-02 16:42:24.000
1052 2001-07-02 16:44:33.000 2001-07-02 16:43:59.000 2001-07-02 16:43:59.000
1053 2001-07-02 16:48:45.000 2001-07-02 16:48:19.000 2001-07-02 16:48:19.000
1054 2001-07-02 17:48:04.000 2001-07-02 17:45:50.000 2001-07-02 17:45:50.000
1055 2001-07-02 17:50:35.000 2001-07-02 17:49:35.000 2001-07-02 17:49:35.000
1056 2001-07-02 17:56:30.000 2001-07-02 17:54:15.000 2001-07-02 17:54:15.000
1057 2001-07-02 17:58:01.000 2001-07-02 17:57:11.000 2001-07-02 17:57:11.000
1058 2001-07-02 18:09:38.000 2001-07-02 18:08:30.000 2001-07-02 18:08:30.000
1059 2001-07-02 18:36:02.000 2001-07-02 18:25:32.000 2001-07-02 18:25:32.000
1060 2001-07-02 18:31:04.000 2001-07-02 18:27:50.000 2001-07-02 18:27:50.000
1061 2001-07-02 19:43:06.000 2001-07-02 19:37:48.000 2001-07-02 19:37:48.000
1062 2001-07-02 19:57:22.000 2001-07-02 19:54:29.000 2001-07-02 19:54:29.000
1063 2001-07-02 20:07:50.000 2001-07-02 19:57:00.000 2001-07-02 19:57:00.000
1064 2001-07-02 20:28:39.000 2001-07-02 20:16:53.000 2001-07-02 20:16:53.000
1065 2001-07-02 20:41:34.000 2001-07-02 20:37:40.000 2001-07-02 20:37:40.000
1066 2001-07-02 20:47:13.000 2001-07-02 20:38:47.000 2001-07-02 20:38:47.000
1067 2001-07-02 21:10:28.000 2001-07-02 21:00:08.000 2001-07-02 21:00:08.000
1068 2001-07-02 21:10:16.000 2001-07-02 21:01:57.000 2001-07-02 21:01:57.000
1069 2001-07-02 21:18:34.000 2001-07-02 21:07:57.000 2001-07-02 21:07:57.000
1070 2001-07-02 21:54:44.000 2001-07-02 21:42:32.000 2001-07-02 21:42:32.000
1071 2001-07-02 21:50:27.000 2001-07-02 21:45:37.000 2001-07-02 21:45:37.000
1072 2001-07-02 21:53:56.000 2001-07-02 21:50:07.000 2001-07-02 21:50:07.000
1073 2001-07-02 22:09:46.000 2001-07-02 22:07:56.000 2001-07-02 22:07:56.000

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-07 : 16:32:04

I finally figured it out... if anyone else needs this in the future here is the final code...

select
cData.CFSID
,cData.CompleteTime
,cData.OccuredStart
,cLog.ArriveTime

from NewClient.dbo.CFSData cData
left join (select cfsid, max(clog.arriveTime) as ArriveTime
from NewClient.dbo.CFSOfficerLog cLog
group by CFSID) as cLog

ON cLog.CFSID = cData.CFSID

order by CFSID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 23:39:33

if you're using sql 2005 or above you can use this too


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CFSID ORDER BY COALESCE(ArriveTime,0) DESC) AS Seq,*
FROM NewClient.dbo.CFSData
)cData
WHERE Seq=1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources