problem in select from same table

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 2005 Forums

Transact-SQL (2005)

problem in select from same table

Author

Topic

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-12 : 08:36:23

HI ALL,
i have a table like
id docid action actiondate
1 1 C 23-03-2009
1 1 U 23-03-2009

i want to retreive actiondates based on different action in single select row and create join with mastertable based on docid to get createddate,Updated date etc

Thanks in advance,
de4ever

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 08:46:58

Do you have an expected output?

E 12°55'05.63"
N 56°04'39.26"

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-12 : 08:48:33

I would use subqueries instead of joins in this case, like
select docid,
upddate = ( select actiondate from actoins a where a.docid = d.docid and action = 'u')
from document d

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 08:52:37

Vadym... Think again. What if there are multiple updatedate dates?

E 12°55'05.63"
N 56°04'39.26"

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-12 : 08:57:07

quote:
Originally posted by Peso

Vadym... Think again. What if there are multiple updatedate dates?

E 12°55'05.63"
N 56°04'39.26"

if there are multiple update dates i need the maximum one

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-12 : 08:57:50

quote:
Originally posted by heavymind

I would use subqueries instead of joins in this case, like
select docid,
upddate = ( select actiondate from actoins a where a.docid = d.docid and action = 'u')
from document d

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

i need update date and created date as well

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-12 : 08:59:09

quote:
Originally posted by Peso

Do you have an expected output?

E 12°55'05.63"
N 56°04'39.26"

My expected ouput is like

docid,createddate,updatedate

--for each docid

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-12 : 09:03:06

select docid,
createddate = ( select actiondate from actoins a where a.docid = d.docid and action = 'c'),
upddate = ( select max(actiondate) from actoins a where a.docid = d.docid and action = 'u')
from document d

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 09:04:23

[code]DECLARE @Sample TABLE
(
id INT,
docid INT,
[action] CHAR(1),
actiondate DATETIME
)

INSERT @Sample
SELECT 1, 1, 'C', '20090323' UNION ALL
SELECT 1, 1, 'U', '20090323'

-- This you should write as a derived table and join to
SELECT docID,
MAX(CASE WHEN [action] = 'C' THEN actionDate ELSE NULL END) AS CreateDate,
MAX(CASE WHEN [action] = 'U' THEN actionDate ELSE NULL END) AS UpdateDate
FROM @Sample
GROUP BY docID[/code]

E 12°55'05.63"
N 56°04'39.26"

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-12 : 09:20:28

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		id INT,
		docid INT,
		[action] CHAR(1),
		actiondate DATETIME
	)

INSERT	@Sample
SELECT	1, 1, 'C', '20090323' UNION ALL
SELECT	1, 1, 'U', '20090323'

-- This you should write as a derived table and join to
SELECT		docID,
		MAX(CASE WHEN [action] = 'C' THEN actionDate ELSE NULL END) AS CreateDate,
		MAX(CASE WHEN [action] = 'U' THEN actionDate ELSE NULL END) AS UpdateDate
FROM		@Sample
GROUP BY	docID

E 12°55'05.63"
N 56°04'39.26"

Thanks a lot.it solved my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources