Split fix value per month to each day and country (and exclude certain values)

Question

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    country VARCHAR(255),
    sales_date DATE,
    sales_volume DECIMAL,
    fix_costs DECIMAL
);

INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 

('DE', '2020-01-03', '500', '2000'),
('NL', '2020-01-03', '320', '2000'),
('FR', '2020-01-03', '350', '2000'),
('None', '2020-01-31', '0', '2000'),

('DE', '2020-02-15', '0', '5000'),
('NL', '2020-02-15', '0', '5000'),
('FR', '2020-02-15', '0', '5000'),
('None', '2020-02-29', '0', '5000'),

('DE', '2020-03-27', '180', '4000'),
('NL', '2020-03-27', '670', '4000'),
('FR', '2020-03-27', '970', '4000'),
('None', '2020-03-31', '0', '4000');

Expected Result:

sales_date   |   country    |   sales_volume   |     fix_costs
-------------|--------------|------------------|------------------------------------------
2020-01-03   |     DE       |       500        |     37.95  (= 2000/31 = 64.5 x 0.59)
2020-01-03   |     FR       |       350        |     26.57  (= 2000/31 = 64.5 x 0.41)
2020-01-03   |     NL       |       320        |      0.00
-------------|--------------|------------------|------------------------------------------
2020-02-15   |     DE       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     FR       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     NL       |         0        |      0.00
-------------|--------------|------------------|------------------------------------------    
2020-03-27   |     DE       |       180        |     20.20  (= 4000/31 = 129.0 x 0.16) 
2020-03-27   |     FR       |       970        |    108.84  (= 4000/31 = 129.0 x 0.84)   
2020-03-27   |     NL       |       670        |      0.00
-------------|--------------|------------------|-------------------------------------------

The column fix_costs in the expected result is calculated as the following:

Step 1) Exclude country NL from the next steps but it should still appear with value 0 in the results

Step 2) Get the daily rate of the fix_costs per month.(2000/31 = 64.5; 5000/29 = 172.4; 4000/31 = 129.0)

Step 3) Split the daily value to the countries DE and FR based on their share in the sales_volume. (500/850 = 0.59; 350/850 = 0.41; 180/1150 = 0.16; 970/1150 = 0.84)

Step 4) In case the sales_volume is 0 the daily rate gets split 50/50 to DE and FR as you can see for 2020-02-15.

In comparison to this quesiton I added the condition that country NL is excluded from the calculation of the daily fix_cost rate but it should still appear with value 0 in the results. Therefore, I tried to go with this query:

SELECT
s.sales_date, 
s.country,
s.sales_volume,
s.fix_costs,

 (CASE WHEN country = 'NL' THEN 0
       WHEN SUM(CASE WHEN country <> 'NL' THEN sales_volume ELSE 0 END) OVER (PARTITION BY sales_date) > 0
       THEN ((s.fix_costs/ EXTRACT(DAY FROM (DATE_TRUNC('MONTH', sales_date + INTERVAL '1 MONTH') - INTERVAL '1 DAY'))) *
              sales_volume / NULLIF(SUM(sales_volume) OVER (PARTITION BY sales_date), 0)
              )
        ELSE (s.fix_costs / EXTRACT(DAY FROM (DATE_TRUNC('MONTH', sales_date + INTERVAL '1 MONTH') - INTERVAL '1 DAY'))) * 1 
              / SUM(CASE WHEN country <> 'NL' THEN 1 ELSE 0 END) OVER (PARTITION by sales_date)
        END) AS imputed_fix_costs
        
FROM sales s
WHERE country NOT IN ('None')
GROUP BY 1,2,3,4
ORDER BY 1;

However, somehow it does not exclude NL from the caluclation.
What do I need to modify to make it work and get the expected result?

I already described the entire algorithm in you first question: https://stackoverflow.com/questions/66331359/how-to-apply-cross-join-in-postgresql (see especially step 1 in explanation) It is absolutely not clear, why you ask the same question again (https://stackoverflow.com/questions/66370116/assign-total-value-of-month-to-each-day-of-month) and again (https://stackoverflow.com/questions/66370116/assign-total-value-of-month-to-each-day-of-month) — S-Man, Feb 26 '21 at 10:03
Does this answer your question? [How to apply CROSS JOIN in PostgreSQL?](https://stackoverflow.com/questions/66331359/how-to-apply-cross-join-in-postgresql) — S-Man, Feb 26 '21 at 10:03

Michi · Accepted Answer · 2021-02-26T13:30:38.497

DB-Fiddle

SELECT
s.sales_date, 
s.country,
s.sales_volume,
s.fix_costs,

 (CASE WHEN country = 'NL' THEN 0
       
       /* Exclude NL from fixed_costs calculation */
       WHEN SUM(CASE WHEN country <> 'NL' THEN sales_volume ELSE 0 END) OVER (PARTITION BY sales_date) > 0
       THEN ((s.fix_costs/ extract(day FROM (date_trunc('month', sales_date + INTERVAL '1 month') - INTERVAL '1 day'))) *
              sales_volume / 
              NULLIF(SUM(s.sales_volume) FILTER (WHERE s.country != 'NL')  OVER (PARTITION BY s.sales_date), 0)
              )
              
        /* Divide fixed_cots equaly among countries in case of no sale*/      
        ELSE (s.fix_costs / extract(day FROM (date_trunc('month', sales_date + INTERVAL '1 month') - INTERVAL '1 day'))) 
              / SUM(CASE WHEN country <> 'NL' THEN 1 ELSE 0 END) OVER (PARTITION by sales_date)
              
        END) AS imputed_fix_costs
        
FROM sales s
WHERE country NOT IN ('None')
GROUP BY 1,2,3,4
ORDER BY 1;

Split fix value per month to each day and country (and exclude certain values)

1 Answers1