Module 6 Challenge Part 2

00:00

I welcome back to Model six. This is less than 6.10 challenge to. And if you went through the previous challenge, this slide is mostly the same is that one challenges air based on real world requests. Read the requirements Document

00:15

attempt to solve before viewing the solution.

00:17

Remember, there are multiple ways to reach the same answer. The most important thing is that you derive the correct answer

00:24

now. With that said, Let's go ahead and take a look at the requirements document.

00:28

All right, here we are at the course Challenge document. So let's go ahead and take a look at this. Utilizing the axiomatic a database. Generate the following report. Management needs a query that will expose department statistics. The lead programmers instructions Blow will guide you on the requirements.

00:44

Senior Programmer instructions, columns required.

00:47

Department I D Department description

00:51

Number of active employees assigned to each department.

00:54

Use the employee position table. One to many relationship occurrence of no on column end date. Any cased employee is still active,

01:03

so that sounds like a good area to do a table expression. It would seem that maybe one of our table expressions might be active employees and active employees are identified by having an open position. And that's identified on the position table by having a no foreign end date,

01:23

meaning there is no end date for that position.

01:26

And then finally, number of hours from time card

01:30

again on Lee Active Employees. We don't want the hours being

01:34

collected from inactive employees,

01:37

so tables required

01:38

AP timecard e p A. Employees be account

01:44

BP employees position an e p department.

01:48

We'll see we have some notes

01:49

for this query. It's likely you'll need table expressions to make the data flow more manageable.

01:56

Yeah, I think the active employees will make an excellent table expression.

02:00

Tried to make a table expression for active employees. Yep, I mentioned that again.

02:06

Then connect that to a department query that utilizes aggregation to generate account.

02:12

You also need to include the timecard hours so that managers can see how many hours are being used in each department.

02:17

Make sure to group by when you need distinct results.

02:23

We have some bullet points employee I D Maps to be account across tables to keep that in mind. That's the key connection

02:30

Department I. D. Is located on the E p A Employees table with respect to employees assignment

02:37

Focus on the department i D column as the overall report deals with statistics of the departments.

02:45

Okay, so if you like to take a shot at this, go ahead and pause the video in the next section. We will be working through the solution. All right, let's work through that solution. Now. I've copied the some of the data from that document into the the D Beaver pain, so I can easily look at it and see what the requirements were.

03:01

And, um so let's get started.

03:05

So I know we need active employees.

03:08

So a good way to handle these type of problems is to break things apart

03:13

and maybe use

03:15

table expressions to separate your areas of concern. So I know I need active employees, so we'll start with that so we'll start with with

03:22

active underscore employees

03:27

as

03:28

brackets

03:30

and let's start righting to query in here. So we're select star

03:37

from

03:39

E P A employees

03:43

and

03:46

and I'm going to join

03:51

the position table because the way that in a lot of applications that you can tell if an employee's active or terminated is by positions table. If they don't have an active position, then they're probably not working. Course you would need to check with how you use is used application to confirm it. That's true. But in this case, we're gonna assume that that's true.

04:11

And what we're gonna do is we're checked the positions table and make sure that there is a null in the end date. So that looks like

04:20

this E p employee position. If I open this up,

04:25

I can see we have a start day. We have a start reason we have an end date. This end date is what we are interested in focusing on.

04:31

I believe on the test data. Everyone that's on here is still active,

04:36

but because this record of who ever this is it's the CEO for the position I d.

04:43

They have no end date, which means that they started on 2012 12 31 and they're still working. And there's no end in sight. So they're still active now. If there was an end date filled in here of 2013 January 1st,

04:58

it would look as if that person had worked one or two days and then they don't work here anymore cause they ended on that date. Now this is a one to many relationship

05:09

based on the employee's. So, for example, if you're represented on this table and you get a promotion,

05:15

your old record will get an end date when your old position ended and it you'll get a new record with your same employee i. D. Number that has a new start date and no for the end day. And that's how this application would handle promotions. And I've seen it handled the same way in other applications as well.

05:31

So we're going to use that to identify our are active employees.

05:41

So join E P employees

05:44

position

05:46

and call it pos for position

05:48

on a pos dot company I d. That's the equal. The company idea is the easy one, because that's just always part of the composite key

05:57

and

05:59

dot the account I d

06:01

equals positioned. I think it's employee I d. Over here.

06:06

I'm gonna say where

06:11

position dot end date

06:14

is no.

06:16

And then we're gonna get to very specific. Actually, we're gonna get three very specific columns. We're gonna get the employees that be account I d.

06:25

We're gonna get the employees,

06:28

not

06:30

company i d. Let's put that before the account I d.

06:33

Company I D.

06:36

And let's bring back the department ideas. Well, because we have that here, and that's kind of the focus of this report.

06:45

So

06:46

company i d be account i d department I d active employees, and it is no. Now there's an issue here.

06:55

We really should have the possibility of getting multiple returns on Knoll because an employee should only have one and date. That is no, but we are not going to chance that we're just going to group by and make sure

07:10

that we do get unique records.

07:15

You know, you commonly see some programmers used the word distinct to group or it doesn't group by, but it's ah, away Teoh make the records distinct,

07:24

but it's a good happy to get used to using the group by because you use it with all the aggregate functions. Anyway,

07:30

let's go ahead and test this.

07:32

Flexed are from active

07:35

employees.

07:41

Run.

07:44

Okay, It looks like I got some results back,

07:46

so we have the active employees.

07:49

Another component we need is the timecard hours,

07:55

so let's go ahead and start a another table expression.

08:00

What time card? Hours as

08:03

the brackets.

08:07

And then let's go ahead and just select star for now. What tables do I need? Well, I definitely need the time card table,

08:16

So time card

08:18

P time card

08:20

call that TC

08:22

and I only one active employees. I don't want the time card data of inactive employees, so I'm gonna join to that active employees table expression I have above so join active

08:37

employees a e

08:39

on a company. I d

08:46

equals t See that company idea on

08:50

t see that it's an employee. I d equals e

08:56

that be the account I d.

09:01

All right,

09:05

so I need to get those statistics. I'm gonna need to get rid of the star, so TC dot

09:11

a company idea

09:15

on. Then we will get the TC. That's

09:20

department I D.

09:22

No, no, not TC Empty that

09:26

or a

09:28

for active employees.

09:30

That

09:31

department i d.

09:37

And then let's some

09:41

the TC that

09:45

hours or time spent,

09:50

all right.

09:50

And then we will group by

09:54

these two columns,

09:58

right?

10:01

And let let's make sure that's making sense. So select star from

10:07

time

10:09

card

10:11

hours.

10:13

That's another score

10:16

run this

10:18

says I have a mistake. I forgot a comma

10:22

Go.

10:24

All right. So we have our departments

10:28

and our hours

10:31

That looks right.

10:35

Okay. So far, we have our active employees as a temple expression.

10:39

Then in our time card hours, we're going to the time card table. We're connecting up our active employees that we that we formed in a table expression above

10:48

to make sure that we only get the data of active employees because we're focused on the department were including the department I d. As the aggregating pivot of some function.

11:01

So what do we need to do now? Well, we need to bring the department table, and and I don't think we need any more table expression. So let's just go ahead and,

11:11

uh, right arm a inquiry here. Start with a select star

11:16

from

11:18

E P Department.

11:20

And, of course, if we're interested in knowing what's on the table, we can always just look it up.

11:26

Of course we got a

11:28

spell it right?

11:31

Maybe

11:35

U P. Department. There we go.

11:39

So we can see we have these departments on our definition table. Four departments,

11:43

not too many.

11:46

So let's go ahead.

11:48

I select from it.

11:50

Gonna alias that with d B t d e p T.

11:54

Then I'm gonna left join.

11:56

And this is so that if I have departments on that department definition table that aren't representing and represented in the timecard anywhere, they will still come back with a zero, as opposed to not coming back at all.

12:11

So left join

12:16

active employees because we're gonna need that count. So we're gonna need that active employees table expression

12:22

on department dot

12:24

company i d equal e m p. That company i d

12:30

and departments

12:33

that department I d

12:35

equals e and P

12:37

dot department idea.

12:41

All right,

12:43

so let's go ahead and,

12:45

uh,

12:46

give it a that count company idea

12:54

department dot department i d

12:58

and the apartment dot description.

13:01

And now let's count

13:03

Count

13:07

P that

13:09

G count I d.

13:13

And, of course, to use an aggregate function, we have to group by.

13:18

So let's go ahead and get this in there.

13:24

Let's run this and see what it looks like.

13:28

All right, So we got our active employees counts and administration got four and consultant got 10.

13:33

And sales. We got the neat thing about a report like this too is that wherever it was being rendered, whenever they added new employees, you would see new counts poppin

13:45

so they would see. Oh, Consulting Consulting added to people today, I can see because I remember yesterday it was 10. Today it's 12.

13:52

That's the kind of benefit you get with these or the reason management looks for these kind of reports.

14:00

So we're not done yet. We need the hours that are being used per department by active employees,

14:07

so we actually need to join the table.

14:09

I need to bring in the time Cardo hours. So left join

14:15

timecard hours

14:22

t see on t c dot company i d

14:26

equals

14:26

harman dot company idea

14:30

and T c That

14:31

apartment I d

14:35

equals

14:37

e p t dot department idea.

14:41

And then we need Teoh

14:46

some

14:50

t c dot hours

14:52

alias this as ours. It needs a name

14:58

as department hours

15:03

on as department

15:07

employees

15:09

count.

15:09

Come on,

15:11

it's going and run this. Make sure it works,

15:16

All right, I'm seeing something I know that nobody wants to see, and that's knows.

15:22

So the reason that's happening is that you probably have some active employees that have no hours entered and any number you add to annul and sequel becomes no. So if I do know plus five,

15:35

that becomes no.

15:37

So what we want to do is that when an employee has no hours and we're summoning them together, we want instead of no, we want that knoll to become a zero. So there's a specific function that we need, and that's the police function. So let's go ahead and insert that

15:52

now. There's two places we could try to put that we could try to put that on the outside of the some,

15:56

or we could try to put it on the inside of this some. Now which one is the better choice?

16:02

Well, it's the inside of the some

16:03

because what will happen is on the inside, you're gonna get you No, no. Plus five plus five plus knoll, and you're gonna end up with no. And then police on the outside is gonna change that to zero.

16:17

Now, if you put it on the inside and you have five plus zero plus five plus zero, you're gonna end up with 10. So it's gonna be you end up with 10 or you end up with zero so the more accurate place to place that is on the inside

16:32

police

16:33

Any time we get no, you want to get a zero Instead,

16:37

Let's go ahead and run that and see what we get.

16:41

All right. Looks like we got zeros. So that's fine.

16:45

And it looks like we are done with this challenge.

16:49

So what were the interesting parts of this challenge? While we needed to use table expressions to make it more manageable,

16:57

we used one table expression and get the active employees.

17:02

We used the another table expression to get the timecard hours, and we focused on the department I d in both table expressions because the primary report request focused on department statistics. So that made sense. And then, on the final query,

17:18

we joined the previous two table expressions on the department i ds

17:22

to get those departments statistics. And the next interesting thing that we needed to do was be sure to use the coal lease function to replace Knoll with zero so that ah, people on the front end, we're looking at zeros instead of Knowles.