Sum up value 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

Development Tools

Other Development Tools

Sum up value from 2 tables

Author

Topic

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-09 : 02:29:20

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.

SELECT 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-09 : 02:56:28

Yes. You are using MS ACCESS syntax.
Yes. You are using cartesian product for both tables which makes no sense.

SELECT		DATEPART(year, q.start_date) [year],
		DATEPART(week, q.start_date) [week],
		SUM(q.Sec)
FROM		(
			SELECT		start_date
					DATEDIFF(second, start_date, stop_date) Sec
			FROM		table1
			UNION ALL
			SELECT		start_date
					DATEDIFF(second, start_date, stop_date)
			FROM		table2
		) q
GROUP BY	DATEPART(year, q.start_date),
		DATEPART(week, q.start_date)
ORDER BY	1,
		2

Peter Larsson
Helsingborg, Sweden

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-09 : 03:53:55

i know my query have create a cartesian product but i just dunno how to make it right!!
i want to sum up the time different with the 2 table... the statement that you provide is not working and i don't think that is what i need too...
in the result i need to show the sum of the lapsed time between the start_date and stop_date in seconds and group by week.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 04:05:05

Why is it not working? Does it give the wrong result or is there a syntax error?

Even if it was wrong, the query did not give enough clues how to solve your problem?

SELECT		sum(datediff(second, t1.start_date, t1.stop_date)),
		sum(datediff(second, t2.start_date, t2.stop_date))
FROM		table1 t1
CROSS JOIN	table2 t2
GROUP BY	DATEPART(year, t1.start_date),
		DATEPART(week, t1.start_date)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 04:25:39

If you do not know what you need, try to give us same sample data for the two tables and some idea of how the expected output (based on the provided sample data) should look like.

Peter Larsson
Helsingborg, Sweden

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-09 : 04:31:49

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

what my sql statement will be??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 04:40:43

This (which you should be able to deduct from other answers)

SELECT		DATEPART(week, q.start_date) AS WorkWeek,
		SUM(q.col1) AS tbl1,
		SUM(q.col2) AS tbl2
FROM		(
			SELECT	start_date,
				DATEDIFF(second, start_date, end_date) AS col1,
				0 AS col2
			FROM	table1
			UNION ALL
			SELECT	start_date,
				0,
				DATEDIFF(second, start_date, end_date)
			FROM	table2
		) q
WHERE		DATEPART(year, q.start_date) = 2006
GROUP BY	DATEPART(week, q.start_date)
ORDER BY	DATEPART(week, q.start_date)

Peter Larsson
Helsingborg, Sweden

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-11-09 : 04:47:54

this query is not working!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 04:59:28

Why is it not working?
Do you receice an error or the wrong result?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 05:06:12

This test code (sample data provided by you)

declare @table1 table (start_date datetime, end_date datetime)

insert	@table1
select	'4/1/2006 12:31:32 AM', '4/1/2006 12:50:45 AM' union all
select	'4/1/2006 01:23:12 AM', '4/1/2006 01:30:01 AM' union all
select	'4/15/2006 02:03:22 AM', '4/15/2006 02:10:55 AM'

declare @table2 table (start_date datetime, end_date datetime)

insert	@table2
select '4/1/2006 01:11:32 AM', '4/1/2006 01:20:25 AM' union all
select '4/1/2006 02:23:12 AM', '4/1/2006 02:40:41 AM' union all
select '4/1/2006 03:03:22 AM', '4/1/2006 03:10:55 AM'

SELECT		DATEPART(week, q.start_date) AS WorkWeek,
		SUM(q.col1) AS tbl1,
		SUM(q.col2) AS tbl2
FROM		(
			SELECT	start_date,
				DATEDIFF(second, start_date, end_date) AS col1,
				0 AS col2
			FROM	@table1
			UNION ALL
			SELECT	start_date,
				0,
				DATEDIFF(second, start_date, end_date)
			FROM	@table2
		) q
WHERE		DATEPART(year, q.start_date) = 2006
GROUP BY	DATEPART(week, q.start_date)
ORDER BY	DATEPART(week, q.start_date)

produces the exact result as you want and showed us!

WorkWeek  tbl1  tbl2
--------  ----  ----
13        1562  2035
15         453     0

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 05:10:23

Have you posted to the wrong forum? This is a SQL Server forum.

Maybe you should have asked this question in the MS ACCESS forum?
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

SELECT		FORMAT(q.start_date, "ww", 1, 2) AS WorkWeek,
		SUM(q.col1) AS tbl1,
		SUM(q.col2) AS tbl2
FROM		(
			SELECT	start_date,
				DATEDIFF("s", start_date, end_date) AS col1,
				0 AS col2
			FROM	@table1
			UNION ALL
			SELECT	start_date,
				0,
				DATEDIFF("s", start_date, end_date)
			FROM	@table2
		) q
WHERE		YEAR(q.start_date) = 2006
GROUP BY	FORMAT(q.start_date, "ww", 1, 2)
ORDER BY	FORMAT(q.start_date, "ww", 1, 2)