Mutiplication and Sum in T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mutiplication and Sum in T-SQL

Author

Topic

Nil35
Starting Member

20 Posts

Posted - 2013-07-09 : 13:17:36

ID A B C
1 2 3 NULL /0
2 4 1 (6)
3 5 2 (2)+(12)=14
4 1 3 (A1*B3)+(A2*B2)+(A3*B1)
5 3 1 (A1*B4)+(A2*B3)+(A3*B2)+(A4*B1)
6 2 5 (A1*B5)+(A2*B4)+(A3*B3)+(A4*B2)+(A5*B1)

Using SQL Server 2008R2
Please help me to find out logic for above scenario,
need to find out coloum C,
data types of column A and B is Numeric

Thank You in Advance
nil

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 14:27:53

see this illustration


declare @t table
(
ID int,
 A int,
  B int,
   C int
   )
   
   
insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5)


select  t.ID,t.A,t.B,SUM(t1.A * t2.B) AS C
from @t t
outer apply (select ROW_NUMBER() over (order by ID ASC) AS Seq,
              A
                   FROM @T
                   WHERE ID < t.ID
                     
            )t1
  outer apply (select ROW_NUMBER() over (order by ID DESC) AS Seq,
              B
                   FROM @T
                   WHERE ID < t.ID
                    
            )t2
 WHERE COALESCE(t1.Seq,0) = COALESCE(t2.Seq,0)  
 GROUP BY t.ID,t.A,t.B                 
  ORDER BY ID     

output
---------------------------------------------
ID	A	B	C
---------------------------------------------
1	2	3	NULL
2	4	1	6
3	5	2	14
4	1	3	23
5	3	1	22
6	2	5	34

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nil35
Starting Member

20 Posts

Posted - 2013-07-09 : 15:31:47

WORKING GRATE, THANK YOU SO MUCH VISAKH

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 01:22:12

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-10 : 03:22:46

[code]SELECT a.ID,
a.A,
a.B,
w.C
FROM @t AS a
OUTER APPLY (
SELECT SUM(b.A * c.B)
FROM @t AS b
INNER JOIN @t AS c ON c.ID + b.ID = a.ID
) AS w(C)
ORDER BY a.ID;[/code]

N 56°04'39.26"
E 12°55'05.63"

Nil35
Starting Member

20 Posts

Posted - 2013-07-26 : 11:11:07

Hey Guys, Thank You so Much
Above codes are perfectly working
But now I want to increase number of ID virtually, right now we have 6 ID in our table @t, but I want to increase it up to 10 and values for column A and B after 6th row should be 0.

declare @t table
(
ID int,
A int,
B int,
C int
)
insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5)

So table will look like
-----------------------------------------
ID A B C
-----------------------------------------
1 2 3 NULL
2 4 1 6
3 5 2 14
4 1 3 23
5 3 1 22
6 2 5 34
7 0 0 (a1 * b6)+ (a2 * b5)+ (a3 * b4)+ (a4 * b3)+ (a5 * b2)+ (a6 * b1)
8 0 0 (a1 * b7)+ (a2 * b6)+ (a3 * b5)+ (a4 * b4)+ (a5 * b3)+ (a6 * b2)+(a7+a1)
9 0 0 (a1 * b8)+ (a2 * b7)+ (a3 * b6)+ (a4 * b5)+ (a5 * b4)+ (a6 * b3)+(a7+a2) +(a8+a1)
10 0 0 (a1 * b9)+ (a2 * b8)+ (a3 * b7)+ (a4 * b6)+ (a5 * b5)+ (a6 * b4)+(a7+a3) +(a8+a2) +(a9+a1)

Thank You

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 11:17:04

can you explain how you'll get value as 0. I cant see any 0's for any of the other columns as per your calculation logic.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nil35
Starting Member

20 Posts

Posted - 2013-07-26 : 11:30:57

source table have only 6 rows but when we increase ID to 7,8,9,10 (virtually)that time we need to take column A = 0 and column B=0

nil

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-26 : 11:47:52

Is the red text below typo or do you want addition instead of multiplication in those cases?

Assuming typos: Is this what you are looking for (altering SwePeso's code):
[CODE]
SELECT a.ID,
a.A,
a.B,
w.C
FROM (SELECT * FROM @t
UNION
SELECT 7, 0, 0, 0
UNION
SELECT 8, 0, 0, 0
UNION
SELECT 9, 0, 0, 0
UNION
SELECT 10, 0, 0, 0
) AS a
OUTER APPLY (
SELECT SUM(b.A * c.B)
FROM @t AS b
INNER JOIN @t AS c ON c.ID + b.ID = a.ID
) AS w(C)
ORDER BY a.ID;

[/CODE]

quote:
Originally posted by Nil35

Hey Guys, Thank You so Much
Above codes are perfectly working
But now I want to increase number of ID virtually, right now we have 6 ID in our table @t, but I want to increase it up to 10 and values for column A and B after 6th row should be 0.

declare @t table
(
ID int,
A int,
B int,
C int
)
insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5)

So table will look like
-----------------------------------------
ID A B C
-----------------------------------------
1 2 3 NULL
2 4 1 6
3 5 2 14
4 1 3 23
5 3 1 22
6 2 5 34
7 0 0 (a1 * b6)+ (a2 * b5)+ (a3 * b4)+ (a4 * b3)+ (a5 * b2)+ (a6 * b1)
8 0 0 (a1 * b7)+ (a2 * b6)+ (a3 * b5)+ (a4 * b4)+ (a5 * b3)+ (a6 * b2)+(a7+a1)
9 0 0 (a1 * b8)+ (a2 * b7)+ (a3 * b6)+ (a4 * b5)+ (a5 * b4)+ (a6 * b3)+(a7+a2) +(a8+a1)
10 0 0 (a1 * b9)+ (a2 * b8)+ (a3 * b7)+ (a4 * b6)+ (a5 * b5)+ (a6 * b4)+(a7+a3) +(a8+a2) +(a9+a1)

Thank You

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 11:50:06

[code]

declare @t table
(
ID int,
A int,
B int,
C int
)

insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5),
(7, 2, 8),
(9, 2, 6)

select t.ID,CASE WHEN ID < = 6 THEN t.A ELSE 0 END AS A,CASE WHEN ID < = 6 THEN t.B ELSE 0 END AS B,
CASE WHEN ID < = 6 THEN SUM(t1.A * t2.B) ELSE 0 END AS C
from @t t
outer apply (select ROW_NUMBER() over (order by ID ASC) AS Seq,
A
FROM @T
WHERE ID < t.ID

)t1
outer apply (select ROW_NUMBER() over (order by ID DESC) AS Seq,
B
FROM @T
WHERE ID < t.ID

)t2
WHERE COALESCE(t1.Seq,0) = COALESCE(t2.Seq,0)
GROUP BY t.ID,t.A,t.B
ORDER BY ID

output
------------------------------
ID A B C
------------------------------
1 2 3 NULL
2 4 1 6
3 5 2 14
4 1 3 23
5 3 1 22
6 2 5 34
7 0 0 0
9 0 0 0

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nil35
Starting Member

20 Posts

Posted - 2013-07-26 : 11:57:36

I want something that after ID 6 code should generate ID automatically up to 10 and should take value for column A = 0 and B = 0 (automatically)

nil

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-26 : 12:18:15

[CODE]

declare @t table
(
ID int,
A int,
B int,
C int
)

insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5);

(using SwePeso's code)

; WITH CTE AS
(SELECT MAX(ID) as MX FROM @t),
CTE2 AS
(SELECT number as ID, 0 as A, 0 as B, 0 as C from spt_values, CTE T where type = 'p' and number between MX+1 and 10
UNION
SELECT ID, A, B, C from @t)
SELECT a.ID,
a.A,
a.B,
w.C
FROM CTE2 a OUTER APPLY (
SELECT SUM(b.A * c.B)
FROM CTE2 AS b
INNER JOIN @t AS c ON c.ID + b.ID = a.ID
) AS w(C)
ORDER BY a.ID;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 12:21:45

[code]
declare @t table
(
ID int,
A int,
B int,
C int
)

insert @t(ID,A,B)
values
(1, 2, 3),
(2, 4, 1),
(3, 5, 2),
(4, 1, 3),
(5, 3, 1),
(6, 2, 5)

;With CTE
AS
(
SELECT MAX(ID) +1 AS ID,0 AS A, 0 AS B, 0 AS C
FROM @t
UNION ALL
SELECT ID+1,A,B,C
FROM CTE
WHERE ID + 1 <=10
)

select t.ID,t.A,t.B,
SUM(t1.A * t2.B) AS C
from @t t
outer apply (select ROW_NUMBER() over (order by ID ASC) AS Seq,
A
FROM @T
WHERE ID < t.ID

)t1
outer apply (select ROW_NUMBER() over (order by ID DESC) AS Seq,
B
FROM @T
WHERE ID < t.ID

)t2
WHERE COALESCE(t1.Seq,0) = COALESCE(t2.Seq,0)
GROUP BY t.ID,t.A,t.B
UNION ALL
SELECT *
FROM CTE
ORDER BY ID

output
-------------------------------------------
ID A B C
-------------------------------------------
1 2 3 NULL
2 4 1 6
3 5 2 14
4 1 3 23
5 3 1 22
6 2 5 34
7 0 0 0
8 0 0 0
9 0 0 0
10 0 0 0

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nil35
Starting Member

20 Posts

Posted - 2013-07-26 : 13:13:41

Grate Vishakh and MuMu88, Thank You so Much

This is what I need
I just added one Union all in your code
Example
;With CTE
AS
(
SELECT MAX(ID) +1 AS ID,0 AS A, 0 AS B
FROM @t
UNION ALL
SELECT ID+1,A,B
FROM CTE
WHERE ID + 1 <=10
)
,ctea as
(
select ID,A,B from @t
union all
select ID,A,B from cte
)

nil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources