Skip to content

Instantly share code, notes, and snippets.

@OnlyByGrace
Created November 26, 2019 01:21
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save OnlyByGrace/ea483767e48a2f619cf30821afd5081f to your computer and use it in GitHub Desktop.
Save OnlyByGrace/ea483767e48a2f619cf30821afd5081f to your computer and use it in GitHub Desktop.
SQL Query Without Location Check
DECLARE @attributeId AS INT = 6062 -- << CHANGE ME!
SELECT
[Id],
[Name],
CASE
WHEN (SUBSTRING(ReservationiCal, 0, CHARINDEX('UID:',ReservationiCal)) != SUBSTRING(CalendariCal, 0, CHARINDEX('UID:',ReservationiCal))) THEN '<span class="bg-warning">Schedules are different</span>'
END as [Schedules],
CASE
WHEN ContactMismatches = 1 THEN '<span class="bg-warning">Contact information is different</span>'
END as [Event Contact]
FROM
(
SELECT Reservation.[Id] as Id
,Reservation.[Name] as Name
,EventItemOccurrence.Location as CalendarLocation
,STUFF([Schedule].iCalendarContent, CHARINDEX('DTSTAMP:', [Schedule].iCalendarContent), 24, '') as [ReservationiCal]
,STUFF([CalSchedule].iCalendarContent, CHARINDEX('DTSTAMP:', [Schedule].iCalendarContent), 24, '') as [CalendariCal]
,AttributeValue.[Value] AS EventItemOccurrence
,CASE
WHEN EventItemOccurrence.ContactEmail <> Reservation.EventContactEmail THEN 1
WHEN EventItemOccurrence.ContactPhone <> Reservation.EventContactPhone THEN 1
WHEN EventItemOccurrence.ContactPersonAliasId <> Reservation.EventContactPersonAliasId THEN 1
ELSE 0
END as ContactMismatches
FROM [dbo].[_com_centralaz_RoomManagement_Reservation] as Reservation
LEFT JOIN [dbo].[Schedule] ON Reservation.ScheduleId = Schedule.Id
LEFT JOIN [dbo].AttributeValue ON AttributeValue.EntityId = Reservation.Id AND AttributeValue.AttributeId = @attributeId
LEFT JOIN [dbo].EventItemOccurrence ON AttributeValue.Value = EventItemOccurrence.Id
LEFT JOIN [dbo].Schedule as CalSchedule ON EventItemOccurrence.ScheduleId = CalSchedule.Id
WHERE AttributeValue.[Value] IS NOT NULL
) as Reservations
WHERE SUBSTRING(ReservationiCal, 0, CHARINDEX('UID:',ReservationiCal)) != SUBSTRING(CalendariCal, 0, CHARINDEX('UID:',ReservationiCal)) OR ContactMismatches = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment