SQL Server How to sum all fields in a row for each row of two queries and then divide them

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”

  • Error with SqlBulkCopyColumnMapping
  • sql group by versus distinct
  • Hibernate SQL In clause making CPU usage to 100%
  • What is the best practice for inserting a record if it doesn't already exist?
  • How to prevent out-of-range datetime value error?
  • Reason for - ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  • 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?

    By the way each table has over 100 dates in it, I just only posted 3 to save time and space. so doing something like this wont work.

        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
    

    Im wondering if there is a more efficient way of doing this.

  • How to do a Postgresql subquery in select clause with join in from clause like SQL Server?
  • SQL row return order
  • SqlServer to Postgres DDL migration/conversion
  • SQL - Turn relationship IDs into a delimited list
  • database record locking
  • Microsoft Hex dates
  • 2 Solutions collect form web for “SQL Server How to sum all fields in a row for each row of two queries and then divide them”

    I got what you are looking for using common table expressions. You should be able to copy the script below and plug in your existing queries. You need to decide what you want to do when none of the values in query 2 are negative, because you can’t divide by 0. I am checking for it and if the value is 0, I plug in the total from query 1. This will make the Daily Cost always 1. I’m not sure if that is what you want, but if it isn’t; it will need to be modified.

    WITH cte1 AS
    (
        --Put Query 1 here
    ),
    
    cte2 AS
    (
        --Put Query 2 here
    ),
    
    --Total up columns 1-5 from Query 1
    cte3 AS 
    (
    SELECT CPT,[Resource],(SUM([1])+SUM([2])+SUM([3])+SUM([4])+SUM([5])) AS 'Total'
    FROM cte1
    GROUP BY CPT,[Resource]
    ),
    
    --Total negative numbers from columns 1-5
    --and take the absolute value
    cte4 AS
    (
    SELECT CPT,[Resource],(
                ABS(SUM(CASE WHEN [1] < 0 THEN [1] ELSE 0 END)+
                    SUM(CASE WHEN [2] < 0 THEN [2] ELSE 0 END)+
                    SUM(CASE WHEN [3] < 0 THEN [3] ELSE 0 END)+
                    SUM(CASE WHEN [4] < 0 THEN [4] ELSE 0 END)+
                    SUM(CASE WHEN [5] < 0 THEN [5] ELSE 0 END))
                    ) AS 'Total'
    FROM cte2
    GROUP BY CPT,[Resource]
    )
    
    --Join cte3 and cte4. Then divide the total from
    --cte3 by cte4
    --IMPORTANT: You must take account for the divide by zero rule
    --I am checking to see if cte3 and cte4 is 0 and if it is, I'm
    --changing them to 1. It should never reach that point though
    --because of the where clause omitting them.
    SELECT cte3.CPT,cte3.[Resource],CAST(ROUND(CASE WHEN cte3.Total = 0 
                                    THEN 1 ELSE cte3.Total END/
                                    CASE WHEN cte4.Total = 0 
                                    THEN 1 ELSE cte4.Total END,2) 
                                    AS DECIMAL (5,2))  'Daily Cost'
    FROM cte3
         JOIN cte4 ON cte3.CPT = cte4.CPT AND
                      cte3.[Resource] = cte4.[Resource]
    WHERE cte3.Total <> 0 AND cte4.Total <> 0
    ORDER BY CPT,[Resource]
    

    Sample Data Results

    +-----------+----------+------------+
    |   CPT     | Resource | Daily Cost |
    +-----------+----------+------------+
    |2017-06-11 |   RM1    |   1.53     |
    |2017-06-11 |   RM2    |   1.79     |
    |2017-06-11 |   RM3    |   2.00     |
    |2017-06-12 |   RM1    |   1.00     | --Row that had no negative numbers in Query 2
    |2017-06-12 |   RM2    |   1.17     |
    |2017-06-12 |   RM3    |   0.44     |
    +-----------+----------+------------+
    

    Well you called it a query, so i assume the two queries are actually result sets. In that case, or the case that they are actual tables which conform to the example, you seemingly only have a single row for each resource per day. In that case, you can do something as such, though I got a different result for your second resource:

    declare @query1 table (CPT date, 
                           [Resource] varchar(3), 
                           [1] decimal (5,2), 
                           [2] decimal (5,2), 
                           [3] decimal (5,2), 
                           [4] decimal (5,2), 
                           [5] decimal (5,2))
    insert into @query1
    values
    ('20170611','RM1',0.0,28.0,28.0,28.0,28.0),
    ('20170611','RM2',14.0,23.0,28.0,28.0,0.0)
    
    declare @query2 table (CPT date, 
                           [Resource] varchar(3), 
                           [1] decimal (5,2), 
                           [2] decimal (5,2), 
                           [3] decimal (5,2), 
                           [4] decimal (5,2), 
                           [5] decimal (5,2))
    insert into @query2
    values
    ('20170611','RM1',-23.0,34.0,-22.0,-28.0,7.0),
    ('20170611','RM2',24.0,-15.0,30.0,-18.0,-19.0)
    
    select
        q1.CPT
        ,q1.Resource
        ,(q1.[1] + q1.[2] + q1.[3] + q1.[4] + q1.[4])
         /
         abs((case when q2.[1] < 0 then q2.[1] else 0 end + 
              case when q2.[2] < 0 then q2.[2] else 0 end + 
              case when q2.[3] < 0 then q2.[3]  else 0 end + 
              case when q2.[4] < 0 then q2.[4] else 0 end + 
              case when q2.[5] < 0 then q2.[5] else 0 end))
    from 
        @query1 q1
        inner join
            @query2 q2 on
            q1.CPT = q2.CPT 
            and q1.Resource = q2.Resource
    

    If these are actual results from a query, then just place them as a subquery…

    select
        q1.CPT
        ,q1.Resource
        ,(q1.[1] + q1.[2] + q1.[3] + q1.[4] + q1.[4])
            /
            abs((case when q2.[1] < 0 then q2.[1] else 0 end + 
                case when q2.[2] < 0 then q2.[2] else 0 end + 
                case when q2.[3] < 0 then q2.[3]  else 0 end + 
                case when q2.[4] < 0 then q2.[4] else 0 end + 
                case when q2.[5] < 0 then q2.[5] else 0 end))
    from 
        (select ... from ... where ..) q1
        inner join
            (select ... from ... where ...)  q2 on
            q1.CPT = q2.CPT 
            and q1.Resource = q2.Resource
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.