SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Mutiplication and Sum in T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nil35
Starting Member

USA
20 Posts

Posted - 07/09/2013 :  13:17:36  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/09/2013 :  14:27:53  Show Profile  Reply with Quote
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
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/09/2013 :  15:31:47  Show Profile  Reply with Quote
WORKING GRATE, THANK YOU SO MUCH VISAKH

nil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/10/2013 :  01:22:12  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/10/2013 :  03:22:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/26/2013 :  11:11:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2013 :  11:17:04  Show Profile  Reply with Quote
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
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/26/2013 :  11:30:57  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/26/2013 :  11:47:52  Show Profile  Reply with Quote
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):

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;





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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2013 :  11:50:06  Show Profile  Reply with Quote


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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/26/2013 :  11:57:36  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/26/2013 :  12:18:15  Show Profile  Reply with Quote



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;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2013 :  12:21:45  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/26/2013 :  13:13:41  Show Profile  Reply with Quote
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

Edited by - Nil35 on 07/26/2013 13:23:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000