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
 Problem with Query

Author  Topic 

Schareina
Starting Member

4 Posts

Posted - 2013-01-18 : 02:22:59
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 03:16:24
[code]
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]
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

karthik0805
Starting Member

14 Posts

Posted - 2013-01-18 : 04:38:24
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-18 : 04:50:39
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 - 2013-01-18 : 05:10:01
I agree with you
Go to Top of Page

Schareina
Starting Member

4 Posts

Posted - 2013-01-19 : 04:04:50
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

52326 Posts

Posted - 2013-01-19 : 04:07:01
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

subashseo
Starting Member

4 Posts

Posted - 2013-01-19 : 07:14:42
unspammed
Go to Top of Page
   

- Advertisement -