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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
30421 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
52326 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

549 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
52326 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

549 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
52326 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  
 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.12 seconds. Powered By: Snitz Forums 2000