Group By - 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

General SQL Server Forums

New to SQL Server Programming

Group By

Author

Topic

zulfiqarnoor
Starting Member

1 Post

Posted - 2012-04-25 : 09:55:50

If i have two Tables , Say Tabl1

ID Deposit Date
1 10 12/3/2012
3 20 13/3/2012
4 30 23/3/2012
4 34 23/03/2012

and tabl2 with
ID Expenditure Date
1 5 14/3/2012
3 15 15/3/2012
4 18 24/3/2012
4 16 25/03/2012

Now how can display the sum of deposit and expenditure Group By week.
Week# Profit
3 30-20 = 10
4 64-34 = 30

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-25 : 11:06:21

This is not too hard to do, except for one thing. How do you define your "week"? (http://msdn.microsoft.com/en-us/library/ms174420.aspx).

In your case, it looks like you are just counting weeks starting on some specific date. If so, you can specify what that start date is and then do the calculation like this:

DECLARE @startDate DATE = '20120312';
SELECT
	COALESCE(d.Week,e.Week) AS Week,
	Deposits,
	Expenditures,
	COALESCE(Deposits,0) - COALESCE(Expenditures,0) AS Net
FROM
(
	SELECT
		DATEDIFF(dd,@startDate,[Date])/7+1 AS Week,
		SUM(Deposit) AS Deposits
	FROM
		Table1
	GROUP BY
		DATEDIFF(dd,@startDate,[Date])/7+1
) d
FULL JOIN
(
	SELECT
		DATEDIFF(dd,@startDate,[Date])/7+1 AS Week,
		SUM(Expenditure) AS Expenditures
	FROM
		Table2
	GROUP BY
		DATEDIFF(dd,@startDate,[Date])/7+1
) e ON e.Week = d.Week;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources