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

Transact-SQL (2005)

JOIN

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-26 : 06:03:59

i have a table like that

CODE	STATUSH	STATUSL	DATE
123	11	11	1.1.09
123	12	11	2.1.09
123	23	22	3.1.09
123	33	22	4.1.09
123	33	33	1.1.10

i want to get

CODE	STATUSH	STATUSL	MAXDATE	MINDATE
123	23	22	3.01.09	2.01.09

i mean
take the min date where STATUSH>11 and min date where STATUSL>=22

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:22:43

your date is in DD.MM.YY or MM.DD.YY ?
is it min or max date ? your expected result shows MAXDATE.

The first date in your result correspond to the STATUSH or STATUSL ?

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 06:25:10

may be this one?
declare @t table(CODE int, STATUSH int,STATUSL int, DATE datetime)
insert into @t select 123, 11, 11, '1/1/09' union all select
123, 12, 11, '2/1/09' union all select
123, 23, 22, '3/1/09' union all select
123, 33, 22, '4/1/09' union all select
123, 33, 33, '1/1/10'

select t.code,t.statush,t.statusl,maxdate,mindate
from @t t
left join (
select code,min(date)mindate from @t where
statush>11
group by code)s on s.code = t.code
left join(
select code,min(date)maxdate from @t where
statusl>=22
group by code) k on k.code = t.code and k.maxdate = t.date
where maxdate is not null

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-26 : 07:02:52

select t.code,t.statush,t.statusl,maxdate,mindate
from @t t
left join (
select code,min(date)mindate from @t where
statush>11
group by code)s on s.code = t.code

have duplicate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:13:50

seems like this


SELECT t.CODE,t2.STATUSH,t2.STATUSL,t2.DATE AS MAXDATE,t1.DATE AS MINDATE
FROM Table t
OUTER APPLY (SELECT TOP 1 DATE
             FROM Table
             WHERE CODE=t.CODE
             AND STATUSH>11
             ORDER BY DATE) t1
OUTER APPLY (SELECT TOP 1 STATUSH,STATUSL,DATE
             FROM Table
             WHERE CODE=t.CODE
             AND STATUSL>=22
             ORDER BY DATE) t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources