Finding distinct values between two fiscal years

I have a sample of data from my table in sql.  The table is named AttendanceGrid.

Unfortunately there was some poor planning and now I have to figure out how to get distinct values from two fiscal years.  The problem is that the ID's are different in Fiscal 2013 and Fiscal 2014.  however the rest of the record is considered a duplicate.

How is it possible to get a count by committee of all the unduplicated records from September 2013 to August 2014

Here is what I started and am getting the wrong results....

Select Committee, count(*) from AttendanceGrid Where (Month IN ( 'September', 'October', 'November','December','January', 'February','March','April', 'May','June') And Fiscal = 2013)
Or
(Month IN ( 'July', 'August') And Fiscal = 2014)
And Total > 0
AND [CSW/TitleXX] = 'T'
Group by Committee, [First Name],[Last Name],BirthDate
Order by Committee
al4629740Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
The query in the question has 4 fields in the GROUP BY clause but of those only [Committee] in in the SELECT clause
That is a syntax error.

If you want a count by Committee, the only GROUP BY [Committee]

Now, you don't really explain how to arrive at a measure of "distinct" and that word is so abused it is hard to know what you really want.

My guess is you want to figure out how many different people have participated in each committee in that 12 month period. I.e. if the same person is found more than once you still only want a person to be 1

for this you use COUNT( DISTINCT [something] )

e.g.
SELECT
      Committee
    , COUNT( DISTINCT [First Name] + [Last Name] + convert(varchar, [BirthDate] ,112) ) as DistinctCount
    , COUNT( * ) as CountAny
FROM AttendanceGrid
WHERE (
            Month IN ('September', 'October', 'November', 'December', 'January', 'February', 'March', 'April', 'May', 'June')
      AND 
            Fiscal = 2013
      )
OR (
            Month IN ('July', 'August')
      AND
            Fiscal = 2014
      )
AND Total > 0
AND [CSW/TitleXX] = 'T'
GROUP BY Committee
ORDER BY Committee
;

Open in new window


line 4 above is included so you can see the impact of using distinct INSIDE the COUNT() function.
0
 
Saurabh Singh TeotiaCommented:
Quick question the month column where the values are stored is a text column like showed in your example or is their any column which a date format which can be use to filter data for the period..

 September 2013 to August 2014

Again if it's formatted as date..then how are you storing dates in that column and what it's called?
0
 
Saurabh Singh TeotiaCommented:
Again i will write the query by something like this...

select distinct Committee, count(*) 
from AttendanceGrid
where
Where ((Month IN ( 'September', 'October', 'November','December') And Fiscal = 2013)
or 
(Month IN ('January', 'February','March','April', 'May','June','July', 'August') And Fiscal = 2014))
And Total > 0
AND [CSW/TitleXX] = 'T'
Group by Committee
Order by Committee 

Open in new window


Also if you have the one column where you have values stored as date it will be much easy to apply filter for that particular date range...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
al4629740Author Commented:
Thats the problem, there is no date column...

This is an old design.  There is a month column and fiscal year column.  Thats it

Your query does not work.  The count is way too high
0
 
Saurabh Singh TeotiaCommented:
Can you try the query which i gave to you and let me know if this works for you..
0
 
al4629740Author Commented:
It does not work...
0
 
Saurabh Singh TeotiaCommented:
what happens..? it give you incorrect results?? or if it gives you an error?
0
 
Saurabh Singh TeotiaCommented:
My bad didn't realized you have two where in the query..use this one...

select distinct Committee, count(*) 
from AttendanceGrid

Where ((Month IN ( 'September', 'October', 'November','December') And Fiscal = 2013)
or 
(Month IN ('January', 'February','March','April', 'May','June','July', 'August') And Fiscal = 2014))
And Total > 0
AND [CSW/TitleXX] = 'T'
Group by Committee
Order by Committee 

Open in new window

0
 
al4629740Author Commented:
I fixed it before and still gives the wrong results.  There are way too many in the count so it can't be right.
0
 
Saurabh Singh TeotiaCommented:
The query looks right to me..can you pull out sample data for just one month and compare manually..because i don't see any problem in the query...

Also if you need my help in comparison..just paste the data in excel and upload here..as i can help you in doing the same..
0
 
PortletPaulfreelancerCommented:
I should make this point....

GROUP BY creates unique rows

SELECT DISTINCT creates unique rows

do NOT use BOTH in a single query (there is no point, the rows are already unique)
0
 
PortletPaulfreelancerCommented:
Can you share some sample data, for EACH TABLE, and then the "expected result" please.
This approach (data & result) is usually the fastest route to solution.

---------
There appears to be a second related question
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28628463.html

which reveals that there may be more than one table involved

it isn't helpful (to you, or us) if you start multiple questions on the same problem and in fact everyone involved can get confused.
0
 
al4629740Author Commented:
That question has two tables.  Its a separate question...

My guess is you want to figure out how many different people have participated in each committee in that 12 month period. I.e. if the same person is found more than once you still only want a person to be 1

That is correct


Output needs to be something like this

Committee           Count
Agency1                    5
Agency2                  58
Agency3                   33
0
 
PortletPaulfreelancerCommented:
Have you tried what I suggested?

COUNT( DISTINCT [something] )

that "something" could be a contacenation e.g. [last_name]+[first_name]
or it could be an internal but unique identity for each person

there is NOT enough details for me to be precise....  hence you must choose the [something]


------------
btw:
providing  an expected result - without sample data - is not that useful as we cannot reproduce the problem.

supply a sample from each table, only a few rows is needed & private data can be omitted or changed.
0
 
Jeff DarlingDeveloper AnalystCommented:
Is the birthdate a datetime?  Are the times different?  If yes, that explains why the previous query I gave you in the question http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28628463.html didn't produce correct results.  

Changed the query to only look at birth year, month and day.

SELECT A.Committee
	,count(A.ID) AS 'Core Males'
FROM AttendanceGrid A
INNER JOIN tblRegistration R ON A.[ID] = R.[ID]
WHERE (
		(
			A.Month IN ('November','December','January','February','March','April','May','June')
			AND A.Fiscal = 2013
			)
		OR (
			A.Month IN ('July','August','September','October')
			AND A.Fiscal = 2014
			)
		)
	AND R.Combo5 = 'Male'
	AND A.Total > 0
	AND A.[CSW/TitleXX] = 'T'
	AND A.ID in (select min(ID) from AttendanceGrid group by [First Name],[Last Name],year(BirthDate),month(birthdate),day(birthdate))
GROUP BY A.Committee
ORDER BY A.Committee

Open in new window

0
 
PortletPaulfreelancerCommented:
@Jeff note
That question has two tables.  Its a separate question...
ID: 40643842
That other question  is Q_28628463

--------------
@al4629740

Jeff Darling has proposed using  count(DISTINCT A.ID) for question Q_28628463
I have proposed COUNT( DISTINCT [First Name] + [Last Name] + convert(varchar, [BirthDate] ,112) ) as DistinctCount
for this question

i.e. both of us suggest:

COUNT( DISTINCT [something] )

but without data it is impossible for us to give you the exact query.

Have you tried COUNT( DISTINCT .... ) at all?
0
 
Jeff DarlingDeveloper AnalystCommented:
I believe that paul as the correct solution, I saw the post, but did not notice the date conversion 112.  That I believe is important.

PortletPaul2015-03-03 at 19:48:23ID: 40643854
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.