unique query/stored proc - 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)

unique query/stored proc

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-18 : 10:46:23

I am facing with an very interesting situation. i have a table that looks like this

AcctNo Arrivaldt departdt

X 10/18/08 7:00 AM 10/18/08 11:00AM

Y 10/18/08 7:10 AM 10/18/08 11:20AM

Z 10/18/08 8:00AM 10/18/08 11:50 AM

one accountnumber will be passed on as parameter and for this i need to list all the other accountnumbers whose Arrivaldt is between this(selected parameter) Arrivaldt and departdt.
if Y is passed as parameter then it should pop up account A since its arrival date time is between arrival and depart datetimes of account number Y.

i tried a lot and posted at several places but no results hope i will be anwsered here.
Thanks in advance for everyone.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 10:54:01

declare @Input varchar(10)
set @Input='X'

select acctno from yourtable
where
Arrivaldt > (select min(Arrivaldt) from yourtable where AcctNo=@Input)
and
departdt < (select max(departdt) from yourtable where AcctNo=@Input)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 10:55:07

CREATE PROC GetAccntDetails
@AcctNo int
AS
;With Accnt_CTE (AcctNo,Arrivaldt,departdt) AS
(
SELECT AcctNo,Arrivaldt,departdt
FROM AccntTable
WHERE AcctNo=@AcctNo
UNION ALL
SELECT AcctNo,Arrivaldt,departdt
FROM AccntTable t
INNER JOIN Accnt_CTE c
ON t.Arrivaldt >= c.Arrivaldt
AND t.Arrivaldt<=c.departdt
)

SELECT * FROM Accnt_CTE ORDER BY AcctNo
GO

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-18 : 10:59:05

[code]
select * from <tab> t
where exists (
select 1 from <tab>
where acctno = @acctno
and t.arrivaldt between arrivaldt and departdt
and acctno <> t.acctno
)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources