Sql query syntax in WHERE clause

Hello,
I have an SQL query which works but need and additional parameter
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 ,
            'A4' 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



Currently the where clause is only based on the ArrivalDate  and Departure Date but the requirement is

Something like this:

WHERE  isnull(deleterecord,0) = 0
AND 
 (
  (ArrivalDate between '2016-01-01' AND  @EndDate
   AND ISNULL(ExtendedTo, DepartureDate) >= @StartDate)
  OR
  (ArrivalDate2 between '2016-01-01' AND  @EndDate
   AND ISNULL(ExtendedTo2, DepartureDate2) >= @StartDate)
  OR
  (ArrivalDate3 between '2016-01-01' AND  @EndDate
   AND ISNULL(ExtendedTo3, DepartureDate3) >= @StartDate)
  OR
  (ArrivalDate4 between '2016-01-01' AND  @EndDate
   AND ISNULL(ExtendedTo4, DepartureDate4) >= @StartDate)
  )

Open in new window


but I am getting an syntax error when I use the where clause.
Any suggestions?

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

ste5anSenior DeveloperCommented:
Did you run it? What's wrong with the result?
RIASAuthor Commented:
Yes, its giving red squiggly lines on the where clause and error incorrect syntax
Thanks
Olaf DoschkeSoftware DeveloperCommented:
The CTE named "Unpivoted" does not include the field DeleteRecord, it already accounted its state in all the queries where clauses, so leave that out of your final where clause.

WHERE 
  (ArrivalDate between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo, DepartureDate) >= @StartDate)
  OR
  (ArrivalDate2 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo2, DepartureDate2) >= @StartDate)
  OR
  (ArrivalDate3 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo3, DepartureDate3) >= @StartDate)
  OR
  (ArrivalDate4 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo4, DepartureDate4) >= @StartDate)

Open in new window


That wouldn't just cause a syntax error, but invalid column name. Anyway, it's one step in the right direction.

Parsing of the command doesn't reveal a syntax error, so perhaps you just don't have USEd the correct database.

Bye, Olaf.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

RIASAuthor Commented:
Thanks Olaf! Will try andbrb!
ste5anSenior DeveloperCommented:
The data filter in the outer SELECT filter the unioned set. Thus it filters all four cases. Thus what is wrong with the original query (it is syntactically correct)?
RIASAuthor Commented:
Olaf and Ste5an,
I get an error:

Msg 207, Level 16, State 1, Line 55
Invalid column name 'ExtendedTo'.
Msg 207, Level 16, State 1, Line 57
Invalid column name 'ArrivalDate2'.
Msg 207, Level 16, State 1, Line 57
Invalid column name 'ArrivalDate2'.
Msg 207, Level 16, State 1, Line 58
Invalid column name 'ExtendedTo2'.
Msg 207, Level 16, State 1, Line 58
Invalid column name 'DepartureDate2'.
Msg 207, Level 16, State 1, Line 60
Invalid column name 'ArrivalDate3'.
Msg 207, Level 16, State 1, Line 60
Invalid column name 'ArrivalDate3'.
Msg 207, Level 16, State 1, Line 61
Invalid column name 'ExtendedTo3'.
Msg 207, Level 16, State 1, Line 61
Invalid column name 'DepartureDate3'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'ArrivalDate4'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'ArrivalDate4'.
Msg 207, Level 16, State 1, Line 64
Invalid column name 'ExtendedTo4'.
Msg 207, Level 16, State 1, Line 64
Invalid column name 'DepartureDate4'.

Open in new window

RIASAuthor Commented:
Full query :
DECLARE @EndDate AS DATE = '20180222';
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 
  (ArrivalDate between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo, DepartureDate) >= @StartDate)
  OR
  (ArrivalDate2 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo2, DepartureDate2) >= @StartDate)
  OR
  (ArrivalDate3 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo3, DepartureDate3) >= @StartDate)
  OR
  (ArrivalDate4 between '20160101' AND  @EndDate
   AND ISNULL(ExtendedTo4, DepartureDate4) >= @StartDate)

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
You get invalid column name errors if the columns don't exist in the database, not because the names themselves are invalid or not allowed. You also get these errors, if you're not connected to the database having these fields and tables.

In your case, you're unioning several queries each having one of the ExtendedTo or DepartureDates, thus these columns, of which there are many in the original table, are reduced to one in the CTE result. MSSQL seems to be graceful about the different names you give the columns of the partial results.

So take a look at the first subquery field names: (GuestName, Property, ArrivalType, ArrivalDate, DepartureDate, Roomno, RoomType)
These are the only column names available in Unpivoted and your final query and final where clause also can only address these names.

So all you need now is
SELECT *
FROM   Unpivoted P
WHERE 
  (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window


No further ORed clauses, the Unioning of all the subqueries you do on CORR already "streamlines" (or unpivots) all the different ExtendedToX and DepartureDateX into the columns Unpivoted.ArrivalDate and Unpivoted.DepartureDate

Please have a look at what a CTE is and does. You show, that you don't care about what happens here. Tables with Date1,2,3,4,5 etc are a bad design in themselves anyway. Such naming points out you indeed want to save up to 5 records of something with an arrival or departure date into a separate table  (besides making it possible to have 6,10,100 records). This CTE, that you most probably got from a previous question is coping with the bad non normalized database design of the CORR table by pulling all these separated columns of records into single columns of the Unpivoted CTE result, having multiple records instead.

Sorry, I should have seen this in your original post already. It slips a usual SQL developers mind, because such design is so off the scale, that you have to concentrate on this to see it. All the subqueries do have different names, but in a Union, the first subquery defines the result column names and in further subqueries, the columns just need to have the correct type to get appended by the union operation, the names don't matter. It would become more apparent if you'd had a CTE with the section defining its result structure with column names and their data types before even starting the CTE query.

The main point is, while CORR has DepartureDate, DepartureDate2, DepartureDate3, DepartureDate4, etc, The CTE you query with your final query does not, it doesn't query CORR anymore, it queries the CTE result, which is named "Unpivoted" by the line WITH Unpivoted AS (query). That intermediate query result has data coming from CORR ExtendTo and DepartureDate columns, but these names are not inherited. The CTE query cuts CORR columns and puts them in multiple rows. That concept can be named unpivoting, though "real" unpivoting works differently, this is sufficient here for you.

All that simplifies what you need to do in the end, it frees you from needing 4 or 5 where clauses for all these columns, as you now only have each of them once. Can't you see, that this is making things simpler. To stress it out even very obvious: If your original CORRS table would already have multiple rows instead of so many similarly named and numbered columns, your only query - without and CTE portion - would need to be what you now do as final query after the CTE code, you would mainly SELECT * FROM GoodDesignedCORR WHERE ArrivalDate <= @EndDate AND DepartureDate >= @StartDate;

That's why database developers make that big fuss about the database normalization. It is a bit of a steep learning curve, but once you design normalized databases, you don't need to unpivot your stored data to make simple queries on it. It highers the complexity of database design, but it makes everyday work and extensibility and the major time you work on your data easier. You spare much more time for yourself, if you would invest in learning these basics.

Bye, Olaf.

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
Olaf DoschkeSoftware DeveloperCommented:
TL;DR: Just do this as your final query for the moment, and pay attention to the columns this result has. These are all the columns you might address in your final where clause, no less, but most important, no more:

SELECT * FROM Unpivoted

Open in new window


So just delete the whole where clause for the moment, just so you can see what you have at hand with the Unpivoted CTE result and can refer to in your final WHERE clause.

Bye, Olaf.
RIASAuthor Commented:
Txs! Will try and brb
RIASAuthor Commented:
Olaf,
Just a quick request ,can you please post the entire query as I am really struggling.

Thanks
RIASAuthor Commented:
Thanks a lot for highlighting many points. Yes indeed an learning curve.

Cheers
Olaf DoschkeSoftware DeveloperCommented:
Since you accepted an answer already I don't know if you still need or wan this, but htis is the full query, as it should rather be written:

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 )
SELECT *
FROM   Unpivoted 
WHERE 
  (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window


An Ideal CORR table would already have the structure, that the CTE creates by cutting your data in column groups and appending these cuts into one slimmer version of the data.

Notice how I stated the column names of Unpivoted just once where this name is defined, ever column name aliasing with AS NAME clauses becomes unnecessary. Also notice you forgot to change A3 to A4 as ArrivalType, that could also be a simple arrival number or simply be deducted from sort order of the dates. All these things make queries unnecessarily complex.

Bye, Olaf.
RIASAuthor Commented:
Thanks Olaf !
RIASAuthor Commented:
Olaf,
I am getting an error on:

WHERE 
  (ArrivalDate between '20160101' AND  @EndDate
   AND DepartureDate >= @StartDate)

Open in new window


Msg 207, Level 16, State 1, Line 51
Invalid column name 'DepartureDate'.

Thanks
RIASAuthor Commented:
Sorry Olaf,
Ignore my post.

It should have been
DapatureDate
Olaf DoschkeSoftware DeveloperCommented:
I have a type on the list of column names, DapatureDate. Fix that to DepartureDate

Bye, Olaf.
RIASAuthor Commented:
Got it!
Olaf DoschkeSoftware DeveloperCommented:
DapatureDate should be fixed to DepartureDate, unless you like my new word creation.
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
Query Syntax

From novice to tech pro — start learning today.