sql query with an enhancement

Hello,
I have a query which works perfectly fine , but need an enhancement in it.
DECLARE  @EndDate AS DATE = '2018-02-19'
DECLARE @StartDate as date = '2018-02-12'


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' 



,     format(ArrivalDate2,'yyyy-MM-dd') ArrivalDate2
,     format(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
             END,'yyyy-MM-dd') AS DepartureDate2
,     [Roomno2] 
,     [RoomTypeRouting2] 

,     format(ArrivalDate3,'yyyy-MM-dd') ArrivalDate3
,     format(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
            END,'yyyy-MM-dd') AS DepartureDate3
,     [Roomno3]
,     [RoomTypeRouting3]

,     format(ArrivalDate4,'yyyy-MM-dd') ArrivalDate4
,     format(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
             END,'yyyy-MM-dd') AS DepartureDate4 
,     [Roomno4] 
,     [RoomTypeRouting4] 
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


Need to display
1:
  [Roomno2]
,     [RoomTypeRouting2]  only when

WHERE ( DeleteRecord is null or DeleteRecord = 0)
AND ArrivalDate2 >= '2016-01-01'
AND ArrivalDate2 <= @EndDate
AND COALESCE(ExtendedTo2,DepartureDate2) >= @StartDate

2:
 
  [Roomno3]
,     [RoomTypeRouting3]  only when

WHERE ( DeleteRecord is null or DeleteRecord = 0)
AND ArrivalDate3 >= '2016-01-01'
AND ArrivalDate3 <= @EndDate
AND COALESCE(ExtendedTo3,DepartureDate3) >= @StartDate

Similar for  [Roomno4] ,RoomTypeRouting4]


Any suggestions are appreciated!
Thanks
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Separate UI from logic. Then your queries are simpler to read. The format of your date is irrelevant to the problem. Also consider really doing this in the front-end, where it belongs to.

Use unambiguous date literals like 'yyyymmdd', thus e.g. '20160101' instead of '2016-01-01'.

Also I prefer IIF() over CASE in simple conditions, but much worse here  ISNULL() is the correct choice:

DECLARE @EndDate AS DATE = '20180219';
DECLARE @StartDate AS DATE = '20180212';

SELECT GuestName ,
       SentTo AS Property ,
       ArrivalDate ,
       ISNULL(Extendedto, DepartureDate) AS DepartureDate ,
       Roomno ,
       RoomTypeRouting AS RoomType ,
       ArrivalDate2 AS ArrivalDate2 ,
       ISNULL(Extendedto2, DepartureDate2) AS DepartureDate2 ,
       [Roomno2] ,
       [RoomTypeRouting2] ,
       ArrivalDate3 AS ArrivalDate3 ,
       ISNULL(Extendedto3, DepartureDate3) AS DepartureDate3 ,
       [Roomno3] ,
       [RoomTypeRouting3] ,
       ArrivalDate4 AS ArrivalDate4 ,
       ISNULL(Extendedto4, DepartureDate4) AS DepartureDate4 ,
       [Roomno4] ,
       [RoomTypeRouting4]
FROM   CORR
WHERE  (   DeleteRecord IS NULL
           OR DeleteRecord = 0 )
       AND ArrivalDate >= '2016-01-01'
       AND ArrivalDate <= @EndDate
       AND ISNULL(ExtendedTo, DepartureDate) >= @StartDate;

Open in new window


Now to your problem: seems like a kind of pivoting. Are room2 to room4 only displayed, when there is a value for room?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Thanks!
Are room2 to room4 only displayed, when there is a value for room?
Yes , they are to be displayed only when there is an value.
0
ste5anSenior DeveloperCommented:
E.g.

DECLARE @EndDate AS DATE = '20180219';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted
AS ( SELECT GuestName ,
            SentTo AS Property ,
            'A1' AS ArrivalType ,
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate) AS DepartureDate ,
            Roomno ,
            RoomTypeRouting AS RoomType
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo AS Property ,
            'A2' AS ArrivalType ,
            ArrivalDate2 AS ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2) AS DepartureDate2 ,
            [Roomno2] ,
            [RoomTypeRouting2]
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo AS Property ,
            'A3' AS ArrivalType ,
            ArrivalDate3 AS ArrivalDate3 ,
            ISNULL(Extendedto3, DepartureDate3) AS DepartureDate3 ,
            [Roomno3] ,
            [RoomTypeRouting3]
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo AS Property ,
            'A3' AS ArrivalType ,
            ArrivalDate4 AS ArrivalDate4 ,
            ISNULL(Extendedto4, DepartureDate4) AS DepartureDate4 ,
            [Roomno4] ,
            [RoomTypeRouting4]
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 )
SELECT *
FROM   Unpivoted P
WHERE  P.ArrivalDate >= '20160101'
       AND P.ArrivalDate <= @EndDate
       AND P.DepartureDate >= @StartDate;

Open in new window

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:
Thanks Sir, Will try and brb!
0
RIASAuthor Commented:
Thanks Ste5an, it worked like a charm!
0
RIASAuthor Commented:
The client actually prefer in the accepted solution format.
0
RIASAuthor Commented:
Ste5an,
Just a quick one:

Where is the where clause for
WHERE  (   DeleteRecord IS NULL
           OR DeleteRecord = 0 )
       AND ArrivalDate2 >= '2016-01-01'
       AND ArrivalDate2 <= @EndDate
       AND ISNULL(ExtendedTo2, DepartureDate2) >= @StartDate;

Open in new window

Also
WHERE  (   DeleteRecord IS NULL
           OR DeleteRecord = 0 )
       AND ArrivalDate3 >= '2016-01-01'
       AND ArrivalDate3 <= @EndDate
       AND ISNULL(ExtendedTo3, DepartureDate3) >= @StartDate;

Open in new window

same for ArrivalDate4 , ExtendedTo4, DepartureDate4

The query needs to find results on these as well and not only

WHERE  (   DeleteRecord IS NULL
           OR DeleteRecord = 0 )
       AND ArrivalDate >= '2016-01-01'
       AND ArrivalDate <= @EndDate
       AND ISNULL(ExtendedTo, DepartureDate) >= @StartDate;

Open in new window



Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

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.