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
 General SQL Server Forums
 New to SQL Server Programming
 sql server query please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/20/2013 :  12:18:42  Show Profile  Reply with Quote
Dear All

I have the following scenario:
i have many passengers buying tickets some time two friends buys from differnet locations at mostly similar time and same date so i need to know who has booked ticket with same destination on same flight date and number with average differnece of 60 minutes what will be the best query or method to calculate this and obtain the records that has this scenario
table example:
name | flightdate| from | to | flight number | booking datetime|
Name1 : 10/10/2013: london| frankfurt| 100| 05/10/2013 10:00pm
Name2 : 10/10/2013: london| frankfurt| 100| 05/10/2013 11:00pm
Name1 : 10/10/2013: london| frankfurt| 100| 02/11/2013 6:00pm

thank you
hakimi

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/20/2013 :  14:01:28  Show Profile  Reply with Quote
Maybe try a self join:
SELECT 
	*
FROM 
	Table AS A
INNER JOIN
	Table AS B
	ON A.FlightDate = B.FlightDate
	AND A.To = B.To
	AND A.FlightNumber = B.FlightNumber
WHERE
	ABS(DATEDIFF(MINUTE, A.BookingDate, B.BookingDate)) <= 60
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  15:19:41  Show Profile  Reply with Quote
Here is a way to get what you need:


DECLARE @reservations TABLE( [Name] VARCHAR(20), [flightdate] DATE, [from] VARCHAR(20), 
	[to] VARCHAR(20), [flightnumber] int, [bookingdtm] datetime);

INSERT INTO  @reservations([Name], [flightdate], [from], [to], [flightnumber], [bookingdtm])
VALUES('Name1', '10/10/2013', 'london', 'frankfurt', 100, '05/10/2013 10:00pm'),
('Name2', '10/10/2013', 'london', 'frankfurt', 100, '05/10/2013 11:00pm'),
('Name3', '10/10/2013', 'london', 'frankfurt', 100, '02/11/2013 6:00pm'),
('Name4', '10/10/2013', 'london', 'frankfurt', 100, '05/11/2013 5:00am'),
('Name5', '10/10/2013', 'london', 'frankfurt', 100, '05/11/2013 6:00pm'),
('Name1', '10/10/2013', 'newyork', 'frankfurt', 100, '05/10/2013 10:10pm'),
('Name2', '10/10/2013', 'newyork', 'frankfurt', 100, '05/10/2013 11:10pm'),
('Name3', '10/10/2013', 'newyork', 'frankfurt', 100, '02/11/2013 6:30pm'),
('Name4', '10/10/2013', 'newyork', 'frankfurt', 100, '05/11/2013 5:20am'),
('Name5', '10/10/2013', 'newyork', 'frankfurt', 100, '05/11/2013 6:30pm');

; WITH CTE1 AS
(SELECT *,  ROW_NUMBER() OVER(PARTITION BY [flightdate], [from], [to] order by bookingdtm) as RN 
    FROM @reservations),
CTE2 as
(SELECT C1.RN, C1.[NAME], C1.[flightdate], C1.[from], C1.[to], 
    C1.[flightnumber], C1.[bookingdtm], 
	DATEDIFF(Minute, C1.[bookingdtm], C2.[bookingdtm]) as Timediff
    FROM CTE1 C1 INNER JOIN CTE1 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  
    C1.[to] = C2.[to] and C1.RN = C2.RN-1)	
SELECT C1.[NAME], C1.[flightdate], C1.[from], C1.[to], C1.[flightnumber], 
    C1.[bookingdtm] from CTE2 C1 JOIN CTE2 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  
    C1.[to] = C2.[to] and C1.RN = C2.RN+1 
	WHERE C1.Timediff <= 60 or (C2.Timediff <= 60) 
	ORDER BY C1.[flightdate], C1.[from], C1.[to] 



Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/20/2013 :  16:54:56  Show Profile  Reply with Quote
Dear friends

thank you for your replies, i tried mumu reply it looks good, but i need some more help, i have the table with more columns
like column1 | 2 3 4 4 etc.
and i have the data in one view so instead of inserting the records mentioned i want to bring it from the view1 how to do that?

thank you and B. Regards

Hakimi
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3722 Posts

Posted - 05/20/2013 :  17:21:10  Show Profile  Reply with Quote
Mumu was simply showing you an example since she did not have access to your tables. You don't need to declare the table or do the inserts. Instaed, in the query, simply replace the @reservations with the name of your view
; WITH CTE1 AS
(SELECT *,  ROW_NUMBER() OVER(PARTITION BY [flightdate], [from], [to] order by bookingdtm) as RN 
    FROM NAME_OF_YOUR_VIEW_HERE),
CTE2 as
(SELECT C1.RN, C1.[NAME], C1.[flightdate], C1.[from], C1.[to], 
    C1.[flightnumber], C1.[bookingdtm], 
	DATEDIFF(Minute, C1.[bookingdtm], C2.[bookingdtm]) as Timediff
    FROM CTE1 C1 INNER JOIN CTE1 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  
    C1.[to] = C2.[to] and C1.RN = C2.RN-1)	
SELECT C1.[NAME], C1.[flightdate], C1.[from], C1.[to], C1.[flightnumber], 
    C1.[bookingdtm] from CTE2 C1 JOIN CTE2 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  
    C1.[to] = C2.[to] and C1.RN = C2.RN+1 
	WHERE C1.Timediff <= 60 or (C2.Timediff <= 60) 
	ORDER BY C1.[flightdate], C1.[from], C1.[to] 
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/20/2013 :  18:15:43  Show Profile  Reply with Quote
Thank you all, i did the query but i found something wrong in the results some time i find time differnce more than 2 hours, i am not sure may be cause of hundred thousands records but can i order them now based on the flightnumber and bookingdatetime its not now .

thank you .

Hakimi
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/20/2013 :  19:28:09  Show Profile  Reply with Quote
Hi Mumu ,

I noticed that its not checking the time correctly its only checking the minutes so for example if the differnce i set 30 minutes :
10:10 first
4:40 second

it will show the records as the the differnce between the minutes are 30 minutes .

appriciates your assistant
thank you
hakimi
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  22:57:08  Show Profile  Reply with Quote
Good catch.
Here is the corrected query:

; WITH CTE1 AS
(SELECT *,  ROW_NUMBER() OVER(PARTITION BY [flightdate], [from], [to] order by bookingdtm) as RN from @reservations),
CTE2 as
(SELECT C1.RN, C1.[NAME], C1.[flightdate], C1.[from], C1.[to], C1.[flightnumber], C1.[bookingdtm], 
	DATEDIFF(second, C1.[bookingdtm], C2.[bookingdtm]) / 60  as Timediff
    FROM CTE1 C1 INNER JOIN CTE1 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  C1.[to] = C2.[to] and C1.RN = C2.RN-1)	
SELECT C1.[NAME], C1.[flightdate], C1.[from], C1.[to], C1.[flightnumber], C1.[bookingdtm] from CTE2 C1 JOIN CTE2 C2 
	ON C1.[flightdate] = C2.[flightdate] and C1.[from] = C2.[from] and  C1.[to] = C2.[to] and C1.RN = C2.RN+1 
	WHERE C1.Timediff <= 60 or (C2.Timediff <= 60) 
	ORDER BY C1.[flightdate], C1.[from], C1.[to] 



Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/20/2013 :  23:20:30  Show Profile  Reply with Quote
Just start to learn lead and lag.
hope this work =P

SELECT	*
FROM	(
		SELECT
			*
		,	LEAD(Bookingdtm, 1, 0) OVER (PARTITION BY [flightdate], [from], [to] order by bookingdtm) behind
		,	LAG(Bookingdtm, 1, 0) OVER (PARTITION BY [flightdate], [from], [to] order by bookingdtm) front
		FROM	@reservations)src
WHERE	ABS(DATEDIFF(hour, bookingdtm, behind)) = 1 OR ABS(DATEDIFF(hour, bookingdtm, front)) = 1
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/21/2013 :  04:01:58  Show Profile  Reply with Quote
Dear Mumu

this is the query i have but when executing it gives error message "Msg 8156, Level 16, State 1, Line 4
The column 'RN' was specified multiple times for 'CTE2'."


; WITH CTE1 AS
(SELECT [Flight Date],Board,[off],[Last Mod Date],[Flight Number],[Agency Name],[First Name], ROW_NUMBER() OVER(PARTITION BY [Flight Date], [Board], [Off] order by [Last Mod Date]) as RN from AllUniqueData),

CTE2 as

(SELECT C1.RN, C1.RN, C1.[First Name],c1.[Agency Name], C1.[Flight Date], C1.[Board], C1.[Off],
C1.[Flight Number], C1.[Last Mod Date],
DATEDIFF(second, C1.[Last Mod Date], C2.[Last Mod Date]) / 60 as Timediff
FROM CTE1 C1 INNER JOIN CTE1 C2
ON C1.[Flight Date] = C2.[Flight Date] and C1.[Board] = C2.[Board] and C1.[Off] = C2.[Off] and C1.RN = C2.RN-1)
SELECT C1.[First Name], C1.[Flight Date], C1.[Board], C1.[Off], C1.[Flight Number], C1.[Last Mod Date] from CTE2 C1 JOIN CTE2 C2
ON C1.[Flight Date] = C2.[Flight Date] and C1.[Board] = C2.[Board] and C1.[Off] = C2.[Off] and C1.RN = C2.RN+1
WHERE C1.Timediff <= 60 or (C2.Timediff <= 60)
ORDER BY C1.[Flight Date], C1.[Board], C1.[Off]

appreciates your kind help
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/21/2013 :  07:03:27  Show Profile  Reply with Quote
Mumu ,

thank you for your efforts i managed to fix it, ur solution is really pretty good thank you so much

Hakimi
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.09 seconds. Powered By: Snitz Forums 2000