• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 38
  • Last Modified:

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
0
RIAS
Asked:
RIAS
  • 5
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Add your new columns before the FROM clause (and don't forget comas):

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' 
 ,       [RoomTypeRouting2] 
 ,     [RoomTypeRouting3] 
,      [RoomTypeRouting4] 
,      [ArrivalDate2] 
,      [ArrivalDate3] 
,      [ArrivalDate4] 
,      [DepartureDate2] 
,      [DepartureDate4] 
,      [DepartureDate3] 
,      [Extendedto2] 
,      [Extendedto3] 
,      [Extendedto4] 
,      [Roomno2] 
,      [Roomno3] 
,      [Roomno4] 
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

0
 
RIASAuthor Commented:
Thanks Eric! Will try and brb
0
 
RIASAuthor Commented:
Thank you Sir! Worked like a charm.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RIASAuthor Commented:
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
0
 
RIASAuthor Commented:
Also Eric,

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

Cheers
0
 
Éric MoreauSenior .Net ConsultantCommented:
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

0
 
RIASAuthor Commented:
Thanks Eric,
Still the query is not complete , will post a new question related to this as its a new requirement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now