Query Help - 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 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

learntsql

524 Posts

Posted - 2011-08-08 : 02:51:57

Hi All,

DECLARE @travel table(ID int ,RaiseID int,PassengerID varchar(50),TravelFromDate datetime ,TravelToDate datetime)

INSERT INTO @travel
SELECT 1,120,'P1','6-Aug-2011','7-Aug-2011'
union all
SELECT 2,123,'P1','7-Aug-2011','7-Aug-2011'
union all
SELECT 3,121,'P2','6-Aug-2011','7-Aug-2011'
union all
SELECT 4,122,'P2','7-Aug-2011','9-Aug-2011'
union all
SELECT 5,125,'P3','6-Aug-2011','7-Aug-2011'
union all
SELECT 6,126,'P3','8-Aug-2011','9-Aug-2011'

select * from @travel

Here my requirement is when for same passenger prev TravelTodate and next TravelFromdate are same
need to return that passengerid and date and if possible any text as status.
Please guide me.
TIA

Sachin.Nand

2937 Posts

Posted - 2011-08-08 : 03:44:57

So what should be the output of the above example ?

PBUH

learntsql

524 Posts

Posted - 2011-08-08 : 03:50:31

Here my output is

'P1','7-Aug-2011','Mapped'
'P2','7-Aug-2011','Mapped'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:49:51

[code]
SELECT t.PassengerID,t.TravelFromDate,'Mapped'
FROM Table t
CROSS APPLY (SELECT TOP 1 TravelToDate
FROM Table
WHERE PassengerID =t.PassengerID
AND TravelToDate <= t.TravelFromDate
ORDER BY TravelToDate DESC)t1
WHERE t1.TravelToDate = t.TravelFromDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2011-08-08 : 04:52:51

ThanQ Visakh,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:53:16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2011-08-11 : 05:27:40

Sorry Guys ,
I have one more query here plz..for following data

DECLARE @travel table(ID int ,RaiseID int,PassengerID varchar(50),TravelFromDate datetime ,TravelToDate datetime)

INSERT INTO @travel
SELECT 1,120,'P1','6-Aug-2011','7-Aug-2011'
union all
SELECT 2,123,'P1','7-Aug-2011','7-Aug-2011'
union all
SELECT 3,121,'P2','6-Aug-2011','7-Aug-2011'
union all
SELECT 4,122,'P2','7-Aug-2011','9-Aug-2011'
union all
SELECT 5,125,'P3','6-Aug-2011','7-Aug-2011'
union all
SELECT 6,126,'P3','8-Aug-2011','9-Aug-2011'
union all
SELECT 7,129,'P4','5-Aug-2011','5-Aug-2011'
union all
SELECT 8,130,'P4','5-Aug-2011','5-Aug-2011'
union all
SELECT 9,131,'P1','5-Aug-2011','5-Aug-2011'

SELECT t.PassengerID,t.TravelFromDate,t.TravelToDate,'Mapped'
FROM @travel t
CROSS APPLY (SELECT TOP 1 TravelToDate
FROM @travel
WHERE PassengerID =t.PassengerID
AND TravelToDate <= t.TravelFromDate
AND RaiseID < = t.RaiseID
ORDER BY TravelToDate DESC
)t1
WHERE t1.TravelToDate = t.TravelFromDate

That is single passenger can apply travel request two times for same day(i,e. morning and afternoon)
in this case it shoud appear in final select as for "P4" passenger here.
but when he applies only either for morning or afternoon then it should not appear as like for P1 on Aug-05.

Please guide me.
TIA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 07:52:12

[code]
SELECT t.PassengerID,t.TravelFromDate,t.TravelToDate,'Mapped'
FROM @travel t
CROSS APPLY (SELECT TOP 1 TravelToDate
FROM @travel
WHERE PassengerID =t.PassengerID
AND TravelToDate <= t.TravelFromDate
AND RaiseID < t.RaiseID
ORDER BY TravelToDate DESC
)t1
WHERE t1.TravelToDate = t.TravelFromDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources