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

IK1972
Starting Member

49 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
337 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
Starting Member

49 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
337 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
337 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
Starting Member

49 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000