on 09-25-2008 10:09 AM
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
Hi..
To create command level parameters you dont need to declare in command.
--Remove the declaration of parameters from command.
--In the Add command window right hand side there is a button Create.
--Using tha above button create parameters and define the datatype of the parameters.
--Use the created parameters in your add command and click on ok.
It creates command level parameters.
Regards,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi..
Yes. You have to create these two parameters at command level and place the parameters in your command whereever you want to evaluate.
For Example : If you create a parameter by name YearNo. Then in your command you will have to use as follows :
Select * from <Table> where <TableField> = {?YearNo}
Hope this will help you.
Regards,
Sastry
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.