hi guys
i have a storedprocedure but i'm not allowed to add new storedprocedures in the database so i created a command for the report and now my problem i'm having difficulties creating parameters:
declare @WeekNo varchar(10),
declare @YearNo varchar(10)
select distinct vw_wrkWorkOrders.SectionDescription,
round((select convert(float,count(1)) from vw_wrkWorkOrders a
where vw_wrkWorkOrders.SectionDescription = a.SectionDescription
and (convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and
convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/
(select case COUNT(1) when 0 then 1 else count(1) end
from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription)*100,1) as 'Job Requested',
round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Closed','Cancelled') and
(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and
convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/
(select case count(1) when 0 then 1 else count(1) end
from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Closed','Cancelled'))*100,1) as 'Job Completed',
round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Scheduled, Awaiting Execution') and
(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and
convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/
(select case count(1) when 0 then 1 else count(1) end
from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Scheduled, Awaiting Execution'))*100,1) as 'Job Scheduled',
round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Complete/Awaiting Feedback') and
(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and
convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/
(select case count(1) when 0 then 1 else count(1) end
from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and
a.UserDefinedStateDescription in ('Complete/Awaiting Feedback'))*100,1) as 'OutStanding'
from vw_wrkWorkOrders
where vw_wrkWorkOrders.SectionDescription in ('Civil\Building\Carpentry','Electrical','Mechanical','Painting',
'Plumbing','Welding')
i have to create the two parameters @WeekNo, @YearNo which are in the query