Using Transform within Sp
Hi
Pls Help, I have this transform stmt within a Sp which I wish to use
inorder to output the results similar to a crosstab qry...so my ? is can
I use this code and if so why do I recieve an error
Code:
if @ReportType = 'MonthlyCounts'
begin
TRANSFORM count(CQuote_int)as Quotes
SELECT DateName(Month, ReportDate) Month
from #transactionstable
WHERE (DATEPart(YYYY, ReportDate) = DATEPart(YYYY, GetDate())
GROUP BY DateName(Month, ReportDate), datePart(MM,ReportDate)
ORDER BY order by datePart(MM,ReportDate)
PIVOT transactionstable.Month;
/*
select DateName(Month, ReportDate) Month, sum(CQuote_int)Quotes ,sum(CReferral_int)Referrals , sum(CNewBus_int) Policies
from #transactionstable
where DATEPart(YYYY, ReportDate) = DATEPart(YYYY, GetDate())
group by DateName(Month, ReportDate), datePart(MM,ReportDate)
order by datePart(MM,ReportDate)
*/
end
The errors are
Code:
Server: Msg 170, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 123
Line 123: Incorrect syntax near 'TRANSFORM'.
Server: Msg 156, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 127
Incorrect syntax near the keyword 'GROUP'
Ta very much
Re: Using Transform within Sp
You say you are using this statement in an SP, so i take it you are using SQL Server ???
If so then you can't use the Transform Statement it doesn't exist in SQL Server it is an Access Specific statement
Re: Using Transform within Sp
Hi, Thanks for replying!! is there or do you know if this can be done in sql
using any other stmt??
:blush:
Re: Using Transform within Sp
