SQLTeam.com | Weblogs | Forums

SQL Help


#1

Hello all SQL expert,

I am trying to get any equipment rentals that rent the equipment in year 2016 only.

SELECT Customer, Rent_Date, Return_Date
FROM Rental_Table

Here is the output from above query:

and here is the output I am expecting:

Customer of Linda, Bryan and Samantha need to be removed from the list because they have no activity in year 2016.
For example:
Customer Linda, last rental activity was in year 2009.
Customer Bryan, last rental activity was in year 2014.
Customer Amanda, last rental activity was in year 2015.

Only customer Samantha and Rick are what I need to be on the list because they have activity during the year of 2016.
Because:
Customer Samantha last activity is on Rent_Date 04/20/2015 and has not return the equipment yet. Still active.
Customer Rick, last rent activity was on 01/01/2016 and return the equipment at the same day on 01/01/2016.

How do I modify my query to achieve what I need to have on the above logic?

Thanks all


#2

Hope this works for you::

Select * from Rental_Table
where name in
(
Select name from Rental_Table
where
datePart(yy,Return_date )=2016
Or Return_date is NULL
)


#3

Thank you EarlySunrise