Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query for formatting money 1

Status
Not open for further replies.

jlr123

Technical User
Joined
Feb 24, 2014
Messages
117
Location
US
I am just a beginner with SQL. I have two queries which were previously set up. The first formats dollars and cents, however the second one does not. I want to know if I can insert the Convert statement into the second query in the appropriate place?

Correct value for money in one query:
select Convert(nvarchar(max),cast(sum(Attainment) as money),1) as Cp_Comm, 'Year To Date Revenue' as Payee_id from payout_calc where incentive_id in ('Equipment Sales','SalesRep Service Commission','Field Service','Test Bench ELECTRICAL','Test Bench OTHER') and convert(nvarchar,Calc_Plan_Yr)='{!CurrentYearQC}' and payee_id<>'Jesse Flores'

Incorrect value as money in another query below:
Select C.Period_number AS Month,sum(Attainment) as [Total Revenue] from Payout_calc T Join Calendar_detail C On T.Period_Number=C.Period_number where incentive_id in ('Equipment Sales','SalesRep Service Commission','Field Service','Test Bench ELECTRICAL','Test Bench OTHER') and payee_id<>'Jesse Flores' Group By C.Period_number order by Month

Can I insert the sql convert statement into the query as below or is that not where it goes?
Select C.Period_number AS Month, select Convert(nvarchar(max),cast sum(Attainment)as money),1) as [Total Revenue] from Payout_calc T Join Calendar_detail C On T.Period_Number=C.Period_number where incentive_id in ('Equipment Sales','SalesRep Service Commission','Field Service','Test Bench ELECTRICAL','Test Bench OTHER') and payee_id<>'Jesse Flores' Group By C.Period_number order by Month

 
Omit the second "select":

[pre]
Select C.Period_number AS Month, Convert(nvarchar(max),cast sum(Attainment)as money),1) as [Total Revenue]
from Payout_calc T Join Calendar_detail C On T.Period_Number=C.Period_number
where incentive_id in ('Equipment Sales','SalesRep Service Commission','Field Service','Test Bench ELECTRICAL','Test Bench OTHER')
and payee_id<>'Jesse Flores' Group By C.Period_number order by Month
[/pre]
 
For some reason I get an error near sum when using the query editor.
 
When you run it, what error do you get?

Tamar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top