SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grabbing the top date with nulls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 01/07/2013 :  15:29:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/07/2013 :  15:59:43  Show Profile  Reply with Quote
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
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 01/07/2013 :  16:13:51  Show Profile  Reply with Quote
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
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 01/07/2013 :  16:16:58  Show Profile  Reply with Quote
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
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 01/07/2013 :  16:32:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/07/2013 :  23:39:33  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000