Bruce writes "List Of Jobs
Page _ of _
First | Previous First | Previous Next | Last Next | Last
Contractor ID | Company Name | Number of Jobs
Well what I am trying to do seems simple enough but I am finding it harder then I expected. I am trying to create a record set to display in the table above. I would like to do it with a stored procedure and not in asp code as much as possible. Here is what I have so far. I use the Stored Procedure bellow.
CREATE Procedure SP_RetrieveRec_JobsPerTime
@contractorid varchar(12),
@StartTime datetime,
@EndTime datetime
As
if @contractorid <> ""
begin
select a.ContractorID, a.CompanyName , b.Job_ID
from Contractor a, CustomerJobInfo b, ContractorZipservices c, ContractorZipsTable d ,CustOrderAddress e
Where a.ContractorID = @contractorid and c.ContractorID = a.ContractorID and b.ServiceID = c.ServiceID and
c.ContractorID = d.ContractorID and b.JobAddressID = e.JobAddressID and D.zip = e.JobPostalcode and
b.DateCreated>='4/01/2001' and b.DateCreated<='5/01/2001'
end
else
begin
select a.ContractorID, a.CompanyName , b.Job_ID
from Contractor a, CustomerJobInfo b, ContractorZipservices c, ContractorZipsTable d ,CustOrderAddress e
Where a.TestAcc <> '1' and b.BadJob <> '1' and c.ContractorID = a.ContractorID and b.ServiceID = c.ServiceID and
c.ContractorID = d.ContractorID and b.JobAddressID = e.JobAddressID and D.zip = e.JobPostalcode and
b.DateCreated>='4/01/2001' and b.DateCreated<='5/01/2001'
Order BY a.ContractorID
End
The first problem I have with the above Stored Procedure is that I can’t seem to figure out how to past the dates in. I would like the dates to come in using the Start and End date variables but I get and error when I put those variables in place of those dates when trying to run the stored procedure. I am sure it is just a format problem but have not been able to find the information on how to do it. The above stored procedure then produce a Record Set like this.
ContractorID CompanyName Job_ID
--------------- -------------------------------------------------- -----------
CN0000000114 Heritage Construction 322
CN0000000114 Heritage Construction 324
CN0000000124 Elite Contractors & Design 333
CN0000000124 Elite Contractors & Design 336
CN0000000128 SEMJ,Inc 333
CN0000000128 SEMJ,Inc 336
CN0000000128 SEMJ,Inc 334
CN0000000129 Arrowhead Construction & Engineering 324
CN0000000130 P.J Construction 324
CN0000000131 Generations Construction, LP 322
CN0000000131 Generations Construction, LP 324
(148 row(s) affected)
I shortened the data
From here I use asp code to count an only display one ID and company name with the amount of jobs they received. This makes it very difficult to control my page size and to page through records. Can anyone think of easier way to return all the company ID’s Names and the number of jobs they got in the stored procedure itself? That would get ride of about 3 pages of code that I know is very inefficient to display this information.
By the way thank you guys for this site it has been a great help.
Bruce"
SP count problem 
Posted - 2001-05-23 : 21:05:44