Question

Photo of Michael Garrison

0

Populating "First visit" date

2

I'm working on a report which will rely heavily on the "First visit" date- but my problem is that for most of the data imported via Excavator, those dates are blank.

I have a Group set up in rock with people who I want to fill in the "First Visit" date to be equal to the "Date Record Created" attribute.

Using the Batch Update function I could set the "First Visit" date to a single arbitrary date for every person in the group, but I'd like it to be just a little more accurate.

I see that the "First visit" attribute is type 717, so I started experimenting with using an INSERT statement to create the value for each person. But the GUID is required and isn't auto-generated like the Id is upon insert. Is there a function I can call to generate a GUID on the fly? And is there somewhere else that GUID needs to get stored in another table? (I tried searching through the entire database to find another reference of a GUID Rock created when I added a "First visit" date to someone's profile for the first time but came up empty - but I'm not confident that I might not have missed it and that it will turn out to be necessary for Rock to function properly.

Any insight would be helpful, thanks!

  • Michael Garrison

    Michael Garrison

    SELECT NEWID() appears to return a similarly-formatted string as the GUID- is that what ROCK uses or are there checks to make sure the returned GUID is in fact unique?

    • Photo of David Leigh

      0

      Michael,

      There is no (easy) way to check that the GUID is unique, but you don't need to for a few reasons:

      1. The probability of GUID collisions anywhere is miniscule (1 in 2^128) - pretty good odds!
      2. The GUID will only be used to identify a row only in the context of a single table - so the chances of duplicates is even further reduced.
      3. GUID columns in Rock have a unique index - so your INSERT will simply fail if there are collisions with other GUIDs in the same table.

      GUIDs aren't used as foreign keys to relate records, they are only used to store references to rows - so you don't need to worry about compromising database integrity.

      So the short answer is: Yes, using NEWID() is the correct way to generate values for new rows in the table.

      Hope this helps!

      • Michael Garrison

        Michael Garrison

        Helps a lot- thanks. I'm curious about the need/difference for both a Row ID and a GUID. But that's probably a discussion for another time... and now I know that's all it's used for I'll run my query and go on my merry way.

        • David Leigh

          David Leigh

          The Row ID is used internally to create links (foreign keys) between related records in different tables. It is an integer value, very efficient for performing index lookups and only unique for a single table in a single database. The GUID is (almost certainly) unique across database instances, so very useful for using as a portable reference to a record - but it is also a very large, unsequenced value that is (comparatively) inefficient to use for database lookups and join operations. So they are both useful in different scenarios, if that makes sense.

        • Photo of Michael Garrison

          0

          To be explicit, here's the query I've come up with (the group containing the people I want to set "First Visit" dates on is GroupId=6240)

          DELETE FROM [AttributeValue] WHERE [EntityId] IN (SELECT [PersonId] FROM [GroupMember] WHERE [GroupId]=6240) AND [AttributeId]=717 AND [Value] IS NULL;
INSERT INTO [AttributeValue] ([IsSystem],[AttributeId],[EntityId],[Value],[Guid])
 SELECT
  '' AS 'IsSystem',
  717 AS 'AttributeId',
  p.[Id] AS 'EntityId',
  CONVERT(VARCHAR(27),p.[CreatedDateTime],101) AS 'Value',
  NEWID() AS 'Guid'
 FROM [Person] p
 WHERE
  p.[Id] IN (SELECT [PersonId] FROM [GroupMember] WHERE [GroupId]=6240)
 AND
  NOT EXISTS (SELECT 1 FROM [AttributeValue] WHERE [AttributeId]=717 AND [EntityId]=p.[Id]);

          It appears to work when wrapped in ROLLBACK TRANSACTION, so I'm just going to wait a bit before commiting it and see if anyone knows of any place I should update the new GUID values as well.