| Author |
 Topic  |
|
|
IK1972
Starting Member
41 Posts |
 Posted - 09/25/2012 : 14:55:26
|
I have one table with 4 column. In this table we are storing all request data.
Create table #temp(UID varchar(50), ClientIdentifier varchar(20), CreatedDate datetime, Comments varchar(500))
123, abc, 09/05/2012 5:00:23 AM, NULL
123, xyz, 09/05/2012 5:20:23 AM, NULL
345, NULL, 09/06/2012 6:25:20 PM, NULL
345, fbg, 09/06/2012 7:20:25 PM, NULL
345, xyz, 09/07/2012 7:20:25 PM, NULL
567, gfdg, 09/02/2012 5:66:66 AM, NULL
567, NULL, 09/02/2012 6:00:00 AM, NULL
789, NULL, 09/004/2012 7:00:12 AM, NULL
789, NULL, 09/04/2012 7:00:15 AM, NULL
Now I need to get the rows for every UID where ClientIderifier is null in first request (Base on CreatedDate) so as above data
I will get 2 rows.
345, NULL, 09/06/2012 6:25:20 PM, NULL
789, NULL, 09/004/2012 7:00:12 AM, NULL
Thanks
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 09/25/2012 : 15:35:10
|
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
Create table #temp(UID varchar(50), ClientIdentifier varchar(20), CreatedDate datetime, Comments varchar(500))
insert #temp
select 123, 'abc', '09/05/2012 5:00:23 AM', NULL union all
select 123, 'xyz', '09/05/2012 5:20:23 AM', NULL union all
select 345, NULL, '09/06/2012 6:25:20 PM', NULL union all
select 345, 'fbg', '09/06/2012 7:20:25 PM', NULL union all
select 345, 'xyz', '09/07/2012 7:20:25 PM', NULL union all
select 567, 'gfdg', '09/02/2012 5:56:56 AM', NULL union all
select 567, NULL, '09/02/2012 6:00:00 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:12 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:15 AM', NULL;
with cte1
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY UID ORDER BY CreatedDate) as rn
FROM #temp
AS t
)
SELECT *
FROM cte1
WHERE rn = 1
AND ClientIdentifier IS NULL
|
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 09/25/2012 : 15:35:15
|
Soemthing like this should work:SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY UID ORDER BY CreatedDate ASC) AS RowNum
FROM
#Temp
) AS A
WHERE
RowNum = 1
AND ClientIdentifier IS NULLEDIT: Ahh too slow.. |
Edited by - Lamprey on 09/25/2012 15:36:02 |
|
|
|
IK1972
Starting Member
41 Posts |
Posted - 09/25/2012 : 15:50:02
|
Is it possible if I can get all requests CreatedDate and ClientIdentifier in rows
Like result should like this.
UID Request1, Request2, Request3
345 09/06/2012 6:25:20 PM - NULL, 09/06/2012 7:20:25 PM - fbg, 09/07/2012 7:20:25 PM - xyz
789 09/004/2012 7:00:12 AM - NULL, 09/04/2012 7:00:15 AM - NULL NULL
Thanks |
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 09/25/2012 : 15:57:54
|
What about getting all rows for the UIDs that qualify?
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
Create table #temp(UID varchar(50), ClientIdentifier varchar(20), CreatedDate datetime, Comments varchar(500))
insert #temp
select 123, 'abc', '09/05/2012 5:00:23 AM', NULL union all
select 123, 'xyz', '09/05/2012 5:20:23 AM', NULL union all
select 345, NULL, '09/06/2012 6:25:20 PM', NULL union all
select 345, 'fbg', '09/06/2012 7:20:25 PM', NULL union all
select 345, 'xyz', '09/07/2012 7:20:25 PM', NULL union all
select 567, 'gfdg', '09/02/2012 5:56:56 AM', NULL union all
select 567, NULL, '09/02/2012 6:00:00 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:12 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:15 AM', NULL;
with cte1
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY UID ORDER BY CreatedDate) as rn
FROM #temp
AS t
)
SELECT t.*
FROM #temp
AS t
JOIN cte1
AS c
ON t.UID
= c.UID
WHERE c.rn = 1
AND c.ClientIdentifier IS NULL
ORDER BY c.UID,
c.CreatedDate
/*
UID ClientIdentifier CreatedDate Comments
345 NULL 2012-09-06 18:25:20.000 NULL
345 fbg 2012-09-06 19:20:25.000 NULL
345 xyz 2012-09-07 19:20:25.000 NULL
789 NULL 2012-09-04 07:00:12.000 NULL
789 NULL 2012-09-04 07:00:15.000 NULL
*/
|
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 09/25/2012 : 16:02:15
|
The row example is more flexible, so if you can work with that in your application you will be better off, but here is a pivoted example that gives you exactly what you want:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
Create table #temp(UID varchar(50), ClientIdentifier varchar(20), CreatedDate datetime, Comments varchar(500))
insert #temp
select 123, 'abc', '09/05/2012 5:00:23 AM', NULL union all
select 123, 'xyz', '09/05/2012 5:20:23 AM', NULL union all
select 345, NULL, '09/06/2012 6:25:20 PM', NULL union all
select 345, 'fbg', '09/06/2012 7:20:25 PM', NULL union all
select 345, 'xyz', '09/07/2012 7:20:25 PM', NULL union all
select 567, 'gfdg', '09/02/2012 5:56:56 AM', NULL union all
select 567, NULL, '09/02/2012 6:00:00 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:12 AM', NULL union all
select 789, NULL, '09/04/2012 7:00:15 AM', NULL;
with cte1
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY UID ORDER BY CreatedDate) as rn
FROM #temp
AS t
)
SELECT t.UID,
MAX(CASE t.rn WHEN 1 THEN t.ClientIdentifier END) AS ClientIdentifier1,
MAX(CASE t.rn WHEN 1 THEN t.CreatedDate END) AS CreatedDate1,
MAX(CASE t.rn WHEN 2 THEN t.ClientIdentifier END) AS ClientIdentifier2,
MAX(CASE t.rn WHEN 2 THEN t.CreatedDate END) AS CreatedDate2,
MAX(CASE t.rn WHEN 3 THEN t.ClientIdentifier END) AS ClientIdentifier3,
MAX(CASE t.rn WHEN 3 THEN t.CreatedDate END) AS CreatedDate3
FROM cte1
AS t
JOIN cte1
AS c
ON t.UID
= c.UID
WHERE c.rn = 1
AND c.ClientIdentifier IS NULL
GROUP BY t.UID
ORDER BY t.UID
|
Edited by - lazerath on 09/25/2012 16:04:45 |
|
|
|
IK1972
Starting Member
41 Posts |
Posted - 09/25/2012 : 18:26:48
|
Yes this looks good. Thanks! |
|
|
| |
Topic |
|
|
|
SQL Query Help
