SP count problem: http://www.sqlteam.com/forums/link.asp?Topic_ID=6460

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP count problem: http://www.sqlteam.com/forums/link.asp?Topic_ID=6460

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-24 : 16:28:07

Bruce writes "http://www.sqlteam.com/forums/link.asp?Topic_ID=6460

Well I got it but if there is another way I would love to see it to use it for other problems like this. I used the temp table to get it done. Again I love your site. Thanks

CREATE Procedure SP_RetrieveRec_lookupjobs

@contid varchar(12),
@StartTime datetime,
@EndTime datetime,
@JobsCnt int
As

CREATE TABLE #TempJobs (
ContractorID varchar(12),
CompanyName varchar(40),
Job_ID char(30) )

if @contid <> ''
begin
INSERT #TempJobs
select a.ContractorID, a.CompanyName , b.Job_ID
from Contractor a, CustomerJobInfo b, ContractorZipservices c, ContractorZipsTable d ,CustOrderAddress e
Where a.ContractorID = @contid 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>=(convert(smalldatetime,convert(char(10),@StartTime,1))) and b.DateCreated<= (convert(smalldatetime,convert(char(10),@EndTime,1))) and b.BadJob = 0

end
else
begin
INSERT #TempJobs
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>=(convert(smalldatetime,convert(char(10),@StartTime,1))) and b.DateCreated<= (convert(smalldatetime,convert(char(10),@EndTime,1))) and
b.BadJob = 0)

end

CREATE TABLE #TempJobsTotals (
ContractorID varchar(12),
CompanyName varchar(40),
jobs int )

begin
INSERT #TempJobsTotals

Select Distinct ContractorID, CompanyName , count(ContractorID) as jobs
From #TempJobs

Group By ContractorID,CompanyName

Order by CompanyName
end
begin

Select *
From #TempJobsTotals
Where jobs >=@JobsCnt

end

drop table #TempJobs
drop table #TempJobsTotals

That did it. I also made it possible to only display jobs over a selected number. Maybe this would help someone else

Bruce"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources