SQL Query Help - SQL Server Forums

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)

SQL Query Help

Author

Topic

IK1972

56 Posts

Posted - 2012-09-25 : 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

343 Posts

Posted - 2012-09-25 : 15:35:10

[code]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

[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-25 : 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 NULL

EDIT: Ahh too slow..

IK1972

56 Posts

Posted - 2012-09-25 : 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

343 Posts

Posted - 2012-09-25 : 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

343 Posts

Posted - 2012-09-25 : 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

IK1972

56 Posts

Posted - 2012-09-25 : 18:26:48

Yes this looks good. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources