Question

Photo of Jeremy Turgeon

0

Using Liquid to Count

1

I have a dynamic data block that list days of the week, among other columns. How can I output the number of times a specific day occurs?

  • Jeremy Hoff

    Jeremy Hoff

    Hey Jeremy - post your SQL and we can adjust.

    If I understand the request correctly, you'd be looking to add a "COUNT()" column to the SELECT portion of your query.. or something similar.

    • Jeremy Turgeon

      Jeremy Turgeon

      SELECT
      w.[Id]
      , w.[Name]
      , [Status]
      , [InitiatorPersonAliasId]
      , p.[FirstName]
      , p.[LastName]
      , (SELECT TOP 1 [Value]
      FROM [AttributeValue] av
      INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] AND a.[EntityTypeId] = 113 AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId]
      WHERE [EntityId] = w.[Id] AND a.[Key] = 'DayOfTheWeek') AS [Day]
      , (SELECT TOP 1 [Value]
      FROM [AttributeValue] av
      INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] AND a.[EntityTypeId] = 113 AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId]
      WHERE [EntityId] = w.[Id] AND a.[Key] = 'Time') AS [Time]
      FROM
      [Workflow] w
      INNER JOIN [PersonAlias] pa ON pa.Id = w.[InitiatorPersonAliasId]
      INNER JOIN [Person] p ON p.[Id] = pa.[PersonId]
      WHERE w.[WorkflowTypeId] = 1032

      • Photo of Jeremy Turgeon

        0

        Found the answer.  Here's the SQL I'm using:

        ;WITH cte_DayCount AS
        (
                SELECT av.[Value] as DayCount,w.[Id]  AS Id
                FROM [AttributeValue] av
                INNER JOIN [Workflow] w ON av.[EntityId] = w.[Id] 
                INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId]         
                AND a.[EntityTypeId] = 113 
                AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' 
                AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId]
                AND a.[Key] = 'DayOfTheWeek'
                WHERE [Value] Like '%Sunday%'
                GROUP BY w.[Id], av.[Value]
        )               

        SELECT COUNT(hc.DayCount) AS TotalHour

        FROM
        [Workflow] w
        INNER JOIN [PersonAlias] pa ON pa.Id = w.[InitiatorPersonAliasId]
        INNER JOIN [Person] p ON p.[Id] = pa.[PersonId] 
        INNER JOIN cte_DayCount dc ON DC.Id = w.[Id]
        WHERE w.[WorkflowTypeId] = 1032