Track changes in a table on a daily basis

Question

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.

If you are using MS SQL you have audit features included as from 2008 version. With it you can track all changes and access to your database objects. [MSDN doc](https://msdn.microsoft.com/en-us/library/cc280386.aspx) — DoubleT28, Jun 28 '16 at 09:22

score 0 · Answer 1 · answered Jun 28 '16 at 09:30

once thing you can do is calculate the hash of each row to compare if there is any change:

select hashbytes('MD5',cast(FacilityBabyFriendly * 1000000 + FacilityDogsAccepted * 100000 + FacilitySwimmingPool * 10000 + FacilityHotTub * 1000 + FacilityInternetAccess * 100 + FacilitySeaViews * 10 + FacilityParking
as varchar(100) )
) from yourtable

score 0 · Accepted Answer · answered Jun 28 '16 at 09:31

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

Perfect. Works exactly as I wanted . Many Thanks Claire – Claire Jun 28 '16 at 10:08 — Claire, Jun 28 '16 at 10:08
@Claire: Happy to help. upvote if you are satisfied :) – Srini Jun 28 '16 at 21:40 — Srini, Jun 28 '16 at 21:40

score 0 · Answer 3 · answered Jun 28 '16 at 09:50

Value <> 0 means facility has changed

SELECT DateStamp
      ,PropertyCode
      ,PropertyName
      ,FacilityBabyFriendly - LAG(FacilityBabyFriendly) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilityBabyFriendlyChanged
      ,FacilityDogsAccepted - LAG(FacilityDogsAccepted) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilityDogsAcceptedChanged
      ,FacilitySwimmingPool - LAG(FacilitySwimmingPool) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilitySwimmingPoolChanged
      ,FacilityHotTub - LAG(FacilityHotTub) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilityHotTubChanged
      ,FacilityInternetAccess - LAG(FacilityInternetAccess) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilityInternetAccessChanged
      ,FacilitySeaViews - LAG(FacilitySeaViews) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilitySeaViewsChanged
      ,FacilityParking - LAG(FacilityParking) OVER (PARTITION BY PropertyCode, PropertyName ORDER BY DateStamp) AS FacilityParkingChanged
FROM YourTable

Track changes in a table on a daily basis

3 Answers3