Query to track hourwise,daywise,weekwise,Monthwise and yearwise data

Question

Query to track hourwise,daywise,weekwise,Monthwise and yearwise data

abhas

SSCarpal Tunnel

Points: 4681
More actions
August 4, 2016 at 1:20 am

#312278

Hi folks,
I am having tblActivity where we are tracking all login details of employee.
Main columns are
EmpID,ActivityID,ActivityDate
From this, user want a query which will show number of counts as below whose ActivityID = 5.
It should have capability of showing count of Employee who logged for whole day (with slot of every hour), for whole week (with slots of days), for whole Month (with slots of days/weeks) and for whole year (with slots of months). ActivityDate is a datetime filed.
Could you please help me how can i achieve this?
Thanks,
Abhas.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

abhas (8/4/2016)
It should have capability of showing count of Employee who logged for whole day (with slot of every hour), for whole week (with slots of days), for whole Month (with slots of days/weeks) and for whole year (with slots of months). ActivityDate is a datetime filed.
Could you please help me how can i achieve this?

I love reporting questions. You start by asking questions 🙂

What does "every hour" mean? Midnight to midnight? 9am to 5pm?

What does "whole week" mean? Sunday-Saturday? Monday-Friday? Monday-Saturday? Do holidays count?

Whole month - slots of weeks: How do you handle weeks that span month's end?

Whole year. Do we only count people who worked every day of every month for the year? Most days of the year? What about people who were hired in the middle of the year?

Activity date. Presumably this means when an activity started. When did it end? Is there another column that specifies the duration of the activity?

---------------

Just to get you started, you can apply the DATEPART() function to your activity date to find out what hour, day, week, month, or year it belongs to. From there, you would create a summary query (GROUP BY) to get totals based on the DATEPART() result.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Please provide sample data and expected results as listed in the first link in my signature.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

This should be handled in the front end, the query should only return the values aggregated in the smallest unit fo measure (hours in this case, apparently).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63506 More actions · Answer 4

Doing this in PowerBI or PowerPivot would be really simple if you build a quick data warehouse with dimensions for Time/Date and Employee and a few measures