CODEWITHSUNDEEP
sql-serverdatabasemultiplicationselect-query
Ray
Ray

Reputation: 41

SQL Server How to multiply two queries

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

Upvotes: 1

Views: 1579

Answers (6)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

Upvotes: 1

Techno
Techno

Reputation: 180

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

Upvotes: 1

MBurnham
MBurnham

Reputation: 381

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)

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 3

Aidan Connelly
Aidan Connelly

Reputation: 184

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

Upvotes: 1

Related Questions