Query On Date ! - 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 2000 Forums

SQL Server Development (2000)

Query On Date !

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-04-16 : 00:20:41

Hello Everybody :
Contents of Table1 :

FlightId date
---------- ---------------------------
CA100 2003-01-01 00:00:00.000
CA100 2003-03-01 00:00:00.000
CA100 2003-08-01 00:00:00.000
AA200 2002-01-01 00:00:00.000
AA200 2003-02-02 00:00:00.000

I want to select only those rows from each FlightID group that has the lowest date and falls between the range from lowest date till date after 3 months.

For eg : from the above records it should return :
FlightID date
CA100 2003-01-01
CA100 2003-03-01
AA200 2002-01-01
As U can c it should skip CA100 2003-08-01 bcoz it is does not satisfy the condition of falling in range of lowest date [i.e 2003-01-01] and date after adding 3months to this date i.e. 2003-04-01.
It should also skip AA200 2003-02-02.

I am unable to get the desired result. I hope somebody will definetly help me.

Thanks,
Regards,
- Marjo.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-16 : 00:44:57


CREATE table #Flights (FlightId CHAR(5) NOT NULL, FlightDate DATETIME NOT NULL PRIMARY KEY(FlightId, FlightDate))
GO
INSERT #Flights (FlightID, FlightDate)
SELECT 'CA100', '2003-01-01 00:00:00.000'
UNION ALL
SELECT 'CA100', '2003-03-01 00:00:00.000'
UNION ALL
SELECT 'CA100', '2003-08-01 00:00:00.000'
UNION ALL
SELECT 'AA200', '2002-01-01 00:00:00.000'
UNION ALL
SELECT 'AA200', '2003-02-02 00:00:00.000'
GO
SELECT * 
FROM #Flights F
WHERE EXISTS
(
SELECT 1
FROM #Flights
WHERE FlightID = F.FlightID
GROUP BY FlightID
HAVING DATEDIFF(m,Min(FlightDate), F.FlightDate) <= 3
)