Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 SQL Query Help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Yak Posting Veteran

56 Posts

Posted - 09/25/2012 :  14:55:26  Show Profile  Reply with Quote
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
343 Posts

Posted - 09/25/2012 :  15:35:10  Show Profile  Reply with Quote
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

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/25/2012 :  15:35:15  Show Profile  Reply with Quote
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..

Edited by - Lamprey on 09/25/2012 15:36:02
Go to Top of Page

IK1972
Yak Posting Veteran

56 Posts

Posted - 09/25/2012 :  15:50:02  Show Profile  Reply with Quote

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
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
343 Posts

Posted - 09/25/2012 :  15:57:54  Show Profile  Reply with Quote
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
*/



Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
343 Posts

Posted - 09/25/2012 :  16:02:15  Show Profile  Reply with Quote
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
Go to Top of Page

IK1972
Yak Posting Veteran

56 Posts

Posted - 09/25/2012 :  18:26:48  Show Profile  Reply with Quote

Yes this looks good. Thanks!
Go to Top of Page
  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000