SQL Server How to multiply two queries

Question

So i have two different queries as follows:

Query1
CPT        Resource     1      2        3      4      5
2017-06-12  RM1        5.00   5.00    4.00   4.00   2.00
2017-06-12  RM2        3.00   6.00    4.00   7.00   4.00
2017-06-12  RM3        3.00   4.00    6.00   8.00   6.00
2017-06-13  RM1        3.00   7.00    5.00   3.00   5.00
2017-06-13  RM2        4.00   5.00    4.00   2.00   4.00
2017-06-13  RM3        2.00   4.00    5.00   2.00   7.00
2017-06-14  RM1        2.00   4.00    6.00   4.00   2.00
2017-06-14  RM2        6.00   5.00    4.00   5.00   2.00
2017-06-14  RM3        5.00   3.00    7.00   4.00   5.00

and

    Query2
    CPT        Resource     1      2        3      4      5
    2017-06-12  RM1        0.00  -2.00    0.00   0.00  -2.00
    2017-06-12  RM2       -3.00  -3.00    0.00   0.00   0.00
    2017-06-12  RM3       -1.00  -3.00    0.00   0.00   0.00
    2017-06-13  RM1        0.00  -1.00    0.00   0.00   0.00
    2017-06-13  RM2        0.00  -1.00   -1.00  -2.00  -2.00
    2017-06-13  RM3       -2.00  -3.00   -1.00   0.00   0.00
    2017-06-14  RM1        0.00   0.00    0.00   0.00   0.00
    2017-06-14  RM2        0.00  -4.00   -3.00  -2.00   0.00
    2017-06-14  RM3        0.00  -3.00   -1.00   0.00  -2.00

With these two queries how would I go about creating a new query that multiplies data from query1 with the corresponding number in query 2 that is in the same position based on that date, resource, and the hour (which are the headings 1, 2, 3, 4, and 5). I also want only positive numbers so the new data should be multiplied by -1.

If I do this by hand the new table should look like this:

Query3
    CPT        Resource     1      2        3      4      5
    2017-06-12  RM1        0.00  10.00    0.00   0.00   4.00
    2017-06-12  RM2        9.00  18.00    0.00   0.00   0.00
    2017-06-12  RM3        3.00  12.00    0.00   0.00   0.00
    2017-06-13  RM1        0.00   7.00    0.00   0.00   0.00
    2017-06-13  RM2        0.00   5.00    4.00   4.00   8.00
    2017-06-13  RM3        4.00  12.00    5.00   0.00   0.00
    2017-06-14  RM1        0.00   0.00    0.00   0.00   0.00
    2017-06-14  RM2        0.00  20.00   12.00  10.00   0.00
    2017-06-14  RM3        0.00   9.00    7.00   0.00   10.00

score 3 · Answer 1 · answered Jul 12 '17 at 15:40

Just join for the key field and use ABS() to return a positive result.

SELECT Q1.CPT, 
       Q1.Resource,
       ABS(Q1.[1] * Q2.[1]) as [1],
       ABS(Q1.[2] * Q2.[2]) as [2],
       ABS(Q1.[3] * Q2.[3]) as [3],
       ABS(Q1.[4] * Q2.[4]) as [4],
       ABS(Q1.[5] * Q2.[5]) as [5]
FROM Query1 Q1
JOIN Query2 Q2
  ON Q1.CPT = Q2.CPT
 AND Q1.Resourece = Q2.Resource

score 1 · Answer 2 · answered Jul 12 '17 at 15:37

1

You need to join on CPT and Resource, returning table1.1 * table2.1 as 1, like so:

SELECT t1.1 * t2.1 as 1 from t1 join t2 on t1.CPT = t2.CPT and t1.Resource = t2.Resource

answered Jul 12 '17 at 15:37

Aidan Connelly

164
3
13

score 1 · Answer 3 · edited Jul 12 '17 at 16:02

1

you can use simple join as below

Select q1.CPT, q1.[Resource]
     , [1] = abs(q1.[1]*q2.[1])
     , [2] = abs(q1.[2]*q2.[2])
     , [3] = abs(q1.[3]*q2.[3])
     , [4] = abs(q1.[4]*q2.[4])
     , [5] = abs(q1.[5]*q2.[5])   
   from Query1 q1
   join Query2 q2
     on q1.CPT = q2.CPT
    and q1.[Resource] = q2.[Resource]

replace query1 and 2 with your sub query if required

edited Jul 12 '17 at 16:02

xQbert

34,733
2
41
62

answered Jul 12 '17 at 15:41

Kannan Kandasamy

13,405
3
25
38

score 1 · Answer 4 · answered Jul 12 '17 at 15:42

Assuming that you have two tables in question as t1 and t2 then your query should be

select 
    t1.CPT,
    t1.resource,
    ABS(t1.[1]*t2.[1]) as [1],
    ABS(t1.[2]*t2.[2]) as [2],
    ABS(t1.[3]*t2.[3]) as [3],
    ABS(t1.[4]*t2.[4]) as [4],
    ABS(t1.[5]*t2.[5]) as [5]
from
    t1 join t2
        on t1.CPT=t2.CPt 
        and t1.resource=t2.resource

In case you don't have tables t1 and t2 and these are your query results; please encapsulate your queries as subquery with alias of t1 and t2

select 
    t1.CPT,
    t1.resource,
    ABS(t1.[1]*t2.[1]) as [1],
    ABS(t1.[2]*t2.[2]) as [2],
    ABS(t1.[3]*t2.[3]) as [3],
    ABS(t1.[4]*t2.[4]) as [4],
    ABS(t1.[5]*t2.[5]) as [5]
from
    --( your query 1) 
     t1 join 
    --( your query 2)
     t2
        on t1.CPT=t2.CPt 
        and t1.resource=t2.resource

score 1 · Answer 5 · answered Jul 12 '17 at 15:43

SELECT a.CPT
    , a.Resource 
    , ABS(a.Col1 * b.Col1) AS 'Col1'
    , ABS(a.Col2 * b.Col2) AS 'Col2'
    , ABS(a.Col3 * b.Col3) AS 'Col3'
    , ABS(a.Col4 * b.Col4) AS 'Col4'
    , ABS(a.Col5 * b.Col5) AS 'Col5'
FROM Query1 AS a
    INNER JOIN Query2 AS b ON (a.CPT = b.CPT AND a.Resource = b.Resource)

score 1 · Answer 6 · edited Jul 12 '17 at 16:00

1

select Query1.CPT
     , Query1.Resource
     , Query1.1 * Query2.1 as 1
     , Query1.2 * Query2.2 as 2
     , Query1.3 * Query2.3 as 3
     , Query1.4 * Query2.4 as 4
     , Query1.5 * Query2.5 as 5 
from Query1 
Join Query2 on Query1.Resource= Query2.Resource

Try like this, I do not have sql server right now to try it myself, you may need to make some changes to the query.

For Negative Values you can use the following query separately or you can embed this into your main query.

update NewTableName field = field * -1 where field < 0

edited Jul 12 '17 at 16:00

xQbert

34,733
2
41
62

answered Jul 12 '17 at 15:54

Techno

142
4
23

note if the number is positive multiplying by negative will do what? – xQbert Jul 12 '17 at 16:00
It checks if the value is less than 0, only then it multiplies with "-1". – Techno Jul 12 '17 at 16:06
ah true. missed that for some reason. – xQbert Jul 12 '17 at 16:18

SQL Server How to multiply two queries

6 Answers6