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
 Data Field question

Author  Topic 

midpenntech
Posting Yak Master

137 Posts
Posted - 2008-06-09 : 09:52:12
Are you able to take Case statments that have AS (Field name) and use those to subtract after one another under the data tabin sql?

For example i created 3 case statments and they have 3 columns

SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,
SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation,
SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END)
AS Repair

Under the data table I want to Subtract RentalBilling, Depreciation, and Repairs and then have that out come posted in a new column called totals.

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2008-06-09 : 10:36:21
No as the compiler can not evaluate labels at run time.

You would either have to do this as one expression or you could use temporary tables to store the initial values and then use this to subtract.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2008-06-09 : 10:40:14
Use a derived table.


select 
  x.RentalBilling, x.Depreciation, x.Repair,
  x.RentalBilling + x.Depreciation + x.Repair as Total
from
(  
  your sql statement here
) x


Do not forget the "x" at the end, that is the alias for the derived table that contains your SQL statement.



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

midpenntech
Posting Yak Master

137 Posts
Posted - 2008-06-09 : 10:48:41
I think I am confused on how you have this placed. what do you mean bya derived table? are you saying change the AS RentalBilling and change it to x.Rentalbilling under the case section?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-09 : 11:16:12
quote:
Originally posted by midpenntech

I think I am confused on how you have this placed. what do you mean bya derived table? are you saying change the AS RentalBilling and change it to x.Rentalbilling under the case section?

This is what he meant
select 
  x.RentalBilling, x.Depreciation, x.Repair,
  x.RentalBilling + x.Depreciation + x.Repair as Total
from
(
SELECT
	SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling, 
	SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation, 
	SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END) AS Repair
FROM
	your_Table  
) x



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

midpenntech
Posting Yak Master

137 Posts
Posted - 2008-06-09 : 11:47:39
This is what he meant
select
x.RentalBilling, x.Depreciation, x.Repair,
x.RentalBilling + x.Depreciation + x.Repair as Total
from <----Should I put something here?
What is this part---->(
SELECT
SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,
SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation,
SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END) AS Repair
FROM
your_Table
) x <-----and here
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-09 : 11:58:16
quote:
Originally posted by midpenntech

This is what he meant
select
x.RentalBilling, x.Depreciation, x.Repair,
x.RentalBilling + x.Depreciation + x.Repair as Total
from <----Should I put something here?
What is this part---->(
SELECT
SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,
SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation,
SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END) AS Repair
FROM
your_Table
) x <-----and here




That is the alias name given for the derived table. In SQL Server you must specify the alias name. In this example the alias name is x

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

midpenntech
Posting Yak Master

137 Posts
Posted - 2008-06-09 : 12:00:33
This is my code I am so sorry but I dont think I understand can you show me how it would go with my code in what you wrote?


SELECT equip.eqpphybr, equip.kequipnum, equip.kmfg, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001 AS acqcost,equipdaily.cnvf00109 AS bookvalue, SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation, SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END)AS Repairs, equipdaily.cnvf00109 * 0.07 * DATEDIFF(d, '6/1/2008', equip.eqprecdt) / 365 AS interest, DATEDIFF(d, '6/1/2008', equip.eqprecdt)AS Days

FROM equipdet INNER JOIN
equip ON equipdet.kequipnum = equip.kequipnum INNER JOIN
equipdaily ON equip.kequipnum = equipdaily.kequipnum
WHERE (equip.custdisad1 <> 'RPO') AND (equipdaily.cnvf00109 > 0) AND (equip.kmfg <> 'CV') AND (equipdet.gldate BETWEEN @fromdate AND @thrudate)
GROUP BY equip.kmfg, equip.eqpphybr, equip.kequipnum, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001, equipdaily.cnvf00109
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-09 : 12:07:28
quote:
Originally posted by midpenntech

This is my code I am so sorry but I dont think I understand can you show me how it would go with my code in what you wrote?


SELECT equip.eqpphybr, equip.kequipnum, equip.kmfg, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001 AS acqcost,equipdaily.cnvf00109 AS bookvalue, SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation, SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END)AS Repairs, equipdaily.cnvf00109 * 0.07 * DATEDIFF(d, '6/1/2008', equip.eqprecdt) / 365 AS interest, DATEDIFF(d, '6/1/2008', equip.eqprecdt)AS Days

FROM equipdet INNER JOIN
equip ON equipdet.kequipnum = equip.kequipnum INNER JOIN
equipdaily ON equip.kequipnum = equipdaily.kequipnum
WHERE (equip.custdisad1 <> 'RPO') AND (equipdaily.cnvf00109 > 0) AND (equip.kmfg <> 'CV') AND (equipdet.gldate BETWEEN @fromdate AND @thrudate)
GROUP BY equip.kmfg, equip.eqpphybr, equip.kequipnum, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001, equipdaily.cnvf00109

Try

SELECT x.*,x.RentalBilling + x.Depreciation + x.Repair as Total FROM
(
	SELECT equip.eqpphybr, equip.kequipnum, equip.kmfg, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001 AS acqcost,equipdaily.cnvf00109 AS bookvalue, SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation, SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END)AS Repairs, equipdaily.cnvf00109 * 0.07 * DATEDIFF(d, '6/1/2008', equip.eqprecdt) / 365 AS interest, DATEDIFF(d, '6/1/2008', equip.eqprecdt)AS Days
	FROM equipdet INNER JOIN
	equip ON equipdet.kequipnum = equip.kequipnum INNER JOIN
	equipdaily ON equip.kequipnum = equipdaily.kequipnum
	WHERE (equip.custdisad1 <> 'RPO') AND (equipdaily.cnvf00109 > 0) AND (equip.kmfg <> 'CV') AND (equipdet.gldate BETWEEN @fromdate AND @thrudate)
	GROUP BY equip.kmfg, equip.eqpphybr, equip.kequipnum, equip.kmodel, equip.kserialnum, equip.eqprecdt, equipdaily.cnvf001, equipdaily.cnvf00109 
) x


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

midpenntech
Posting Yak Master

137 Posts
Posted - 2008-06-09 : 12:08:49
I got it to work thank you so much.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site