SQL calculation - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL calculation

Author

Topic

Nicolas010
Starting Member

2 Posts

Posted - 2011-02-18 : 11:02:20

Hi all, im new here and im working with asp .net and SQL SERVER 2005. The web page use data from the SQL SERVER and what i need to do i some kind of calculator to get the salary of the employees according to the hours he worked and with the type of product he worked on.
The first table is Producto
IdProducto Producto
1 SVA1
2 SVA2
3 UC1
4 UC2
5 CC1
6 CC2
7 CC3
8 CC4
9 CC5
10 CC6
11 OT1
12 OT2
13 OT3
14 OT4
15 OT5
16 OT6

Second Table is Categoria
Idcategoria IdProducto Nombre Value
1 1 qwe 0.20
2 1 BD 0.20
3 1 TMP 0.20
4 1 BAS 0.10
5 1 HW / SW 0.30
6 2 J2EE 0.35
7 2 Web 0.30
8 2 CCXML E 0.15
9 2 BD 0.20
10 3 Prologic 0.10
11 3 vvv 0.60
12 3 Voice 0.15
13 3 IP 0.15
14 4 Meridian 0.40
15 4 BCM 0.30
16 4 Voice 0.30
17 5 CMS 0.30
18 5 AES 0.30
19 5 IVR 0.20
20 5 AIC 0.10
21 5 Office 0.10
22 6 SECC 0.20
23 6 TAPI 0.40
24 6 CC 0.40
25 7 PA 1.00
26 8 PAB 1.00
27 9 Definir 1.00
28 10 Verint 1.00
29 11 Mosaix 1.00
30 12 VP 1.00
31 13 Dato 1.00
32 14 Elearn 1.00
33 15 Dato 1.00
34 16 PA 1.00

Third Table is Conocimiento
Idemployee IdProducto IdCategoria Value Expertise Total Value
12.697 1 1 0.2 0 0.00
12.697 1 2 0.2 2 0.40
12.697 1 3 0.2 0 0.00
12.697 1 4 0.1 0 0.00
12.697 1 5 0.3 0 0.00
12.697 2 6 0.35 1 0.35
12.697 2 7 0.3 0 0.00
12.697 2 8 0.15 0 0.00
12.697 2 9 0.2 2 0.40
12.697 3 10 0.1 0 0.00
12.697 3 11 0.6 0 0.00
12.697 3 12 0.15 0 0.00
12.697 3 13 0.15 0 0.00
12.697 4 14 0.4 0 0.00
12.697 4 15 0.3 0 0.00
12.697 4 16 0.3 0 0.00
12.697 5 17 0.3 0 0.00
12.697 5 18 0.3 0 0.00
12.697 5 19 0.2 0 0.00
12.697 5 20 0.1 0 0.00
12.697 5 21 0.1 0 0.00
12.697 6 22 0.2 0 0.00
12.697 6 23 0.4 0 0.00
12.697 6 24 0.4 0 0.00
12.697 7 25 1 0 0.00
12.697 8 26 1 0 0.00
12.697 9 27 1 0 0.00
12.697 10 28 1 0 0.00
12.697 11 29 1 0 0.00
12.697 12 30 1 0 0.00
12.697 13 31 1 0 0.00
12.697 14 32 1 0 0.00
12.697 15 33 1 0 0.00
12.697 16 34 1 0 0.00

As you can see, there is a "Total Value" in the third table, and what i have to do is "sum this value where IdProducto = '1'. So i sum the example on the table, the sum for IdProduct = 1 will be: 0.40. And with this value i can determine his payment. Please i need this and it has come very complicated to me. Any dudes please post. I have something like this so far.

UPDATE Ax50_HorasRegistradas
SET ValorRegla = ( SELECT
					CASE 
						WHEN Ax50_HorasRegistradas.Producto= '1' 
and Ax50_HorasRegistradas.Rut = '12.697' 
							THEN SUM(sxb_Conocimiento.Resultado)															 
						END AS SVA1
					FROM Ax50_HorasRegistradas AS Ax50_HorasRegistradas
					JOIN sxb_Conocimiento AS sxb_Conocimiento
					ON Ax50_HorasRegistradas.Rut = sxb_conocimiento.rut 
					WHERE Ax50_HorasRegistradas.Rut = sxb_conocimiento.rut
					GROUP BY Ax50_HorasRegistradas.LINEPROPERTYID, Ax50_HorasRegistradas.Rut)

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 12:00:28

Not sure I understand exactly what you need but in order to get the total amount per employee and product you just need to sum in your third table:


SELECT Idemployee, iDProducto, SUM([Total Value]) as TotalValue
FROM Conocimiento
GROUP BY Idemployee, iDProducto

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-18 : 14:34:42

well using the above structure can you name the columns as highlighted in your following statement?

i need to do i some kind of calculator to get the salary of the employees according to the hours he worked and with the type of product he worked on.

Even better if you come up with only two or three rows for each of the above table and the desired output format you are looking for..

Cheers!
MIK

Nicolas010
Starting Member

2 Posts

Posted - 2011-02-18 : 15:18:02

quote:
Originally posted by KlausEngel

Not sure I understand exactly what you need but in order to get the total amount per employee and product you just need to sum in your third table:

SELECT Idemployee, iDProducto, SUM([Total Value]) as TotalValue
FROM Conocimiento
GROUP BY Idemployee, iDProducto

Hi and thanks fow answering. Your sql statement is good and i came to something like these...


UPDATE Ax50_HorasRegistradas 
SET Expertise =
(SELECT sxb_Conocimiento.Rut, sxb_conocimiento.IdProducto, Sum(sxb_Conocimiento.Resultado)
FROM sxb_Conocimiento as sxb_Conocimiento
JOIN Ax50_HorasRegistradas as Ax50_HorasRegistradas
ON sxb_Conocimiento.Rut = Ax50_HorasRegistradas.Rut
WHERE 
	sxb_Conocimiento.Rut IS NOT NULL AND
	Ax50_HorasRegistradas.rut = sxb_Conocimiento.Rut AND
    sxb_Conocimiento.IdProducto = 6
GROUP BY sxb_Conocimiento.RUT, sxb_Conocimiento.IdProducto)
FROM sxb_Conocimiento as sxb_Conocimiento
JOIN Ax50_HorasRegistradas as Ax50_HorasRegistradas
ON sxb_Conocimiento.Rut = Ax50_HorasRegistradas.Rut
WHERE Ax50_HorasRegistradas.rut = sxb_Conocimiento.Rut

but i get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-20 : 14:02:21

expertise = expects one value from the subquery ... where your sub query returns more than one .. sxb_conocimiento.Rut(first one), sxb_conocimiento..idProducto (second one) and sum(sxb)conocimiento.Resultado Third One

Its similar like you are trying to assign multiple values at a time to a Variable e.g. @x = 1 and 2 and 3 ... and N

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources