Query 2 summed columns from same table

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

Query 2 summed columns from same table

Author

Topic

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-16 : 15:59:20

I have a table that contains
+------------------------------------------------------------------+
|Emp_No | Last_Name | First_Name | fsdatetime | fedatetime | fjobno |
+------------------------------------------------------------------+

Actually it is two tables (last and first names are added via a join). But what I am trying to do is sum the hours spent on job I0002-000 as one column called indirect and then sum the hours on any other job as direct for each employee. Here is what I have:
--


SELECT l.fempno AS "Emp_No",
       p.fname AS "Last_Name",
       p.ffname	AS  "First_Name",
       (sum(DATEDIFF(mi, dir.FSDATETIME,dir.FEDATETIME))/60) AS "Direct",
       (sum(DATEDIFF(mi, indir.FSDATETIME,indir.FEDATETIME))/60) AS "Indirect"

FROM ladetail l
INNER JOIN PREMPL p ON p.fempno = l.fempno

INNER JOIN (
SELECT fempno, fjobno, FSDATETIME, FEDATETIME FROM ladetail

) indir ON l.fempno = indir.fempno AND indir.fjobno = 'I0002-0000'

INNER JOIN (
SELECT fempno, fjobno, FSDATETIME, FEDATETIME FROM ladetail

) dir ON l.fempno = dir.fempno AND dir.fjobno != 'I0002-0000'

WHERE CONVERT(varchar,l.FSDATETIME,101) = CONVERT(varchar,getdate()-1,101)

GROUP BY l.fempno, p.fname, p.ffname
ORDER BY Indirect DESC

--
This is SQL 2000 and I get this error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 01:26:47

try casting it to bigint

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

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-17 : 14:00:52

But I am not casting anything to INT. That's really what I don't understand.

Sachin.Nand

2937 Posts

Posted - 2010-03-17 : 14:02:54

I think the problem is with datediff function because its return type is INT.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 14:03:25

Is this select source for insertion of values to another table?

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

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-17 : 20:52:39

I just imported some data and the tables onto my laptop running SQL Server 2008 and this query ran like a charm. :-/

@visakh16 - No. Just for the sake of a report.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-18 : 08:42:28

When I copy all the data to the test DB I get the same error as on the prod system. I'm not sure what is going on...

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-18 : 10:47:03

Ok, I went back to the drawing board and this is what I have come up with:
--


SELECT l.fempno,
       p.fname  AS "First_Name",
       p.ffname AS "Last_Name", 
       "Direct_Time" =
		    CASE
				WHEN SUM(DATEDIFF(mi, dir.fsdatetime, dir.fedatetime)) IS NULL THEN 0
				ELSE SUM(DATEDIFF(mi, dir.fsdatetime, dir.fedatetime))
			END,
		"Indirect_Time" =
			CASE
				WHEN SUM(DATEDIFF(mi, indir.fsdatetime, indir.fedatetime)) IS NULL THEN 0
				ELSE SUM(DATEDIFF(mi, indir.fsdatetime, indir.fedatetime))
			END
 
 FROM ladetail l
 INNER JOIN prempl p ON l.fempno = p.fempno
 LEFT JOIN ladetail dir   ON l.fempno = dir.fempno   and dir.fjobno  != 'I0002-0000' AND YEAR(dir.fsdatetime) = YEAR(GETDATE()) AND MONTH(dir.fsdatetime) = MONTH(GETDATE()) AND day(dir.fsdatetime) = day(GETDATE())-15
 LEFT JOIN ladetail indir ON l.fempno = indir.fempno and indir.fjobno = 'I0002-0000' AND YEAR(indir.fsdatetime) = YEAR(GETDATE()) AND MONTH(indir.fsdatetime) = MONTH(GETDATE()) AND day(indir.fsdatetime) = day(GETDATE())-15

 WHERE YEAR(l.fsdatetime) = YEAR(GETDATE()) AND MONTH(l.fsdatetime) = MONTH(GETDATE()) AND day(l.fsdatetime) = day(GETDATE())-15
  
 GROUP BY l.fempno, p.fname, p.ffname

This works and gives me the the results I expect. I could probably clean up all of the getdate information, but this is fine as a quick-and-direty query. I got rid of the sub queries and I think that was a major performance issue with my original. Even when it was working, it was taking three times as long.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources