question

Fiotomas-6670 avatar image
0 Votes"
Fiotomas-6670 asked ErlandSommarskog commented

Error message but can't find any error

Hi, the comma after 'STU_A' gives me an error but I don't understand why. Can anyone help?

SELECT
BookRef,
SourceSiteId,
BookingStatus AS Status,
RoomTypeCode AS Decsription,
MarketSegment AS MARKSEG,
DateArrive,
DateDepart,
PackageCode,
DATENAME(month, DateArrive) AS Month,
Year(DateArrive) ArrivalYear,
ChargeTotalNett AS TOTALAccomREV


FROM SyncReservations

WHERE (CreatedTimestamp < '2020-08-01' AND DateArrive BETWEEN '2020-09-01' AND '2022-07-31')

or (PackageCode NOT LIKE
'STU_A' ,
'STU_B',
'STU_C',
'STU_D' ,
'STU_A_2' ,
'STU_B_2',
'STU_C_2'
)

Order BY MarketSegment

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Fiotomas-6670 avatar image
0 Votes"
Fiotomas-6670 answered

Yes, thanks. What did you change?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered Fiotomas-6670 commented

Please check if below query works

 SELECT BookRef
  ,SourceSiteId
  ,BookingStatus AS STATUS
  ,RoomTypeCode AS Decsription
  ,MarketSegment AS MARKSEG
  ,DateArrive
  ,DateDepart
  ,PackageCode
  ,DATENAME(month, DateArrive) AS Month
  ,Year(DateArrive) ArrivalYear
  ,ChargeTotalNett AS TOTALAccomREV
 FROM SyncReservations
 WHERE (
  CreatedTimestamp < '2020-08-01'
  AND DateArrive BETWEEN '2020-09-01'
  AND '2022-07-31'
  )
  AND (
  PackageCode NOT in ( 'STU_A'
  ,'STU_B'
  ,'STU_C'
  ,'STU_D'
  ,'STU_A_2'
  ,'STU_B_2'
  ,'STU_C_2'
  )
  )
  Order BY MarketSegment


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, sorry, one more thing. It is not giving me an error but despite setting date arrive between '2020-09-01' AND '2022-07-31' I keep getting bookings from 2017.
I have also tried taking the CreatedTime Stamp away like this
WHERE (
DateArrive BETWEEN '2020-09-01'
AND '2022-07-31'
)

But still getting entries from 2017.
Why isn't it picking up the correct dates?


0 Votes 0 ·

Please check the where conditions carefully. I have modified OR to AND now. See if it is working now.

1 Vote 1 ·

Thank you.

0 Votes 0 ·
Fiotomas-6670 avatar image
0 Votes"
Fiotomas-6670 answered Fiotomas-6670 published

I have another query.
The below is returning o results. No error but no values in the columns. The query is correct.

(
(
SELECT
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
Count(SyncReservations.RoomPickId),
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
FROM
SyncLookupMediaCodes INNER JOIN SyncReservations ON (SyncReservations.MediaSourceCode=SyncLookupMediaCodes.MediaCode AND SyncReservations.SourceSiteId=SyncLookupMediaCodes.SourceSiteId)
INNER JOIN SyncResStayDetails ON (SyncResStayDetails.BookRef=SyncReservations.BookRef AND SyncReservations.RoomPickId=SyncResStayDetails.RoomPickId)

WHERE
(
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.SourceSiteId IN ( 'LSE01','LSEBA','LSEBW','LSECA','LSEGH','LSEHH','LSENH','LSESW' )
)
OR
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.SourceSiteId IN ( 'LSECS','LSEPA','LSERA' )
)
)
GROUP BY
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
EXCEPT
SELECT
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
Count(SyncReservations.RoomPickId),
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
FROM
SyncLookupMediaCodes INNER JOIN SyncReservations ON (SyncReservations.MediaSourceCode=SyncLookupMediaCodes.MediaCode AND SyncReservations.SourceSiteId=SyncLookupMediaCodes.SourceSiteId)
INNER JOIN SyncResStayDetails ON (SyncResStayDetails.BookRef=SyncReservations.BookRef AND SyncReservations.RoomPickId=SyncResStayDetails.RoomPickId)

WHERE
(
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.SourceSiteId IN ( 'LSE01','LSEBA','LSEBW','LSECA','LSEGH','LSEHH','LSENH','LSESW' )
)
OR
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.PackageCode = 'STU'
AND
SyncReservations.SourceSiteId IN ( 'LSECS','LSEPA','LSERA' )
)
)
GROUP BY
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
)
EXCEPT
(
SELECT
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
Count(SyncReservations.RoomPickId),
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
FROM
SyncLookupMediaCodes INNER JOIN SyncReservations ON (SyncReservations.MediaSourceCode=SyncLookupMediaCodes.MediaCode AND SyncReservations.SourceSiteId=SyncLookupMediaCodes.SourceSiteId)
INNER JOIN SyncResStayDetails ON (SyncResStayDetails.BookRef=SyncReservations.BookRef AND SyncReservations.RoomPickId=SyncResStayDetails.RoomPickId)

WHERE
(
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.PackageCode = 'STU'
AND
SyncReservations.SourceSiteId IN ( 'LSE01','LSEBA','LSEBW','LSECA','LSEGH','LSEHH','LSENH','LSESW' )
)
OR
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.SourceSiteId IN ( 'LSECS','LSEPA','LSERA' )
)
)
GROUP BY
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
EXCEPT
SELECT
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
Count(SyncReservations.RoomPickId),
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
FROM
SyncLookupMediaCodes INNER JOIN SyncReservations ON (SyncReservations.MediaSourceCode=SyncLookupMediaCodes.MediaCode AND SyncReservations.SourceSiteId=SyncLookupMediaCodes.SourceSiteId)
INNER JOIN SyncResStayDetails ON (SyncResStayDetails.BookRef=SyncReservations.BookRef AND SyncReservations.RoomPickId=SyncResStayDetails.RoomPickId)

WHERE
(
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.PackageCode = 'STU'
AND
SyncReservations.SourceSiteId IN ( 'LSE01','LSEBA','LSEBW','LSECA','LSEGH','LSEHH','LSENH','LSESW' )
)
OR
(
cast(SyncReservations.DateArrive AS DATE) > cast(getdate() as date)
AND
SyncReservations.PackageCode = 'STU'
AND
SyncReservations.SourceSiteId IN ( 'LSECS','LSEPA','LSERA' )
)
)
GROUP BY
SyncReservations.SourceSiteId,
cast(SyncReservations.CreatedTimestamp AS DATE),
SyncReservations.PackageCode,
SyncLookupMediaCodes.MediaCode,
concat(SyncReservations.PrimaryGuestTitle,' ',SyncReservations.PrimaryGuestForename,' ',SyncReservations.PrimaryGuestSurname),
cast(SyncReservations.DateArrive AS DATE),
cast(SyncReservations.DateDepart AS DATE),
SyncReservations.BookRef,
SyncReservations.BookingStatus,
SyncResStayDetails.RatePlanCode
)
)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

The below is returning o results. No error but no values in the columns. The query is correct.

If the query is correct, what is the problem? I mean if the query is correct, 0 rows is the correct results. Or?

Anyway, since we don't know your tables, we can't debug you query. But run each of these queries combined with EXCEPT on the own. Then add them together one by one to figure out where things may go wrong.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, no, there should be values. reservations, dates, etc
Could it be that there are too much data to retrieve?

0 Votes 0 ·

If you don't get back any rows, it is because the query returns no rows. Thus, if you expect to get rows back, there is something wrong with your query, and you will need to debug it to find out where more precisely where things go wrong.

0 Votes 0 ·