Question

Photo of Austin Barnett

0

SQL Query - First Time Visit

2

Hey everyone,

Trying to display a Dynamic Data Block on our page that will show a report of the first time guest for a given date.

I have started with this SQL but need to know where to look to learn how to make this then return the specific name of the person.

SELECT * FROM [dbo].[AttributeValue]
WHERE AttributeId=717 AND Value LIKE '2015-10-25%';

Thanks for the help :)

Austin

SQL
  • Photo of Austin Barnett

    1

    OK, so i got it, i'm proud of myself...LOL, if anyone sees anything I could do to make it better, that would be awesome

    SELECT [dbo].[AttributeValue].[EntityId], [dbo].[Person].[Id], [dbo].[Person].[FirstName], [dbo].[Person].[LastName], [dbo].[Person].[Email]
FROM AttributeValue
INNER JOIN Person
ON [dbo].[AttributeValue].[EntityId]=[dbo].[Person].[Id]
WHERE [dbo].[AttributeValue].[AttributeId]=717 AND Value LIKE '2015-10-25%';

     

     

    Austin

    • Mason Kinyon

      Mason Kinyon

      Looks great! My only recommendation would be to use the ValueAsDateTime column when comparing attribute value dates:


      SELECT
      AV.[EntityId],
      P.[Id],
      P.[FirstName],
      P.[LastName],
      P.[Email]
      FROM AttributeValue AV
      INNER JOIN Person P
      ON AV.[EntityId] = P.[Id]
      WHERE AV.[AttributeId] = 717
      AND AV.ValueAsDateTime = '10-25-2015'

      • David Turner

        David Turner

        ValueAsDateTime will not be null for attributes that store dates (like a First Visit attribute). Using ValueAsDateTime also allows you to do more realistic date compares for example to get all people who's first visit was within the last week, you could use a compare like:
        AV.[ValueAsDateTime] > DATEADD( wk, -1, GETDATE() )

      • Photo of Dillan Cagnetta

        0

        Hi David, please have a look. The person has a FIRST VISIT attribute but still has ValueAsDateTime = NULL

        NULL.png