CODEWITHSUNDEEP
sql-serverdatabasedivisionselect-query
Ray
Ray

Reputation: 41

SQL Server how to sum data of each row

So I have two queries that look like this:

Query1

CPT       Resource    1       2        3       4      5
2017-06-11  RM1     0.000   28.000  28.000  28.000  28.000
2017-06-11  RM2     14.000  23.000  28.000  28.000  0.000
2017-06-11  RM3     0.000   0.000   27.000  27.000  0.000
2017-06-12  RM1     12.000  34.000  0.000   0.000   28.000
2017-06-12  RM2     0.000   0.000   0.000   0.000   28.000
2017-06-12  RM3     17.000  0.000   12.000  0.000   0.000

Query 2
CPT       Resource    1       2        3       4      5
2017-06-11  RM1    -23.000  34.000  -22.000 -28.000  7.000
2017-06-11  RM2    24.000  -15.000   30.000 -18.000 -19.000
2017-06-11  RM3     0.000   0.000   -27.000  27.000  0.000
2017-06-12  RM1     12.000  34.000    0.000  13.000  28.000
2017-06-12  RM2     0.000   0.000   -24.000   0.000  28.000
2017-06-12  RM3   -27.000   16.000   19.000 -18.000  -21.000

How do I code a new select query that will return the date, resource, and the total for each row in the first query. Also, I would like it to return the total for each row in the second query as well but I only want it to add up numbers that are negative and then display them as a positive number (multiply the total by -1 or use abs). After this how would you code so that the total for each dated resource in the first query is divided by the matching total for the same in the second query. For example, dividing the total for RM1 on 2017-06-11 in the first query by the total for the same resource and date in the second query and doing this for every row. And how do I make it so that the new column in the new query displaying the divided totals is called "Daily Cost"

Here is an example of what it would look like if I did it manually for the first two rows:

Query 3
CPT     Resource        Daily Cost 
2017-06-11  RM1            1.53
2017-06-11  RM2            1.78

The math in the first row is done by adding 28+28+28+28=112 for the first row in the first query. Then in the second the query the first row is added (-23)+(-22)+(-28)=-73 and then taking the abs value to get 73. Then 112/73 to get 1.53 as the value for the first row in the third query. How do I code this so that sql does it for me for every row?

Upvotes: 0

Views: 101

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Not a lot of detail to work with here so I am just converting your sample data to a couple of ctes. The real issue here is that your table is not normalized which makes for very lengthy and ugly queries to do this sort of thing.

with Query1 (CPT, Resource, Value1, Value2, Value3, Value4, Value5)as
(
    select '2017-06-11', 'RM1', 0.000, 28.000, 28.000, 28.000, 28.000 union all
    select '2017-06-11', 'RM2', 14.000, 23.000, 28.000, 28.000, 0.000 union all
    select '2017-06-11', 'RM3', 0.000, 0.000, 27.000, 27.000, 0.000 union all
    select '2017-06-12', 'RM1', 12.000, 34.000, 0.000, 0.000, 28.000 union all
    select '2017-06-12', 'RM2', 0.000, 0.000, 0.000, 0.000, 28.000 union all
    select '2017-06-12', 'RM3', 17.000, 0.000, 12.000, 0.000, 0.000
)
, Query2 (CPT, Resource, Value1, Value2, Value3, Value4, Value5)as
(
    select '2017-06-11', 'RM1', -23.000, 34.000, -22.000, -28.000, 7.000 union all
    select '2017-06-11', 'RM2', 24.000, -15.000, 30.000, -18.000, -19.000 union all
    select '2017-06-11', 'RM3', 0.000, 0.000, -27.000, 27.000, 0.000 union all
    select '2017-06-12', 'RM1', 12.000, 34.000, 0.000, 13.000, 28.000 union all
    select '2017-06-12', 'RM2', 0.000, 0.000, -24.000, 0.000, 28.000 union all
    select '2017-06-12', 'RM3', -27.000, 16.000, 19.000, -18.000, -21.000
)
select q1.CPT
    , q1.Resource
    , (q1.Value1 + q1.Value2 + q1.Value3 + q1.Value4 + q1.Value5) 
    / nullif((case when q2.Value1 < 0 then ABS(q2.Value1) else 0 end 
        + case when q2.Value2 < 0 then ABS(q2.Value2) else 0 end 
        + case when q2.Value3 < 0 then ABS(q2.Value3) else 0 end
        + case when q2.Value4 < 0 then ABS(q2.Value4) else 0 end
        + case when q2.Value5 < 0 then ABS(q2.Value5) else 0 end), 0)
from Query1 q1
join Query2 q2 on q1.Resource = q2.Resource 
            and q1.CPT = q2.CPT

Upvotes: 1

Related Questions