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
 Problem with Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Schareina
Starting Member

4 Posts
Posted - 01/18/2013 :  02:22:59  Show Profile  Reply with Quote
Hi everyone

I spend a long time to get this @ work:


I have 2 dynamic tables. Created from sub tables with the same structure.


INPUT TABLES:

Table 1: clerk_admin_hour_access_guaranteed AS clerk_pos
-------------------------------------------------------
#clerk_id	#clerk_name	#hour
-------------------------------------------------------
1		Admin		20
1		Admin		11.4
2		Mauser		66
3		Whyskas		5.3
2		Mauser		0.11

...


Table 2: clerk_admin_hour_access_guaranteed AS clerk_neg
-------------------------------------------------------
#clerk_id	#clerk_name	#hour
-------------------------------------------------------
4		Golf		19.2
2		Mauser		10
1		Admin		17.3
2		Mauser		0.2

...




OUTPUT TABLE:

The output must have list all clerks when there are in both tables. And then calculate sum(hour).

Table 3: clerk_admin_hour_total_access AS clerk_sum
-------------------------------------------------------
#clerk_id	#clerk_name	#SUM_hour
-------------------------------------------------------
1		Admin		48.7
2		Mauser		76.31




I have tried a lot of queries. But in all ways I had the same probelm.

Please help me with the query
--
Schareina
Edited by - Schareina on 01/18/2013 02:35:07

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/18/2013 :  03:16:24  Show Profile  Reply with Quote 

SELECT t1.[#clerk_id],t1.[#clerk_name],t1.[#SUM_hour] + t2.[#SUM_hour] AS [#SUM_hour]
FROM
(SELECT [#clerk_id],[#clerk_name],SUM([#hour]) AS [#SUM_hour]
FROM table1 
GROUP BY [#clerk_id],[#clerk_name])t1
INNER JOIN (SELECT [#clerk_id],[#clerk_name],SUM([#hour]) AS [#SUM_hour]
FROM table2 
GROUP BY [#clerk_id],[#clerk_name])t2
ON t2.[#clerk_id] = t1.[#clerk_id]
AND t2.[#clerk_name] = t1.[#clerk_name]



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

karthik0805
Starting Member

14 Posts
Posted - 01/18/2013 :  04:38:24  Show Profile  Reply with Quote
SELECT A.CLERK_ID,A.CLERK_NAME,SUM(A.SUM_HOUR+B.SUM_HOUR) TOT_HOUR
FROM #CLERK_POS A JOIN #CLERK_NEG B ON A.CLERK_ID=B.CLERK_ID
GROUP BY A.CLERK_ID,A.CLERK_NAME
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1503 Posts
Posted - 01/18/2013 :  04:50:39  Show Profile  Reply with Quote
quote:
Originally posted by karthik0805

SELECT A.CLERK_ID,A.CLERK_NAME,SUM(A.SUM_HOUR+B.SUM_HOUR) TOT_HOUR
FROM #CLERK_POS A JOIN #CLERK_NEG B ON A.CLERK_ID=B.CLERK_ID
GROUP BY A.CLERK_ID,A.CLERK_NAME
I think this would produce incorrect results if there are multiple rows for a given clerk_id in either table (such as in the sample data posted by Schareina). Even though it appears a little longer because of the subqueries, I would stick with Visakh's query; that would work correctly in all cases.
Go to Top of Page

karthik0805
Starting Member

14 Posts
Posted - 01/18/2013 :  05:10:01  Show Profile  Reply with Quote
I agree with you
Go to Top of Page

Schareina
Starting Member

4 Posts
Posted - 01/19/2013 :  04:04:50  Show Profile  Reply with Quote
Thanks for our replies.

Visakh16's Solution works fine for me. My mind say i'v wondered about the and-operator in the on-term but now it's clear.

Greetings Schareina
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/19/2013 :  04:07:01  Show Profile  Reply with Quote
welcome

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

subashseo
Starting Member

Barbados
4 Posts
Posted - 01/19/2013 :  07:14:42  Show Profile  Reply with Quote
unspammed
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.06 seconds. Powered By: Snitz Forums 2000