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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
PortletPaulEE Topic AdvisorCommented:
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
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

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, 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
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.