• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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
0
al4629740
Asked:
al4629740
  • 3
  • 2
  • 2
2 Solutions
 
QlemoBatchelor, 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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now