Sum IF

[Vaku1606]

Hello,

I have following table:

PLANNED_END_DATE	QUANTITY	ITEM	CALCULATED_DELIVERY_DATE
12/12/2022 6:30	550000	483174	12/31/2022 0:00
11/22/2022 10:50	550000	483174	11/30/2022 0:00
10/31/2022 21:35	201000	483174	10/31/2022 0:00
8/25/2022 22:44	95648	483172	8/31/2022 0:00
10/5/2022 17:42	1000000	483172	9/30/2022 0:00
11/17/2022 18:37	1000000	483172	11/30/2022 0:00
10/24/2022 8:12	369600	483207	9/30/2022 0:00
10/25/2022 18:27	169600	483207	10/31/2022 0:00
11/29/2022 13:56	300000	483207	11/30/2022 0:00
12/7/2022 11:19	400000	483207	12/31/2022 0:00
10/30/2022 0:53	260000	483241	10/31/2022 0:00

I would like to have 3 columns in my application according to following rules:

If planned end date > calculated delivery date then Sum the Quantity and show it as  "late orders"

If planned end date = calculated delivery date then Sum the Quantity and show it as  "on time orders"

If planned end date < calculated delivery date then Sum the Quantity and show it as "in advance"

Could you please help?

thanks in advance and regards

[BrunPierre · Accepted solution]

I'd do something like the below

LOAD DATE(FLOOR(PLANNED_END_DATE)) AS PLANNED_END_DATE,
QUANTITY,
ITEM,
DATE(FLOOR(CALCULATED_DELIVERY_DATE)) AS CALCULATED_DELIVERY_DATE,
IF(DATE(FLOOR(PLANNED_END_DATE)) > DATE(FLOOR(CALCULATED_DELIVERY_DATE)),QUANTITY,
IF(DATE(FLOOR(PLANNED_END_DATE)) = DATE(FLOOR(CALCULATED_DELIVERY_DATE)),QUANTITY,
IF(DATE(FLOOR(PLANNED_END_DATE)) < DATE(FLOOR(CALCULATED_DELIVERY_DATE)),QUANTITY))) AS [NEW QUANTITY],
IF(DATE(FLOOR(PLANNED_END_DATE)) > DATE(FLOOR(CALCULATED_DELIVERY_DATE)),'LATE ORDERS',
IF(DATE(FLOOR(PLANNED_END_DATE)) = DATE(FLOOR(CALCULATED_DELIVERY_DATE)),'ON TIME ORDERS',
IF(DATE(FLOOR(PLANNED_END_DATE)) < DATE(FLOOR(CALCULATED_DELIVERY_DATE)),'IN ADVANCE'))) AS [ORDER STAUS]

FROM TABLE;

[Vaku1606]

Works perfectly. Thanks a lot