using sign function - 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)

using sign function

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2009-05-07 : 04:32:02

hi there I have the following sql query that works - but isn't quite correct...

SELECT TOP (100) PERCENT dbo.docBranchData.branchName, dbo.docRegions.docRegionName, dbo.stockAnalysis.fldDealer,
COUNT(dbo.stockAnalysis.stockno) AS units, SUM(dbo.stockAnalysis.fldPrice) AS value, SUM(SIGN(dbo.stockAnalysis.fldDaysinstock)) AS Expr1
FROM dbo.docBranchData INNER JOIN
dbo.docRegions ON dbo.docBranchData.branchRegion = dbo.docRegions.docRegionID INNER JOIN
dbo.stockAnalysis ON dbo.docBranchData.branchID = dbo.stockAnalysis.fldDealer
GROUP BY dbo.docBranchData.branchName, dbo.docRegions.docRegionName, dbo.stockAnalysis.fldDealer, dbo.stockAnalysis.fldStatus
HAVING (dbo.stockAnalysis.fldStatus = 'prereg')
ORDER BY units DESC

Where it returns SUM(SIGN(dbo.stockAnalysis.fldDaysinstock) I would only like it to do this if flddaysinstock > 90 (Not the sum of > 90).

Anyone know how I can do this?

Many Thanks

Richard

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 04:37:21

you only want to perform the sum if flddaysinstock > 90 ?


SUM(CASE WHEN flddaysinstock > 90 THEN fldDaysinstock ELSE 0 END)

KH
[spoiler]Time is always against us[/spoiler]

squarefish
Starting Member

28 Posts

Posted - 2009-05-07 : 04:40:38

Not quite - but it may be on the right track.

I want to add up all the records that have been in stock more than 90 days, in other words add 1 to a count if daysinstock > 90.

Richard

squarefish
Starting Member

28 Posts

Posted - 2009-05-07 : 04:43:14

Would this work?
CASE WHEN flddaysinstock > 90 THEN sign(flddaysinstock) ELSE 0 END

squarefish
Starting Member

28 Posts

Posted - 2009-05-07 : 04:45:35

It does!

Thanks khtan!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 04:52:38

Isn't that the same as

SUM(CASE WHEN flddaysinstock > 90 THEN 1 ELSE 0 END)

Why sign a positive value and then sum it up?