Subtracting field values - 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

Subtracting field values

Author

Topic

zai
Starting Member

9 Posts

Posted - 2010-09-03 : 12:01:11

Hi all,

I have a working script that generates a table a simple table.

The Query:

SELECT Name, Location,
Sum(CASE WHEN Status = 'Open' AND Hold = 'No' AND Hold_Media = 'No' AND Hold_Vendor = 'No' THEN 1 END) TotalOpen,
Sum(CASE WHEN Hold_Vendor = 'W.Vendor' THEN 1 END) TotalWait,
Sum(CASE WHEN DateRequestDue < GetDate() AND Day(DateRequestDue) != Day(GetDate()) AND ((Hold_Vendor = 'W.Vendor') OR (Status = 'Open' AND Hold = 'No' AND Hold_Media = 'No' AND Hold_Vendor = 'No')) THEN 1 END) TotalPast
FROM [Discovery].[dbo].[eForm]
WHERE Location IS NOT NULL AND Name IS NOT NULL
GROUP BY Name, Location
ORDER BY TotalOpen desc, Name asc

The result looks like this:

Name Location Total_Open Total_Wait Total _Past

Joe NY 10 5 3
Bob DC 25 5 5

What I liked to do, but don't know how to:

I liked to simply add one more column to the script and result above. The value of the new column would be the difference between two of the columns of the script.

I need to create a new column called "Work" and it would be taking the column value of "Total Open" - "Total Past Due".

It would look like this:

Name Location Total_Open Total_Wait Total _Past Work

Joe NY 10 5 3 7
Bob DC 25 5 5 20

How would I add this column into the script I already have?

Thank you very much for you time.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-03 : 13:48:35

Try this:


SELECT
X.Name
,X.Location
,X.TotalOpen
,X.TotalWait
,X.TotalPast
,Work = X.TotalOpen - X.TotalPast
FROM
(
SELECT 
Name
,Location
,Sum(CASE WHEN Status = 'Open' AND Hold = 'No' AND Hold_Media = 'No' 
AND Hold_Vendor = 'No' THEN 1 END) TotalOpen
,Sum(CASE WHEN Hold_Vendor = 'W.Vendor' THEN 1 END) TotalWait
,Sum(CASE WHEN DateRequestDue < GetDate() AND Day(DateRequestDue) != Day(GetDate()) 
AND ((Hold_Vendor = 'W.Vendor') 
OR (Status = 'Open' AND Hold = 'No' AND Hold_Media = 'No' AND Hold_Vendor = 'No')) THEN 1 END) TotalPast

FROM [Discovery].[dbo].[eForm]
WHERE 
			Location IS NOT NULL 
		AND Name IS NOT NULL
GROUP BY 
			 Name
			,Location
-- ORDER BY TotalOpen desc, Name asc
) as X

ORDER BY X.TotalOpen DESC, X.Name ASC

zai
Starting Member

9 Posts

Posted - 2010-09-04 : 06:03:16

slimt_slimt,

This is perfect. Thank you very much for taking the time to assist me on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources