Use of Distinct with dates and Use of Group By Clause

I have an attendance table in SQL Server 2012 named tblOrgHours. (attached)  This is where I log all my attendance for different agencies in tracking how many hours each activity has been performed each day by different kids.   The RegID in that same table is relational with the student information found in table tblOrgRegistrations. (attached)  I need to create an sql query that will output the number of unique days of activity for each student during a specified time frame.  Each day containing greater than 0 hours is considered a day of activity.  

Here is the code I setup to get this data described above.  Am I properly using the Distinct clause with ActivityDAte to attain what I described.  Also, I am collecting other columns that concern me.  I am not entirely familiar with the Group By clause, but am I using it correctly, once again, to get the desired output.  Thus far, my output seems good, but I'd like experts to take a look at my query/syntax.

select  H.Agency,
		h.SubGroup
	    ,Count(distinct H.ActivityDate) as [# of days]
		,r.LastName
		,r.FirstName
		,r.BirthDate
		,r.Grade
		,r.StudentID

from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID

where H.[Hours] > 0
and h.fiscal = 2018
And h.ActivityDate >= '7/1/17' And h.ActivityDate <= '12/31/17'

group by H.Agency,h.SubGroup, r.LastName,r.FirstName,r.BirthDate,r.Grade,r.StudentID
Order by 1,4,5

Open in new window

tblOrgHours.xlsx
tblOrgRegistrations.xlsx
al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
With dates it is usually more safe to use the equal sign only with one boundary. Date fields contain a  time portion most of the time, so I would check
And h.ActivityDate >= '7/1/17' And h.ActivityDate < '01/01/18'

Open in new window

Date constants are better to be entered in yyyymmdd format when using MSSQL.

Order by with position numbers is prone to errors and more difficult to read. Better to use column names.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
As Qlemo pointed out, columns of data type DATETIME can continue a time portion. This means that you also need to check how ActivityDate is defined. When it's not of the type DATE, then also COUNT(DISTINCT ActivityDate) may return not what you want. In this case you may need a COUNT(DISTINCT CAST(ActivityDate AS DATE)) instead.
0
al4629740Author Commented:
If ActivityDate in the table is of type datetime, then would this still be necessary?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ste5anSenior DeveloperCommented:
Then you need to check the data. Does any row exists which has a time part?

SELECT *
FROM tblOrgHours 
WHERE ActivityDate != CAST(ActivityDate AS DATE);

Open in new window


When it returns rows, then you need the CAST in the DISTINCT.

Otherwise you need to check whether a CHECK constraint exists on that column to prohibit a time portion. If such a constraint exists, then you don't need it. But then you should consider changing the data type to DATE, if allowed by the model.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The DateTime type always keeps a time portion. It might be 0:00 if unset, but it is still there. And that is what ste5an meant - if there are times different from 0:00 you'll have to take them into account.
1
al4629740Author Commented:
I stand corrected.  My table has Date as the datatype.  Sorry for the confusion.  So how does this change your advice?  I'm assuming Cast(ActivityDate as date) is unnecessary as this point.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes. You don't need a cast/convert, and you can leave yor where condition. You could even use between '7/1/17' and '12/31/17' .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.