Author |
Topic  |
|
cstokes91
Yak Posting Veteran
USA
72 Posts |
Posted - 01/07/2013 : 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
Flowing Fount of Yak Knowledge
4614 Posts |
|
cstokes91
Yak Posting Veteran
USA
72 Posts |
Posted - 01/07/2013 : 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
USA
72 Posts |
Posted - 01/07/2013 : 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
USA
72 Posts |
Posted - 01/07/2013 : 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
India
52326 Posts |
Posted - 01/07/2013 : 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/
|
 |
|
|
Topic  |
|