asked on

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

Saurabh Singh Teotia

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?

Saurabh Singh Teotia

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...

al4629740

ASKER

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

Saurabh Singh Teotia

Can you try the query which i gave to you and let me know if this works for you..

al4629740

ASKER

It does not work...

Saurabh Singh Teotia

what happens..? it give you incorrect results?? or if it gives you an error?

Saurabh Singh Teotia

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

al4629740

ASKER

I fixed it before and still gives the wrong results. There are way too many in the count so it can't be right.

Saurabh Singh Teotia

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..

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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)

PortletPaul

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
https://www.experts-exchange.com/questions/28628463/distinct-group-of-values.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.

al4629740

ASKER

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

PortletPaul

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.

Jeff Darling

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

PortletPaul

@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?

Jeff Darling

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