| 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 |
|
Grabbing the top date with nulls