Search code examples
sqlsql-servertemp

Track changes in a table on a daily basis


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:

  • DateStamp,
  • PropertyCode,
  • PropertyName,
  • FacilityBabyFriendly,
  • FacilityDogsAccepted,
  • FacilitySwimmingPool,
  • FacilityHotTub, FacilityInternetAccess,
  • FacilitySeaViews,
  • FacilityParking

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.


Solution

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