query assistance re: select - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query assistance re: select

Author

Topic

klevy
Starting Member

4 Posts

Posted - 2007-09-13 : 15:20:26

The following represents profit and loss for projects. Each project may have multiple sub projects. I would like to be able to select all projects lines for those projects that have an aggregate profit, in this case project A. Can anyone provide me with syntax help for the query statement?

Project# Sub-Project# Profit/(loss)
Project A 1 $2,000
Project A 2 ($1,000)
Project A 3 $2,000
Project B 1 ($2,000)
Project B 2 $1,000

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 15:24:55

group by projID
having count(*)>1

--------------------
keeping it simple...

mohit_sme
Starting Member

13 Posts

Posted - 2007-09-13 : 15:32:21

I am assuming that you need only profitable projects, irrespective of sub-project's profit or loss.

SELECT ProjectName
FROM TableName
GROUP BY ProjectName
HAVING SUM (ProfitLoss) > 0

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 15:36:52

that will not work

quote:
Originally posted by mohit_sme

I am assuming that you need only profitable projects, irrespective of sub-project's profit or loss.

SELECT ProjectName
FROM TableName
GROUP BY ProjectName
HAVING SUM (ProfitLoss) > 0

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

--------------------
keeping it simple...

mohit_sme
Starting Member

13 Posts

Posted - 2007-09-13 : 15:49:56

Can you explain why, I tried that already

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

klevy
Starting Member

4 Posts

Posted - 2007-09-14 : 07:01:58

Mohit, you are correct in your assumption about the sub-project's profitability. Thank you for your response. I will try it today.

quote:
Originally posted by mohit_sme

I am assuming that you need only profitable projects, irrespective of sub-project's profit or loss.

SELECT ProjectName
FROM TableName
GROUP BY ProjectName
HAVING SUM (ProfitLoss) > 0

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

klevy
Starting Member

4 Posts

Posted - 2007-09-14 : 10:51:53

Mohit, you are awesome. It worked as advertised. Kudos to you. Thanks.

Ken

quote:
Originally posted by klevy

Mohit, you are correct in your assumption about the sub-project's profitability. Thank you for your response. I will try it today.

quote:
Originally posted by mohit_sme

I am assuming that you need only profitable projects, irrespective of sub-project's profit or loss.

SELECT ProjectName
FROM TableName
GROUP BY ProjectName
HAVING SUM (ProfitLoss) > 0

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

klevy
Starting Member

4 Posts

Posted - 2007-09-17 : 11:49:24

I think I jumped the gun on my excitement. My ultimate goal is to display all rows of "profitable" projects, even sub-project rows without a profit. Similarly, if their is no profit at the project level, then no rows should display.

Thanks,

quote:
Originally posted by klevy

Mohit, you are awesome. It worked as advertised. Kudos to you. Thanks.

Ken

quote:
Originally posted by klevy

Mohit, you are correct in your assumption about the sub-project's profitability. Thank you for your response. I will try it today.

quote:
Originally posted by mohit_sme

I am assuming that you need only profitable projects, irrespective of sub-project's profit or loss.

SELECT ProjectName
FROM TableName
GROUP BY ProjectName
HAVING SUM (ProfitLoss) > 0

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-17 : 12:42:50

Is this what you are looking for?

DECLARE @Project TABLE (ProjectName VARCHAR(50), SubProjectID INT, Proffit MONEY)

INSERT @Project
SELECT 'Project A', 1, $2000 
UNION ALL SELECT 'Project A', 2, $-1000
UNION ALL SELECT 'Project A', 3, $2000 
UNION ALL SELECT 'Project B', 1, $-2000
UNION ALL SELECT 'Project B', 2, $1000 
UNION ALL SELECT 'Project C', 5, $2000
UNION ALL SELECT 'Project C', 6, $-1000


SELECT
	Project.*
FROM
	@Project AS Project
INNER JOIN
	(
		SELECT 
			ProjectName
		FROM 
			@Project p
		GROUP BY
			ProjectName
		HAVING
			SUM(Proffit) > 0
	) AS Temp
	ON Project.ProjectName = Temp.ProjectName

mohit_sme
Starting Member

13 Posts

Posted - 2007-09-17 : 12:47:10

Klevy, that is very easy, you just need one more join or subquery. But the base logic remains same.

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 13:39:19

Or this

SELECT		TOP 1 WITH TIES
		ProjectName,
		SubProjectId,
		Profit	
FROM		@Project
ORDER BY	SIGN(SUM(Profit) OVER (PARTITION BY ProjectName)) DESC

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources