SQL Query with where clause

Hello,
I have query which works perfect, just need a small modification. Please refer to he expected result attachement.

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

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6,RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT *
FROM   Unpivoted 
WHERE 
  (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window

Book1.xlsx
RIASAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Fixed the syntax issue..
DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     -- removed sub-queries for shorter sample.
     SELECT GuestName ,
            SentTo,
            'A6',               -- This was missing.
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6    -- RequestedBy is not needed.
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
),
Filtered AS (
    SELECT *
    FROM   Unpivoted
    WHERE   ArrivalDate BETWEEN '20160101' AND @EndDate
        AND DepatureDate >= @StartDate
)
SELECT  O.GuestName,
        O.Property,
        O.ArrivalType,
        O.ArrivalDate,
        O.DepatureDate,
        (STUFF((
                SELECT  ', ' + I.RoomType
                FROM    Filtered I
                WHERE   I.GuestName = O.GuestName AND
                    I.Property = O.Property AND
                    I.ArrivalType = O.ArrivalType AND
                    I.ArrivalDate = O.ArrivalDate AND
                    I.DepatureDate = O.DepatureDate AND
                    I.RoomNo = O.RoomNo
                FOR XML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'), 1, 2, '')) as RoomTypes, 
        O.RoomNo
FROM    Filtered O
GROUP BY O.GuestName,
    O.Property,
    O.ArrivalType,
    O.ArrivalDate,
    O.DepatureDate,
    O.RoomNo;

Open in new window


Also did you get a chance to look into the query I've provided..
DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,
			'A6',          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepartureDate, t1.RoomNo, t1.RequestedBy
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepartureDate = t2.DepartureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepartureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should help..
DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6,RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepartureDate, t1.RoomNo, t1.RequestedBy
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepartureDate = t2.DepartureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepartureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
BTW, all your sub queries that you are UNIONing must return exactly the same number of columns and in the same order. I see that your last sub query (the one with ArrivalDate6) is missing the ArrivalType A6 and seems to have an extra column (RequestedBy). That leads to error for sure.
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.

 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Nice Catch, Eric..
Surprisingly, SQL Server parsing can't catch this error and hence I haven't noticed it..
0
 
ste5anSenior DeveloperCommented:
The problem with the last sub-query is, that it has the same number of columns, but different ones. The biggest problem is the missing constant ArrivalType 'A6' between SentTo and ArrivalDate6.

You can use FOR XML to concat these values:

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

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     -- removed sub-queries for shorter sample.
     SELECT GuestName ,
            SentTo,
            'A6',               -- This was missing.
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6    -- RequestedBy is not needed.
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
),
Filtered AS (
    SELECT *
    FROM   Unpivoted
    WHERE   ArrivalDate BETWEEN '20160101' AND @EndDate
        AND DepartureDate >= @StartDate
)
SELECT  O.GuestName,
        O.Property,
        O.ArrivalType,
        O.ArrivalDate,
        O.DapatureDate,
        (STUFF((
                SELECT  ', ' + I.RoomType
                FROM    Filtered I
                WHERE   I.GuestName = O.GuestName AND
                    I.Property = O.Property AND
                    I.ArrivalType = O.ArrivalType AND
                    I.ArrivalDate = O.ArrivalDate AND
                    I.DapatureDate = O.DapatureDate AND
                    I.RoomNo = O.RoomNo
                FOR XML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'), 1, 2, '') AS RoomTypes,
        O.RoomNo
FROM    Filtered O
GROUP BY O.GuestName,
    O.Property,
    O.ArrivalType,
    O.ArrivalDate,
    O.DapatureDate,
    O.RoomNo;

Open in new window


The .value() with the TYPE modifier in th XML is necessary to avoid entiies like & instead of & in the result.
1
 
PortletPaulfreelancerCommented:
Have:
+-----------+-----------+-------------+---------------+--------+-------------+-------------+
| GuestName | Property  | ArrivalDate | DepartureDate | RoomNo |  RoomType   | RequestedBy |
+-----------+-----------+-------------+---------------+--------+-------------+-------------+
| abc       | property1 | 2018-04-22  | 2018-04-26    | NULL   | roomtype1   | requested1  |
| abc       | tbn       | 2018-04-20  | 2018-04-23    | NULL   | roomtype345 | requestor2  |
| abc       | property1 | 2018-04-22  | 2018-04-26    | NULL   | roomtype456 | requested1  |
+-----------+-----------+-------------+---------------+--------+-------------+-------------+

Want:
+-----------+-----------+-------------+---------------+--------+-----------------------+-------------+
| GuestName | Property  | ArrivalDate | DepartureDate | RoomNo |       RoomType        | RequestedBy |
+-----------+-----------+-------------+---------------+--------+-----------------------+-------------+
| abc       | property1 | 2018-04-22  | 2018-04-26    | NULL   | roomtype1,roomtype456 | requested1  |
| abc       | tbn       | 2018-04-20  | 2018-04-23    | NULL   | roomtype345           | requestor2  |
+-----------+-----------+-------------+---------------+--------+-----------------------+-------------+

Open in new window


What would be easier to ask for is that you need to concatenate multiple RoomTypes per requestor into a comma-separated list

For SQL Server versions up to SQL 2017 use the XML based approach as shown by Ste5an, if you happen to have SQL 2017 you could use the much simpler string_agg() function instead.
0
 
RIASAuthor Commented:
Thanks Experts! Will try and brb...
0
 
RIASAuthor Commented:
DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     -- removed sub-queries for shorter sample.
     SELECT GuestName ,
            SentTo,
            'A6',               -- This was missing.
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6    -- RequestedBy is not needed.
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
),
Filtered AS (
    SELECT *
    FROM   Unpivoted
    WHERE   ArrivalDate BETWEEN '20160101' AND @EndDate
        AND DepatureDate >= @StartDate
)
SELECT  O.GuestName,
        O.Property,
        O.ArrivalType,
        O.ArrivalDate,
        O.DepatureDate,
        (STUFF((
                SELECT  ', ' + I.RoomType
                FROM    Filtered I
                WHERE   I.GuestName = O.GuestName AND
                    I.Property = O.Property AND
                    I.ArrivalType = O.ArrivalType AND
                    I.ArrivalDate = O.ArrivalDate AND
                    I.DepatureDate = O.DepatureDate AND
                    I.RoomNo = O.RoomNo
                FOR XML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'), 1, 2, '') as RoomTypes, 
        O.RoomNo
FROM    Filtered O
GROUP BY O.GuestName,
    O.Property,
    O.ArrivalType,
    O.ArrivalDate,
    O.DepatureDate,
    O.RoomNo;

Open in new window



Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'as'.

This line : value('.','NVARCHAR(MAX)'), 1, 2, '') as RoomTypes,
Any suggestions? Thanks
0
 
RIASAuthor Commented:
Thanks, Yes sure !
0
 
RIASAuthor Commented:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.



Thanks
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this once..
DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';
DECLARE @defDate as Date = '20160101';


WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,
			'A6',          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6,RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepartureDate, t1.RoomNo, t1.RequestedBy
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepartureDate = t2.DepartureDate
          AND (t2.ArrivalDate between @defDate AND  @EndDate
   AND t2.DepartureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between @defDate AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window

0
 
RIASAuthor Commented:
Hello Raja,
Your query was spot on!
0
 
RIASAuthor Commented:
Thanks!
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
0
 
RIASAuthor Commented:
Hello Raja,
There is a problem in this query :
DECLARE @EndDate AS DATE = '20180507';
DECLARE @StartDate AS DATE = '20180514';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,
			'A6',          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepatureDate, t1.RoomNo
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepatureDate = t2.DepatureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepatureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between '20160101' AND  @EndDate
   AND DepatureDate >= @StartDate)

Open in new window


It concatenates  but  the rows are still repeated.
Please find the result

The concatenated rows are repeated.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
can you pls attach the result to verify once..
0
 
RIASAuthor Commented:
Please find the attached file.
Book1.xlsx
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Missed DISTINCT clause..
DECLARE @EndDate AS DATE = '20180507';
DECLARE @StartDate AS DATE = '20180514';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,
			'A6',          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT DISTINCT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepatureDate, t1.RoomNo
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepatureDate = t2.DepatureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepatureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between '20160101' AND  @EndDate
   AND DepatureDate >= @StartDate)

Open in new window

1
 
RIASAuthor Commented:
Perfect!!!!Thanks
0
 
RIASAuthor Commented:
Hello Raja,
Thanks but still the same .
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you pls post the current result set and the expected one to check out once..
0
 
RIASAuthor Commented:
Sure!
0
 
RIASAuthor Commented:
Sorry wrong file attached.
0
 
RIASAuthor Commented:
Please wait fro a min
0
 
RIASAuthor Commented:
Please find the corrected file.
Book1.xlsx
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Okay, your original excel file doesn't have any values for RoomNo and hence it wasn't modified, its modified now..
DECLARE @EndDate AS DATE = '20180507';
DECLARE @StartDate AS DATE = '20180514';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',
            ArrivalDate5,
            ISNULL(Extendedto5, DepartureDate5),
            Roomno5,
            RoomTypeRouting5
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
UNION ALL
     SELECT GuestName ,
            SentTo,
			'A6',          
            ArrivalDate6,
            ISNULL(Extendedto6, DepartureDate6) ,
            Roomno6,
            RoomTypeRouting6
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
)
SELECT DISTINCT t1.GuestName, t1.Property, t1.ArrivalDate, t1.DepatureDate
,STUFF(( SELECT ',' + CAST(RoomNo as varchar(10))
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepatureDate = t2.DepatureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepatureDate >= @StartDate)
		  ORDER BY RoomNo
            FOR XML PATH('')),1,1,'') RoomNo
,STUFF(( SELECT ',' + RoomType
           FROM Unpivoted t2
          WHERE t1.GuestName = t2.GuestName
		  AND t1.Property = t2.Property
		  AND t1.ArrivalDate = t2.ArrivalDate
		  AND t1.DepatureDate = t2.DepatureDate
          AND (t2.ArrivalDate between '20160101' AND  @EndDate
   AND t2.DepatureDate >= @StartDate)
		  ORDER BY RoomType
            FOR XML PATH('')),1,1,'') RoomType	
FROM  Unpivoted t1
WHERE (ArrivalDate between '20160101' AND  @EndDate
   AND DepatureDate >= @StartDate)

Open in new window

1
 
RIASAuthor Commented:
Trying.....
0
 
RIASAuthor Commented:
Perfecto!!!!Cheers!
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
0
 
RIASAuthor Commented:
Raja,
i have uploaded n esimple query question please check
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.

All Courses

From novice to tech pro — start learning today.