al4629740
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','Jan uary', '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
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','Jan
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
Again i will write the query by something like this...
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...
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
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...
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
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
Can you try the query which i gave to you and let me know if this works for you..
ASKER
It does not work...
what happens..? it give you incorrect results?? or if it gives you an error?
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
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.
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..
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
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.
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.
ASKER
That question has two tables. Its a separate question...
That is correct
Output needs to be something like this
Committee Count
Agency1 5
Agency2 58
Agency3 33
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
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.
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.
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-exchang e.com/Data base/MS-SQ L-Server/Q _28628463. html didn't produce correct results.
Changed the query to only look at birth year, month and day.
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
@Jeff note
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?
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?
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
PortletPaul2015-03-03 at 19:48:23ID: 40643854
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?