CODEWITHSUNDEEP
sqlamazon-redshift
user14263992
user14263992

Reputation: 115

Passengers who travelled recently

enter image description here

select distinct passenger from table where travel date falls in each of the last 2 months group by passenger

When the query is run on October - September & August data should be pulled. When run on November - September & October data should be pulled.

In the above example, "passenger 1" should be pulled because he travelled both in the month of August and September. "passenger 2&3" should not be pulled.

Upvotes: 1

Views: 738

Answers (3)

Useme Alehosaini
Useme Alehosaini

Reputation: 3116

I have understood your requirements as Select the Passengers who traveled at least once in every month of the last two months (from Today) excluding the travels of the Current Month.

The required SQL code:

WITH cte
AS (
    SELECT passenger AS passenger
        ,MONTH(travel_date) AS travel_month
        ,COUNT(*) AS number_of_travels
    FROM [dbo].[tblTravels]
    WHERE DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())
    GROUP BY passenger
        ,MONTH(travel_date)
    )
SELECT passenger
FROM cte
GROUP BY passenger
HAVING COUNT(*) > 1

More Explanation about answer : To get the Last Two months From Today excluding the Current Month

DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())

If you would like to verify the Code, simply Run the following script:

CREATE TABLE [dbo].[tblTravels] (
    [passenger] [int] NULL
    ,[travel_date] [datetime] NULL
    ,[travel_id] [nchar](10) NULL
    ) ON [PRIMARY]

DELETE FROM [dbo].[tblTravels]

INSERT INTO [dbo].[tblTravels] VALUES (1, '2020-08-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (1, '2020-09-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (2, '2020-08-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (3, '2020-09-01', 'a')
GO

SELECT *
FROM [dbo].[tblTravels]
GO

WITH cte
AS (
    SELECT passenger AS passenger
        ,MONTH(travel_date) AS travel_month
        ,COUNT(*) AS number_of_travels
    FROM [dbo].[tblTravels]
    WHERE DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())
    GROUP BY passenger
        ,MONTH(travel_date)
    )
SELECT passenger
FROM cte
GROUP BY passenger
HAVING COUNT(*) > 1

DROP TABLE [dbo].[tblTravels]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would use date_trunc() for the date filtering. Then:

select t.passenger
from t
where travel_date >= date_trunc('month', current_date) - interval '2 month' and
      travel_date < date_trunc('month', current_date)
group by t.passenger
having count(distinct date_trunc('month', travel_date)) = 2;

This uses count(distinct) because it is generalizable. For instance, it is easy to adjust this query for people who have traveled in four out of the last 6 months.

Upvotes: 1

Iman Javadi
Iman Javadi

Reputation: 141

use below query

Select passenger FROM travel
WHERE  DATEDIFF(month, travel_date, CURRENT_TIMESTAMP)<=2
group BY passenger

for get current date can use getdate() or current_timestamp.
and if you want total detail you dont must from group by.

Select passenger,travel_date,travel_id FROM travel
WHERE  DATEDIFF(month, travel_data, CURRENT_TIMESTAMP)<=2
order by passenger

Upvotes: 0

Related Questions