I have a table that stores each of our properties and whether they have certain icons against them on the website. This table is updated on a daily basis and date stamped.
Table Fields:
All the Facility fields are stored as a 1 [has icon] or 0 [no icon]
What I need is query that shows me, by property, if any of the facility fields have changed on a daily basis.
If anyone could point me in the right direction, if would be much appreciated.
The following query will give you the list of properties which has atleast one of the facility fields changed as compared with the previous day
SELECT
PropertyCode
,PropertyName
FROM Souce s1
WHERE
DateStamp = Current_Date
AND
EXISTS
(SELECT 1
FROM
Source s2
WHERE
s1.PropertyCode=s2.PropertyCode
AND DateStamp = Current_Date - 1 Day
AND
(
(s1.FacilityBabyFriendly<>s2.FacilityBabyFriendly)
OR
(s1.FacilityDogsAccepted<>s2.FacilityDogsAccepted)
OR
(s1.FacilitySwimmingPool<>s2.FacilitySwimmingPool)
OR
(s1.FacilityHotTub<>s2.FacilityHotTub)
OR
(s1.FacilityInternetAccess<>s2.FacilityInternetAccess)
OR
(s1.FacilitySeaViews<>s2.FacilitySeaViews)
OR
(s1.FacilityParking<>s2.FacilityParking)
)
)