Sql Query

Did you change the date format?
In the previous question you used:
DECLARE @Last_Friday AS DATE = '2017-02-07'
DECLARE @This_Friday as date = '2017-02-17'

Now you're using different format?
Arrivaldate  : 05/03/2017
 DepartureDate: 26/03/2017
 ExtendedTo: 10/04/2017

Nope,

Arrivaldate  : =  '2017-03-05'
 DepartureDate:  = '2017-03-26'

Vitor,
The query is

DECLARE @Last_Friday AS DATE = '2017-04-07'
DECLARE @This_Friday as date = '2017-04-14'

SELECT GuestName, ArrivalDate, 
       CASE
              WHEN Extendedto IS NULL THEN DepartureDate
              ELSE Extendedto
       END AS DepartureDate
FROM CORRESPONDENCE
WHERE  ( DeleteRecord is null or DeleteRecord = 0) and
(ArrivalDate >= '2016-01-01'
       AND (
              (Extendedto IS NULL AND DepartureDate >= @Last_Friday)
              OR Extendedto >= @Last_Friday
              )
       AND (ArrivalDate >= @Last_Friday AND ArrivalDate  <=  @This_Friday)
    OR (ArrivalDate <= @Last_Friday 
              AND (
                     (Extendedto IS NULL AND DepartureDate >= @This_Friday)
                     OR Extendedto >= @This_Friday
                     )
              )
       )

Select allOpen in new window

What's the error?
If no error returned then please post some sample data for those dates so we can test it.

There is no error.The result is not correct.

please post some sample data for those dates so we can test it

Yes Vitor.
Working on that

Name      Arrival Date      Departure       Extended To
A      05/03/2017      17/04/2017      
B      04/04/2017      11/04/2017      24/04/2017
C      25/03/2017      10/04/2017      
D      21/03/2017      26/03/2017      14/04/2017
E      27/03/2017      09/04/2017      
F      22/03/2017      10/04/2017      
G      04/04/2017      08/04/2017      
H      31/03/2017      07/04/2017      
I      29/03/2017      12/04/2017      
J      24/03/2017      16/04/2017      
K      27/03/2017      16/04/2017      
L      25/03/2017      07/04/2017      
M      26/03/2017      16/04/2017      
N      02/04/2017      09/04/2017      
O      06/04/2017      16/04/2017      
P      13/03/2017      24/03/2017      02/05/2017
Q      06/04/2017      11/04/2017      
R      06/04/2017      10/04/2017

I can still see a different format for the dates.
What are the data types for the columns Arrival Date, Departure and Extended To?

Datetime

If they're datetime then it should work. Try to make a test by removing the '-' separator:

DECLARE @Last_Friday AS DATE = '20170407'
DECLARE @This_Friday as date = '20170414'

SELECT GuestName, ArrivalDate, 
       CASE
              WHEN Extendedto IS NULL THEN DepartureDate
              ELSE Extendedto
       END AS DepartureDate
FROM CORRESPONDENCE
WHERE  ( DeleteRecord is null or DeleteRecord = 0) and
(ArrivalDate >= '20160101'
       AND (
              (Extendedto IS NULL AND DepartureDate >= @Last_Friday)
              OR Extendedto >= @Last_Friday
              )
       AND (ArrivalDate >= @Last_Friday AND ArrivalDate  <=  @This_Friday)
    OR (ArrivalDate <= @Last_Friday 
              AND (
                     (Extendedto IS NULL AND DepartureDate >= @This_Friday)
                     OR Extendedto >= @This_Friday
                     )
              )
       )

Select allOpen in new window

Vitor,
Didnt give correct results though

Missing Name:


C
E
F
G
H
I
L
N
O
Q
R

Vitor,
The datatype is date

After build a table with your sample data, I ran the query and this is what has returned for me:

GuestName ArrivalDate DepartureDate
--------- ----------- -------------
A         2017-03-05  2017-04-17
B         2017-04-04  2017-04-24
D         2017-03-21  2017-04-14
J         2017-03-24  2017-04-16
K         2017-03-27  2017-04-16
M         2017-03-26  2017-04-16
O         2017-04-06  2017-04-16
P         2017-03-13  2017-05-02

(8 row(s) affected)

Select allOpen in new window

Is what is expected? If no, please let me know which records are expected.

All arrivals must be before THIS Friday

                       TF     
A-------------         TF
    A------------------TF
       A------------   TF
           A-----------TF------
A----------------------TF------          
          
    LF                       
 ---LF---------D
    LF-------------------D
    LF  ------------D
    LF    -------------------D
 ---LF-------------------------D    

Select allOpen in new window

All Extended Departures or Departures must be after LAST Friday

Try this

DECLARE @Last_Friday AS DATE = '2017-04-07'
DECLARE @This_Friday as date = '2017-04-14'

SELECT
      GuestName
    , format(ArrivalDate,'yyyy-MM-dd') ArrivalDate
    , format(CASE
                  WHEN Extendedto IS NULL THEN DepartureDate
                  ELSE Extendedto
           END,'yyyy-MM-dd') AS DepartureDate
FROM @CORRESPONDENCE

WHERE  ( DeleteRecord is null or DeleteRecord = 0) 
AND ArrivalDate >= '2016-01-01'
AND ArrivalDate <= @This_Friday
AND COALESCE(ExtendedTo,DepartureDate) >= @Last_Friday

Select allOpen in new window

All records should be displayed

Need to know who is in house between
DECLARE @Last_Friday AS DATE = '2017-04-07'
DECLARE @This_Friday as date = '2017-04-14'

All are returned by my revised where clause. Please consider the simplification carefully, it is a bit tricky at first, but if you compare the arrival date to the latest Friday, and the departure to the earlier Friday it is much easier. This will find anyone who was in the house between those 2 dates.

PortletPaul,
Thanks , if you refer to my question:
The requirement is that if extended date is present then consider extended date or else consider departure date .

If you look at the data , all guest are in house between
DECLARE @Last_Friday AS DATE = '2017-04-07'
DECLARE @This_Friday as date = '2017-04-14'

If you only want to know who's in the house during a giving period then you don't need to test the ArrivalDate. Just check if the DepartureDate is between the giving period:

SELECT GuestName, ArrivalDate, 
       CASE
              WHEN Extendedto IS NULL THEN DepartureDate
              ELSE Extendedto
       END AS DepartureDate
FROM CORRESPONDENCE
WHERE (DeleteRecord is null or DeleteRecord = 0) AND 
	(
		DepartureDate BETWEEN @Last_Friday AND @This_Friday
		OR Extendedto BETWEEN @Last_Friday AND @This_Friday
	)

Select allOpen in new window

The requirement is that if extended date is present then consider extended date or else consider departure date

Yes, understood, and you will see that this is true here:

AND COALESCE(ExtendedTo,DepartureDate) >= @Last_Friday

Perhaps you would try it?


@Vitor
watch for arrival before the earlier friday, and departure after the later friday

Vitor,

I tried your query but,
J,K,M,O,P were not displayed

Vitor,

 I tried your query but,
 J,K,M,O,P were not displayed

@Vitor
 watch for arrival before the earlier friday, and departure after the later friday

Looks like I were too fast. Paul just called mine attention for that.

PortletPaul,
Tried your query it is giving syntax error:
Must declare the table variable "@CORRESPONDENCE".

Is there some reason for not trying my suggestion?

I tried to explain with a text diagram.

a room booking could occur between the 2 dates
or start before the earlier date and end between the 2 dates
or start after the earlier date and end after the later date
or a booking could start before the earlier date and end after the later date (a long stay that spans the 2 dates)

ALL of these conditions are handled by the logic I portrayed in my small diagrams
The common conditions are
       all starts must be before the this friday (TF)
       all ends must be after the  last friday (LF)

remove the @

You are fantastic! Thanks a ton mate.
Vitor thanks for the efforts .