Calculate daily meeting times for each employee

Question

Calculate daily meeting times for each employee

SSC Veteran

Points: 268

January 29, 2019 at 4:29 am

Hi
I have 3 columns (Meeting types) (datetime) and 3 columns with meeting type durations (float).
I also have an employee column with employeeID.
Is it possible to find total daily meeting times for an employee?
My current table looks like this:

ID	Employee	MeetingType1	MeetingType2	MeetingType3	Mtg1Time	Mtg2Time	Mtg3Time
1	X1	21/06/2019	30/06/2019	02/08/2019	60	15	30
2	Y2	25/07/2019			60	0	0
3	X1		21/06/2019			15
4	X1			21/06/2019			30
5	Y2		25/07/2019	25/07/2019
6	Y2
7	Y3

Desired result:
Employee           Date                      MeetingType1 MeetingType2 MeetingType3 Total Time (mins)
X1                           21/06/2019         Yes                         Yes                         Yes                         105
X1                           30/06/2019         No                          Yes                         No                          15

I have looked into pivot/unpivot (haven’t been able to visualise the solution through it). I am unable to get to the desired result. I have also tried cross apply but I don’t thing that will work.

Any help would be really appreciated.

Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

A little normalization goes a long way. If a single record describes ONE meeting, then this is trivial.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 2

Is it just the three columns? You can use CASE to check for a meeting and calculate a time, then sum those up. If no meeting, return a 0 to sum.

Not sure you need more, but you'd have to provide some expected results. Actually, you ought to be using testing here, with specific cases set up and the results per-calculated to be sure you aren't forgetting something.

naveed.tuf SSC Veteran Points: 268 More actions · Answer 3

pietlinden - Tuesday, January 29, 2019 8:19 AM
A little normalization goes a long way. If a single record describes ONE meeting, then this is trivial.

Hi pietlinden,

The table has an entry (date) for every type of meeting but in one record there can be 3 different dates and times.
There are different dates under MeetingType1, MeetingType2 and MeetingType3 and all 3 columns are fully populated.

Thank you

naveed.tuf SSC Veteran Points: 268 More actions · Answer 4

Steve Jones - SSC Editor - Tuesday, January 29, 2019 8:55 AM
Is it just the three columns? You can use CASE to check for a meeting and calculate a time, then sum those up. If no meeting, return a 0 to sum.
Not sure you need more, but you'd have to provide some expected results. Actually, you ought to be using testing here, with specific cases set up and the results per-calculated to be sure you aren't forgetting something.

Hi Steve,
There are 4 columns, I was hoping for a results as follows:

Desired result:
Employee Date MeetingType1 MeetingType2 MeetingType3 MeetingType4 Total Time (mins)
X1 21/06/2019 Yes Yes Yes No 105
X1 30/06/2019 No Yes No Yes 15

Thank you

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 5

What data is in the empty spaces? This isn't Excel. Either a null, blank space, something is there.

naveed.tuf SSC Veteran Points: 268 More actions · Answer 6

Steve Jones - SSC Editor - Tuesday, January 29, 2019 9:32 AM
What data is in the empty spaces? This isn't Excel. Either a null, blank space, something is there.

Hi Steve,

Yes, apologies, the other spaces all contain dates also, All Meeting type columns have dates in them.

Thanks

naveed.tuf SSC Veteran Points: 268 More actions · Answer 7

naveed.tuf - Tuesday, January 29, 2019 9:37 AM
Steve Jones - SSC Editor - Tuesday, January 29, 2019 9:32 AM
What data is in the empty spaces? This isn't Excel. Either a null, blank space, something is there.
Hi Steve,
Yes, apologies, the other spaces all contain dates also, All Meeting type columns have dates in them.
Thanks

My thinking of this was that I needed a query that would:

Choose the lowest date from all the dates in the meeting types columns, find the meetingtype and then the duration associated with. then check if that same date occurs again anywhere and find the meetingtype and the duration for this and add it to previous found duration time....and so on...
All this to be grouped by each employee and duration summed to give a daily total.

Hope that makes sense...
I have been given this data and I am expected to automate the process as much as possible so my options are pretty limited

Thank you again for your help

pietlinden SSC Guru Points: 63503 More actions · Answer 8

How about
CREATE TABLE Meeting (
MeetingID INT IDENTITY PRIMARY KEY,
MeetingType VARCHAR(20) NOT NULL,
StartDateTime DATETIME,
EndDateTime DATETIME...)

And then maybe you have an Attendees table? (MeetingID, PersonID)

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 9

Your results are incomplete. You have more than 2 dates in the source you've listed. That's what I mean by calculating all results. Don't pick a few and think you've solved something.

Also, if you say all rows contain a date, what date? Or are there NULLs.

You can tackle this a few ways. You can use UNION to normalize this data, or you could use CTEs to get three sets of data back that can be joined and grouped.

drew.allen SSC Guru Points: 76985 More actions · Answer 10

naveed.tuf - Tuesday, January 29, 2019 9:42 AM
My thinking of this was that I needed a query that would:
Choose the lowest date from all the dates in the meeting types columns, find the meetingtype and then the duration associated with. then check if that same date occurs again anywhere and find the meetingtype and the duration for this and add it to previous found duration time....and so on...
All this to be grouped by each employee and duration summed to give a daily total.
Hope that makes sense...
I have been given this data and I am expected to automate the process as much as possible so my options are pretty limited
Thank you again for your help

Yes, it makes sense in that I understand what you are thinking about doing, but NO, it does not make sense in that it's not the best approach.

You want to "unpivot" the data, but not using MS's limited UNPIVOT function. You should be using the CROSS APPLY/Table Value Constructor method.

SELECT * FROM <your table> CROSS APPLY ( VALUES('MeetingType1', MeetingType1, Mtg1Time), ('MeetingType2', MeetingType2, Mtg2Time), <etc.>) mt(MeetingType, MeetingDate, MeetingTime)

You're running into issues, because your table is denormalized. This will normalize your table and make it much easier to work with. Of course, you'll need to "pivot" it again to get your final results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

naveed.tuf SSC Veteran Points: 268 More actions · Answer 11

naveed.tuf

SSC Veteran

Points: 268

January 29, 2019 at 11:14 am

#2020464

Thank you ALL for your help!!