I have basically 3 tables, #1 holds the client information, #2 holds records concerning the client and #3 a list of zipcode information.
I am trying to build a query which considers the client only once regardless of how many records they have in the "activity" table. They need at least one record in the activity table however, to be counted. Then I pull information from the "zipcode" table on the county, city, etc. Basically when it's done I return a result like:
37372 = 10
73658 = 5
65891 = 12
The query I've built seems to be pulling too many numbers. Comparing against a small data set I am pulling something like:
But when I pull the data through a simpler query I can see that 1 client had no zip (so they shouldn't be considered) 1 client had 2 entries (so they should be counted once) and another client with has 1 activity record (so they should be counted once). It should be showing:
I hope this is not too jumbled of an explanation but I'm trying to give as much background information as I can. The current query is posted below.
Thanks for any help.
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@churchList varchar (1000)
, count(c.zip) AS zipCount
JOIN ZipCodeDatabase_STANDARD z
ON zip = ZipCode
EXISTS (SELECT NULL
JOIN users u
ON u.username = a.username
JOIN dbo.fn_SplitString(@churchList, ',') list
ON list.StringValue = church
(a.CharityCkID = c.CharityCkID)
AND (c.zip = z.ZipCode)
AND (a.date >= @dStart)
AND (a.date <= @dEnd))