Companies with negative totals only

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 2008 Forums

Transact-SQL (2008)

Companies with negative totals only

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-10-14 : 07:29:45

Hi,

I need help please.

I need to return per idno and companyno where the total is negative. If I have a companyNo where the total for one row is positive but for another row it's negative then it's not relevant.
To know if sum is negative or positive i need to do the following
sum(compensation+insurance)*factoring

create table #payments (id int identity (1,1) not null ,idno int, compensation float, insurance float,factoring int, companyno int)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (502592	,	624.75	,375,-1,3456)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (502592	,	124.75	,275,-1	,3456)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (797147	,	124.75	,275, 1,5678)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (797147	,	124.75	,275, -1,5678)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (461588	,	874.65	,525,-1	,78909)
insert into #payments (idno,compensation,insurance,factoring,companyno) values (461588	,	874.65	,525,1,126789	)

OUTPUT
id idno compensation insurance factoring companyno
1 502592 624.75 375 -1 3456
2 502592 124.75 275 -1 3456
5 461588 874.65 525 -1 78909

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-14 : 07:43:46

Select * From TableName Where Id In (1,2,5)

veeranjaneyulu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-14 : 08:48:54

Add a having clause like shown below:

SELECT companyno,SUM((compensation+insurance)*factoring)
FROM #payments 
GROUP BY companyno
HAVING SUM((compensation+insurance)*factoring) < 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 09:08:29

I think what you need is this


SELECT idno,compensation,insurance,factoring,companyno
FROM
(
SELECT idno,compensation,insurance,factoring,companyno,
SUM(CASE WHEN (compensation+insurance)*factoring > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY CompanyNo) AS PosSumCnt
FROM #payments 
)t
WHERE PosSumCnt = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-10-14 : 09:23:40

quote:
Originally posted by visakh16

I think what you need is this

SELECT idno,compensation,insurance,factoring,companyno
FROM
(
SELECT idno,compensation,insurance,factoring,companyno,
SUM(CASE WHEN (compensation+insurance)*factoring > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY CompanyNo) AS PosSumCnt
FROM #payments
)t
WHERE PosSumCnt = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks but I don't need to return companies that also have positive values. If a company has a positive and a negative value than i mustn't return it. I must only show companies that have negative values only.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-10-14 : 09:28:18

also it is important that i show the idno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 09:32:06

quote:
Originally posted by collie

quote:
Originally posted by visakh16

I think what you need is this

SELECT idno,compensation,insurance,factoring,companyno
FROM
(
SELECT idno,compensation,insurance,factoring,companyno,
SUM(CASE WHEN (compensation+insurance)*factoring > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY CompanyNo) AS PosSumCnt
FROM #payments
)t
WHERE PosSumCnt = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks but I don't need to return companies that also have positive values. If a company has a positive and a negative value than i mustn't return it. I must only show companies that have negative values only.

the given query will only show cases where company has only negative values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-10-14 : 14:04:38

visakh16 you are correct. For some reason when i first ran it i received incorrect results but it was my mistake.

Thanks so much for the help as always :)

Thanks everyone that helped :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources