Trying to get zero counts - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Trying to get zero counts

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-10 : 07:39:58

I need to return a 0 count for the WebDesc that do not have any offenders in the county. So for example. Allegheny does not have any offenders for the WebDesc of "Sexually Violent Predator" I need to see a row with
0 Allegheny Sexually Violent Predator School

But I only get this

14 Allegheny Lifetime Offender School
7 Allegheny Out-of-State Offender School
7 Allegheny Ten Year Offender School

These are the fields
CountyCount
CountyName
WebDesc
AddType

Here is my proc.

DECLARE @NumberRecords int, @RowCount int
DECLARE @CountyCountTable Table

(

CountyCount int,
CountyName varchar(8000),
WebDesc varchar(8000),
addType varchar(8000)
)

DECLARE @CountyTable Table
(

RowID int IDENTITY(1, 1),
CountyCode int,
CountyName varchar(8000)
)

INSERT INTO @CountyTable

SELECT COUNTY_CODE, COUNTY_NAME FROM SPGlobalLookups.dbo.County_LKP

SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

DECLARE @CountyCode int
DECLARE @CountyName varchar(8000)

WHILE @RowCount <= @NumberRecords
BEGIN

SELECT @CountyCode = CountyCode, @CountyName = CountyName
FROM @CountyTable
WHERE RowID = @RowCount

INSERT @CountyCountTable
SELECT COUNT(offender.offenderid) AS CountyCount, @CountyName as CountyName, dbo.Offender_Type_LKP.Web_Desc as WebDesc, 'Resident' as addType
FROM dbo.OffenderAddress INNER JOIN
dbo.Offender ON offender.offenderid = offenderaddress.offenderid AND dbo.OffenderAddress.County_Code = @CountyCode AND
dbo.OffenderAddress.IsCurrent = 1 AND dbo.OffenderAddress.Type <> N'Mailing' RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON Offender.Offender_Type_LKP_ID = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
WHERE (dbo.offender.Offender_Type_LKP_ID <> 9)
GROUP BY dbo.Offender_Type_LKP.Web_Desc

INSERT @CountyCountTable
SELECT COUNT(offender.offenderid) AS CountyCount, @CountyName as CountyName, dbo.Offender_Type_LKP.Web_Desc as WebDesc, 'Employee' as addType
FROM dbo.OffenderEmployment INNER JOIN
dbo.Offender ON offender.offenderid = OffenderEmployment.offenderid AND dbo.OffenderEmployment.County_Code = @CountyCode AND
dbo.OffenderEmployment.IsCurrent = 1 RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON Offender.Offender_Type_LKP_ID = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
WHERE (dbo.offender.Offender_Type_LKP_ID <> 9)
GROUP BY dbo.Offender_Type_LKP.Web_Desc

INSERT @CountyCountTable
SELECT COUNT(offender.offenderid) AS CountyCount, @CountyName as CountyName, dbo.Offender_Type_LKP.Web_Desc as WebDesc, 'School' as addType
FROM dbo.OffenderSchoolInformation INNER JOIN
dbo.Offender ON offender.offenderid = OffenderSchoolInformation.offenderid AND dbo.OffenderSchoolInformation.County_Code = @CountyCode AND
dbo.OffenderSchoolInformation.IsCurrent = 1 RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON Offender.Offender_Type_LKP_ID = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
WHERE (dbo.offender.Offender_Type_LKP_ID <> 9)
GROUP BY dbo.Offender_Type_LKP.Web_Desc

SET @RowCount = @RowCount + 1

END

SELECT * FROM @CountyCountTable

END

Dave
Helixpoint Web Development
http://www.helixpoint.com

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-10 : 08:10:40

what is the table that holds all the web desc.
Is it dbo.Offender_Type_LKP.Web_Desc ?

I feel that above name part is wrong. Please check and confirm.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-10 : 08:25:30

That is correct

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-10 : 09:37:59

I figured it out

Dave
Helixpoint Web Development
http://www.helixpoint.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources