asked on

Sql query amendenment with more fields

Hello,
I have a query which works perfectly, but recently have added few more fields in the table.
Need to amend the query , any help is appreciated .

SELECT
      GuestName
    , format(ArrivalDate,'yyyy-MM-dd') ArrivalDate
    , format(CASE
                  WHEN Extendedto IS NULL THEN DepartureDate
                  ELSE Extendedto
           END,'yyyy-MM-dd') AS DepartureDate
                                   , SentTo AS Property, Roomno, RoomTypeRouting AS 'RoomType' 
FROM CORR
WHERE ( DeleteRecord is null or DeleteRecord = 0) 
AND ArrivalDate >= '2016-01-01'
AND ArrivalDate <= @EndDate
AND COALESCE(ExtendedTo,DepartureDate) >= @StartDate

Open in new window

New Columns are :

[RoomTypeRouting2] [nvarchar](max) NULL,
      [RoomTypeRouting3] [nvarchar](max) NULL,
      [RoomTypeRouting4] [nvarchar](max) NULL,
      [ArrivalDate2] [date] NULL,
      [ArrivalDate3] [date] NULL,
      [ArrivalDate4] [date] NULL,
      [DepartureDate2] [date] NULL,
      [DepartureDate4] [date] NULL,
      [DepartureDate3] [date] NULL,
      [Extendedto2] [date] NULL,
      [Extendedto3] [date] NULL,
      [Extendedto4] [date] NULL,
      [Roomno2] [nvarchar](max) NULL,
      [Roomno3] [nvarchar](max) NULL,
      [Roomno4] [nvarchar](max) NULL

Regards

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Thanks Eric! Will try and brb

RIAS

ASKER

Thank you Sir! Worked like a charm.

RIAS

ASKER

Hello Eric,
Are we not adding these to the query for new columns :
, format(ArrivalDate,'yyyy-MM-dd') ArrivalDate
, format(CASE
WHEN Extendedto IS NULL THEN DepartureDate
ELSE Extendedto
END,'yyyy-MM-dd') AS DepartureDate
, SentTo AS Property, Roomno, RoomTypeRouting AS 'RoomType'

Thanks

RIAS

ASKER

Also Eric,

Display ' Roomno2' and 'RoomTypeRouting2 ' only when
AND ArrivalDate2 >= '2016-01-01'
AND ArrivalDate2 <= @EndDate
AND COALESCE(ExtendedTo2,DepartureDate2) >= @StartDate

Cheers

Éric Moreau

replace:

,       [RoomTypeRouting2]
,      [Roomno2]

Open in new window

With:

,  case when ArrivalDate2 >= '2016-01-01' AND ArrivalDate2 <= @EndDate AND ExtendedTo2,DepartureDate2 >= @StartDate then [RoomTypeRouting2]  else null end as RoomTypeRouting2
,  case when ArrivalDate2 >= '2016-01-01' AND ArrivalDate2 <= @EndDate AND ExtendedTo2,DepartureDate2 >= @StartDate then [Roomno2]  else null end as Roomno2

Open in new window

RIAS

ASKER

Thanks Eric,
Still the query is not complete , will post a new question related to this as its a new requirement.