Share via

Error message but can't find any error

Fiotomas 116 Reputation points
2021-05-06T08:19:50.823+00:00

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 | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

Fiotomas 116 Reputation points
2021-05-06T08:32:05.877+00:00

Yes, thanks. What did you change?

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator
    2021-05-06T21:48:24.703+00:00

    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.

    Was this answer helpful?

  2. Fiotomas 116 Reputation points
    2021-05-06T10:03:52.26+00:00

    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
    )
    )

    Was this answer helpful?

    0 comments
  3. Vaibhav Chaudhari 39,006 Reputation points Volunteer Moderator
    2021-05-06T08:30:13.81+00:00

    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

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.