Count Until Condition in BigQuery-CodePudding

I have a below table, I want to count the number of t_id that happened before the Meeting(task_type="Meeting") and the number of t_id that happened after the meeting(task_type="Meeting"). In the Output I am looking for two column "Before Meeting" and "After Meeting".Before Meeting will count t_id before "Meeting" and "After Meeting" will count t_id After MEeting that will be reset for each o_id.

In the Example below for O_id="1011" task happened before Meeting was 5 and after meeting was 2 for O_id="1022" task happened before Meeting was 2 and after meeting was 3.

I need the Logic in Big Query

CodePudding user response：

You can try this (replacing TABLE for your table name):

WITH meeting_t_dates as (
  SELECT 
    o_id,
    t_date
  FROM TABLE

  WHERE task_type = 'Meeting'
)


SELECT 
  TABLE.t_id,
  TABLE.o_id,
  TABLE.task_type,
  TABLE.t_date,
  TABLE.c_date,
  TABLE.cr_date,
  TABLE.Sales,
  CASE
    WHEN meeting_t_dates.t_date <  TABLE.t_date THEN 1
    ELSE 0
  END AS 'Before Meeting',
  CASE
    WHEN meeting_t_dates.t_date >  TABLE.t_date THEN 1
    ELSE 0
  END AS 'After Meeting'

FROM
 TABLE
LEFT JOIN meeting_t_dates
ON meeting_t_dates.o_id = TABLE.o_id