Stack Exchange
How to make multiple conditions that may overlap in CASE statement and using GROUP BY
I have a query in BigQuery:
WITH
  date_params AS(
  SELECT
    DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -7 day), WEEK(MONDAY)) AS WeekStartDate,
    DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -1 day), WEEK(SUNDAY)) AS WeekEndDate,
    date "2020-01-01" AS ReportStartDate,
    ),
  finances AS (
  SELECT
    Amount,
    CASE
      WHEN DATE (t1.Created ) BETWEEN WeekStartDate AND WeekEndDate THEN "Weelky"
      WHEN DATE (t1.Created ) BETWEEN ReportStartDate
    AND WeekEndDate THEN "Cumulative"
  END
    AS ReportPeriod
  FROM
    Table_1 t1,
    date_params p )
SELECT
  SUM(Amount) AS `Amount`
FROM
  finances
GROUP BY
  ReportPeriod

Could you please explain how to group result Amount between two time periods: 1) last week and 2) from certain date (ReportStartDate) as I tried to do. The only problem appeared that CASE statement for "Cumulative" includes dates within "Weelky" but I need from the ReportStartDate till last Sunday including last week. 

Please format your code properly!

CASE will only ever return one value for each row, that is why the rows included in Weelky are not being included in Cumulative. There are multiple approaches to make a single row be available both reports. The one I could think that better preserves the structure of your existing query is the following, which constructs an array in order to be able to tag a single row with multiple reports and then unnests the array. I hope this works for you!
WITH
  date_params AS(
  SELECT
    DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -7 day), WEEK(MONDAY)) AS WeekStartDate,
    DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -1 day), WEEK(SUNDAY)) AS WeekEndDate,
    date "2020-01-01" AS ReportStartDate,
    ),
  finances AS (
  SELECT
    Amount,
    ARRAY_CONCAT(
      IF (DATE (t1.Created) BETWEEN WeekStartDate AND WeekEndDate,["Weelky"],[]),
      IF (DATE (t1.Created) BETWEEN ReportStartDate AND WeekEndDate,["Cumulative"],[])
    ) as ReportPeriod
  FROM
    Table_1 t1,
    date_params p )
SELECT
  SUM(Amount) AS `Amount`
FROM
  finances
  CROSS JOIN unnest(ReportPeriod) as ReportPeriod
GROUP BY
  ReportPeriod


Thanks, this can be an option

You can start your "Cumulative" from ReportStartDate and end before WeekStartDate.
CASE 
    WHEN DATE (t1.Created ) BETWEEN WeekStartDate
           AND WeekEndDate then "Weelky"
    WHEN DATE (t1.Created ) BETWEEN ReportStartDate
           AND WeekStartDate-1 then "Cumulative"
    END 
    AS ReportPeriod


I want this last week to be included as well

If I am correct then WeekStartDate and WeekEndDate is for current week. If you give condition "BETWEEN ReportStartDate AND WeekStartDate-1" then you will get data from specified date till start of this week. This is including last week also.                    If not then let me know what do you mean by last week?

OK, sorry for confusing explanation. Let's say I would like to have from ReportStartDate till today. If I put DATE (t1.Created ) BETWEEN ReportStartDate  AND  CURRENT_TIMESTAMP() Then "Cumulative" it should work? I think it excludes data from previous "Weekly" condition

No, I wanted "weekly" to be included in Cumulative
