Help with record counts query - 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

General SQL Server Forums

New to SQL Server Programming

Help with record counts query

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2009-10-04 : 22:05:01

Hi, I'm giving myself fits with this. I'm trying to create a table or view for a report we send out every month. In this view, I need to do counts, which I have found need to be done separately from the select. I have had some success getting the counts right, but not
getting the data to lay out how I need it.

This will give the basic layout (License# is the same as PK facilityID in the facitlity table, and the static text in the other columns will do for now, since all of our facilities are the same).

-------
CREATE table #Temp (
[License#] [varchar](50),
[Address] [varchar](1000),
[#LADCFS] [int],
[#LAPROB] [int],
[#LADMH] [int],
[#NONLA] [int],
[Total] [int],
[Ages][varchar] (50),
[Gender] [char] (1));
GO

Insert into temp (License#, address, Ages,Gender)
Select FacilityID, address1 + ' ' + city + ', ' + State + ' ' + zipcode as Address, '12-17', 'F'
from dbo.facility;
select * from # temp
drop table #temp

The general table structure is pretty simple.

residents: our clients
facility: our facilities
lookup_agency and lookup_county are pretty self explanatory.

This will generate the correct count for one of the columns for one of the facilities, but I just can't seem to tie it all together.

USE DIMONDALE
SELECT

'198203882' = (SELECT COUNT(ResidentID) FROM
(SELECT dbo.All_current.ResidentID, dbo.Facility.FacilityID, dbo.Lookup_Agency.AgencyID
FROM dbo.Facility INNER JOIN
dbo.All_current ON dbo.Facility.FacilityID = dbo.All_current.FacilityID INNER JOIN
dbo.Lookup_Agency ON dbo.All_current.AgencyID = dbo.Lookup_Agency.AgencyID) AS A
WHERE (AgencyID = 13) AND (FacilityID= '198203882'))
Thanks for any help!

----------------
-Stephen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 13:43:38

do you mean this?


SELECT
'198203882' = COUNT(CASE WHEN FacilityID= '198203882' THEN ResidentID ELSE NULL END),
<val2>= COUNT(CASE WHEN FacilityID= <val2> THEN ResidentID ELSE NULL END)
...
FROM dbo.Facility INNER JOIN
dbo.All_current ON dbo.Facility.FacilityID = dbo.All_current.FacilityID INNER JOIN
dbo.Lookup_Agency ON dbo.All_current.AgencyID = dbo.Lookup_Agency.AgencyID) AS A
WHERE (AgencyID = 13)

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2009-10-06 : 09:19:29

Hmmm, Maybe. I didn't explain it vey well, I don't think. The view should look like this, with LA denoting 'county' and dcfs, prob and dmh denoting 'agency' (does that make sense?):

License# |#LADCFS | #LAPROB |#LADMH |#NONLA | Total | Address | Ages | Gender
-----------------------------------------------------------------------------------

Where each row selects first the facility (by license#/facility ID) then uses that value joined with countyID and agencyID to do the counts for each column, then totals the row, then then selects the address, ages, gender for that value. Perhaps the counts should be done as functions? With parameters of facility ID, countyID and agencyID? Will that <val> pull data from column data for that same row, so the select could be something like
Select FacilityID, Address, fnCount(<val1>,<countyID>, <agencyID>), etc?

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources