O. Foss O. Foss - 6 months ago 16
SQL Question

Sum of CASE columns

I'm creating a view with two CASE columns.
In the last column I need to get the sum of these case fields.
Code below

CREATE VIEW full_return_data
AS
SELECT
return.ID_issuance,
CASE WHEN DATEDIFF(d, date_of_return, fact_date_of_return) <= 0 THEN 0
WHEN (fact_date_of_return is NULL) THEN book_local.price
ELSE DATEDIFF(d,date_of_return, fact_date_of_return) * 30
END AS fine1,
CASE WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 2 THEN book_local.price*0.2
WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 3 THEN book_local.price*0.4
WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 4 THEN book_local.price*0.6
WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 5 THEN book_local.price*0.8
WHEN (fact_date_of_return is NULL) THEN 0
END AS fine2,
(fine1 + fine2) AS fine

FROM book_global INNER JOIN
book_local ON book_local.ID_book_global = book_global.ID_book_global INNER JOIN
book_exemplar ON book_exemplar.ID_book_local = book_local.ID_book_local INNER JOIN
book_exemplar_condition ON book_exemplar_condition.ID_book_exemplar = book_exemplar.ID_book_exemplar INNER JOIN
condition ON condition.ID_condition = book_exemplar_condition.ID_condition INNER JOIN
issuance ON issuance.ID_book_exemplar = book_exemplar.ID_book_exemplar INNER JOIN
[return] ON [return].ID_issuance = issuance.ID_issuance


So... (fine1 + fine2) AS fine does'n work. How can I tackle this problem?

Answer

One easy way is to make the other projections part of a subquery

select a.ID_issuance,a.fine1,a.fine2,(a.fine1+a.fine2) from 
(SELECT  
    return.ID_issuance,
    CASE WHEN DATEDIFF(d, date_of_return, fact_date_of_return) <= 0 THEN 0
         WHEN (fact_date_of_return is NULL) THEN book_local.price
         ELSE DATEDIFF(d,date_of_return, fact_date_of_return) * 30
    END AS fine1,
    CASE WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 2 THEN book_local.price*0.2
         WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 3 THEN book_local.price*0.4
         WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 4 THEN book_local.price*0.6
         WHEN [return].ID_condition > book_exemplar_condition.ID_condition AND [return].ID_condition = 5 THEN book_local.price*0.8
         WHEN (fact_date_of_return is NULL) THEN 0
    END AS fine2

FROM book_global INNER JOIN
book_local ON book_local.ID_book_global = book_global.ID_book_global INNER JOIN
book_exemplar ON book_exemplar.ID_book_local = book_local.ID_book_local INNER JOIN
book_exemplar_condition ON book_exemplar_condition.ID_book_exemplar = book_exemplar.ID_book_exemplar INNER JOIN
condition ON condition.ID_condition = book_exemplar_condition.ID_condition INNER JOIN
issuance ON issuance.ID_book_exemplar = book_exemplar.ID_book_exemplar INNER JOIN
[return] ON [return].ID_issuance = issuance.ID_issuance) a

The other way is to actually sum both of your case statement but it can get ugly

Comments