CODEWITHSUNDEEP
sqlgoogle-bigquery
Sin
Sin

Reputation: 161

BIGQUERY YTD Sum not calculating properly

I have a query result UNION' d with a calendar table and the results are as below. I want to add a YTD calculation to it as below but the results I am expecting are not correct.

SELECT *,
sum(AMOUNT) over (partition by FISCAL_YEAR, GL_ACCOUNT, WORKCENTRE_CODE, UNIT_OF_MEASURE order by FISCAL_MONTH ) as YTD_AMOUNT
FROM
(SELECT * FROM QUERY 1
UNION with SELECT * FROM QUERY 2)

Result of above query:

Version WCODE ACC UOM FISCAL_MONTH FISCAL_YEAR CALENDAR_DATE CALENDAR_MONTH AMOUNT DATE_YYYYMM YTD_AMOUNT
A 333333 3005 hours 1 2021 31/07/2020 202007 565.95 202007 565.95
A 333333 3005 hours 1 2021 31/07/2020 202007 0 202007 565.95
A 333333 3005 hours 2 2021 31/08/2020 202008 569.95 202008 1135.9
A 333333 3005 hours 2 2021 31/08/2020 202008 0 202008 1135.9
A 333333 3005 hours 3 2021 30/09/2020 202009 1190.7 202009 2326.6
A 333333 3005 hours 3 2021 30/09/2020 202009 0 202009 2326.6
A 333333 3005 hours 4 2021 31/10/2020 202010 664.85 202010 2991.45
A 333333 3005 hours 4 2021 31/10/2020 202010 0 202010 2991.45
A 333333 3005 hours 5 2021 30/11/2020 202011 606.7 202011 3598.15
A 333333 3005 hours 5 2021 30/11/2020 202011 0 202011 3598.15
A 333333 3005 hours 6 2021 31/12/2020 202012 826.7 202012 4424.85
A 333333 3005 hours 6 2021 31/12/2020 202012 0 202012 4424.85
A 333333 3005 hours 7 2021 31/01/2021 202101 871.3 202101 5296.15
A 333333 3005 hours 7 2021 31/01/2021 202101 0 202101 5296.15
A 333333 3005 hours 8 2021 28/02/2021 202102 847.4 202102 6143.55
A 333333 3005 hours 8 2021 28/02/2021 202102 0 202102 6143.55
A 333333 3005 hours 9 2021 31/03/2021 202103 1225.45 202103 7369
A 333333 3005 hours 9 2021 31/03/2021 202103 0 202103 7369
A 333333 3005 hours 10 2021 30/04/2021 202104 798.55 202104 8167.55
A 333333 3005 hours 10 2021 30/04/2021 202104 0 202104 8167.55
A 333333 3005 hours 11 2021 31/05/2021 202105 936.2 202105 9103.75
A 333333 3005 hours 11 2021 31/05/2021 202105 0 202105 9103.75
A 333333 3005 hours 12 2021 30/06/2021 202106 0 202106 9103.75

Expected Result:

Version WCODE ACC UOM FISCAL_MONTH FISCAL_YEAR CALENDAR_DATE CALENDAR_MONTH AMOUNT DATE_YYYYMM YTD_AMOUNT
A 333333 3005 hours 1 2021 31/07/2020 202007 565.95 202007 565.95
A 333333 3005 hours 1 2021 31/07/2020 202007 0 202007 0
A 333333 3005 hours 2 2021 31/08/2020 202008 569.95 202008 1135.9
A 333333 3005 hours 2 2021 31/08/2020 202008 0 202008 0
A 333333 3005 hours 3 2021 30/09/2020 202009 1190.7 202009 2326.6
A 333333 3005 hours 3 2021 30/09/2020 202009 0 202009 0
A 333333 3005 hours 4 2021 31/10/2020 202010 664.85 202010 2991.45
A 333333 3005 hours 4 2021 31/10/2020 202010 0 202010 0
A 333333 3005 hours 5 2021 30/11/2020 202011 606.7 202011 3598.15
A 333333 3005 hours 5 2021 30/11/2020 202011 0 202011 0
A 333333 3005 hours 6 2021 31/12/2020 202012 826.7 202012 4424.85
A 333333 3005 hours 6 2021 31/12/2020 202012 0 202012 0
A 333333 3005 hours 7 2021 31/01/2021 202101 871.3 202101 5296.15
A 333333 3005 hours 7 2021 31/01/2021 202101 0 202101 0
A 333333 3005 hours 8 2021 28/02/2021 202102 847.4 202102 6143.55
A 333333 3005 hours 8 2021 28/02/2021 202102 0 202102 0
A 333333 3005 hours 9 2021 31/03/2021 202103 1225.45 202103 7369
A 333333 3005 hours 9 2021 31/03/2021 202103 0 202103 0
A 333333 3005 hours 10 2021 30/04/2021 202104 798.55 202104 8167.55
A 333333 3005 hours 10 2021 30/04/2021 202104 0 202104 0
A 333333 3005 hours 11 2021 31/05/2021 202105 936.2 202105 9103.75
A 333333 3005 hours 11 2021 31/05/2021 202105 0 202105 0
A 333333 3005 hours 12 2021 ####### **202106 0 202106 9103.75**

Ideally I would only like to add the missing month only i.e in this case '202106' and then YTD Calculated for the 12 months.

The current results in the output layer are aggregating which is incorrect.

Upvotes: 0

Views: 267

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

From what I can tell, you want the YTD amount to be 0 when the amount is 0. Otherwise the calculation is correct. That is conditional logic:

SELECT t.*,
       (CASE WHEN AMOUNT = 0 THEN 0
             ELSE sum(AMOUNT) over (partition by FISCAL_YEAR, GL_ACCOUNT, WORKCENTRE_CODE, UNIT_OF_MEASURE order by FISCAL_MONTH )
        END) as YTD_AMOUNT
FROM (SELECT * FROM QUERY 1
      UNION with SELECT * FROM QUERY 2
     ) t

Upvotes: 1

Related Questions