SQL Query for formatting money
SQL Query for formatting money
(OP)
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
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
Talk To Other Members
RE: SQL Query for formatting money
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 MonthRE: SQL Query for formatting money
RE: SQL Query for formatting money
Tamar