locked
Finding the Count of jobs in each workflow state RRS feed

  • Question

  • Hi, I have a data feed which contains Vehicle information and a job workflow state and I am looking to get a live count of vehicles  in each workflow state to feed to power BI.

    Data comes in each time a vehicle changes its workflow state  e.g.   

    "Vehicle 1",  "To Job"

    "Vehicle 2",  "At Job"

    "Vehicle 3", "Unloading"

    "Vehicle 1",  "At Job"

    and The output i'm looking for is is the number of Vehicles in each State
    i.e from the above the output would be (after the last record)
    To Job = 0
    At Job  = 2
    Unloading = 1

    It may be that I need a separate output for each state ?

    Any assistance would be greatly appreciated

    Thanks

    Saturday, April 8, 2017 8:26 PM

All replies

  • It’s very similar to this blog: https://blogs.msdn.microsoft.com/streamanalytics/2015/06/05/counting-live-things/

     

    Assuming schema is (timestamp, vehicle, state), the query will be:

     

    WITH ActiveSessions AS

    (

    SELECT

        vehicle,

        TopOne() OVER(ORDER BY timestamp DESC) AS latestEvent

    FROM

        Input TIMESTAMP BY timestamp

    GROUP BY

        HoppingWindow(minute, 600, 5),

        vehicle

    HAVING

        MIN(IsStart) = 1

    )

     

    SELECT

        latestEvent.state,

        COUNT(*)

    FROM

        ActiveSessions

    GROUP BY

        System.Timestamp,

        latestEvent.state


    Monday, April 10, 2017 5:09 PM
  • Thanks, Have made progress with this, the only thing i'm struggling with now is the HAVING statement as i'm trying to do a string comparison    

    i.e. HAVING JobSate='To Job'

    Also, am I right in assuming that I will need a separate query for JobState or is it possible to do all JobStates in one query?

    Thanks again

    Tuesday, April 11, 2017 8:50 AM