Sum from 2 tables - 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

Other Forums

MS Access

Sum from 2 tables

Author

Topic

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-09 : 19:27:34

There have 2 tables, each table contain of start_date and stop_date. i need to find out the time different between start_date and stop_date and sum it up according to workweek from 2 tables. here is my syntax but the figure seem incorrect.

For example
table 1
start_date end_date
4/1/2006 12:31:32 AM 4/1/2006 12:50:45 AM
4/1/2006 01:23:12 AM 4/1/2006 01:30:01 AM
4/15/2006 02:03:22 AM 4/15/2006 02:10:55 AM
table 2
start_date end_date
4/1/2006 01:11:32 AM 4/1/2006 01:20:25 AM
4/1/2006 02:23:12 AM 4/1/2006 02:40:41 AM
4/1/2006 03:03:22 AM 4/1/2006 03:10:55 AM

my result will be like this
workweek tbl1 tbl2
13 1562 2035
15 453 0

Here is my syntax!!
SELECT FORMAT(start_date, 'ww', 1,2) AS workweek,sum(datediff("s", 1.start_date, 1.stop_date)), sum(datediff("s", 2.start_date, 2.stop_date)
FROM table1 1, table2 2
GROUP BY FORMAT(1.start_date, 'ww', 1, 2);
pls advice!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 00:38:22

Also discussed in depth here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74666

You write the query is not working. What is wrong? You have to tell us!
Do you get any result at all? Do you get an error?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 00:47:09

One thought for my previously posted suggestions...
You are aware you have to substitute my table names @table1 and @table2 with your real table names in your environment?

Peter Larsson
Helsingborg, Sweden

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-12 : 21:35:14

if using UNION ALL, it will not seperate tbl1 and tbl2 result... tbl2 result will continue after tbl1 result... and the group by function is aggregate with other column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources