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
 General SQL Server Forums
 New to SQL Server Programming
 sql server query please help

Author  Topic 

alhakimi
Starting Member

23 Posts

Posted - 2013-05-20 : 12:18:42
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-20 : 14:01:28
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

549 Posts

Posted - 2013-05-20 : 15:19:41
Here is a way to get what you need:
[CODE]

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]



[/CODE]
Go to Top of Page

alhakimi
Starting Member

23 Posts

Posted - 2013-05-20 : 16:54:56
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 17:21:10
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

23 Posts

Posted - 2013-05-20 : 18:15:43
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

23 Posts

Posted - 2013-05-20 : 19:28:09
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

549 Posts

Posted - 2013-05-20 : 22:57:08
Good catch.
Here is the corrected query:
[CODE]
; 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]



[/CODE]
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-20 : 23:20:30
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

23 Posts

Posted - 2013-05-21 : 04:01:58
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

23 Posts

Posted - 2013-05-21 : 07:03:27
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
   

- Advertisement -