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
attempt to solve before viewing the solution.
Remember, there are multiple ways to reach the same answer. The most important thing is that you derive the correct answer
now. With that said, Let's go ahead and take a look at the requirements document.
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.
Senior Programmer instructions, columns required.
Department I D Department description
Number of active employees assigned to each department.
Use the employee position table. One to many relationship occurrence of no on column end date. Any cased employee is still active,
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,
meaning there is no end date for that position.
And then finally, number of hours from time card
again on Lee Active Employees. We don't want the hours being
collected from inactive employees,
so tables required
AP timecard e p A. Employees be account
BP employees position an e p department.
We'll see we have some notes
for this query. It's likely you'll need table expressions to make the data flow more manageable.
Yeah, I think the active employees will make an excellent table expression.
Tried to make a table expression for active employees. Yep, I mentioned that again.
Then connect that to a department query that utilizes aggregation to generate account.
You also need to include the timecard hours so that managers can see how many hours are being used in each department.
Make sure to group by when you need distinct results.
We have some bullet points employee I D Maps to be account across tables to keep that in mind. That's the key connection
Department I. D. Is located on the E p A Employees table with respect to employees assignment
Focus on the department i D column as the overall report deals with statistics of the departments.
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.
And, um so let's get started.
So I know we need active employees.
So a good way to handle these type of problems is to break things apart
and maybe use
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
active underscore employees
and let's start righting to query in here. So we're select star
E P A employees
and I'm going to join
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.
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
this E p employee position. If I open this up,
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.
I believe on the test data. Everyone that's on here is still active,
but because this record of who ever this is it's the CEO for the position I d.
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,
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
based on the employee's. So, for example, if you're represented on this table and you get a promotion,
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.
So we're going to use that to identify our are active employees.
So join E P employees
and call it pos for position
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
dot the account I d
equals positioned. I think it's employee I d. Over here.
I'm gonna say where
position dot end date
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.
We're gonna get the employees,
company i d. Let's put that before the account I d.
Company I D.
And let's bring back the department ideas. Well, because we have that here, and that's kind of the focus of this report.
company i d be account i d department I d active employees, and it is no. Now there's an issue here.
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
that we do get unique records.
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,
but it's a good happy to get used to using the group by because you use it with all the aggregate functions. Anyway,
let's go ahead and test this.
Flexed are from active
Okay, It looks like I got some results back,
so we have the active employees.
Another component we need is the timecard hours,
so let's go ahead and start a another table expression.
What time card? Hours as
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,
So time card
P time card
call that TC
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
employees a e
on a company. I d
equals t See that company idea on
t see that it's an employee. I d equals e
that be the account I d.
so I need to get those statistics. I'm gonna need to get rid of the star, so TC dot
a company idea
on. Then we will get the TC. That's
department I D.
No, no, not TC Empty that
for active employees.
department i d.
And then let's some
the TC that
hours or time spent,
And then we will group by
these two columns,
And let let's make sure that's making sense. So select star from
That's another score
says I have a mistake. I forgot a comma
All right. So we have our departments
and our hours
That looks right.
Okay. So far, we have our active employees as a temple expression.
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
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.
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,
uh, right arm a inquiry here. Start with a select star
E P Department.
And, of course, if we're interested in knowing what's on the table, we can always just look it up.
Of course we got a
spell it right?
U P. Department. There we go.
So we can see we have these departments on our definition table. Four departments,
not too many.
So let's go ahead.
I select from it.
Gonna alias that with d B t d e p T.
Then I'm gonna left join.
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.
So left join
active employees because we're gonna need that count. So we're gonna need that active employees table expression
on department dot
company i d equal e m p. That company i d
that department I d
equals e and P
dot department idea.
so let's go ahead and,
give it a that count company idea
department dot department i d
and the apartment dot description.
And now let's count
G count I d.
And, of course, to use an aggregate function, we have to group by.
So let's go ahead and get this in there.
Let's run this and see what it looks like.
All right, So we got our active employees counts and administration got four and consultant got 10.
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
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.
That's the kind of benefit you get with these or the reason management looks for these kind of reports.
So we're not done yet. We need the hours that are being used per department by active employees,
so we actually need to join the table.
I need to bring in the time Cardo hours. So left join
t see on t c dot company i d
harman dot company idea
and T c That
apartment I d
e p t dot department idea.
And then we need Teoh
t c dot hours
alias this as ours. It needs a name
as department hours
on as department
it's going and run this. Make sure it works,
All right, I'm seeing something I know that nobody wants to see, and that's knows.
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,
that becomes no.
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
now. There's two places we could try to put that we could try to put that on the outside of the some,
or we could try to put it on the inside of this some. Now which one is the better choice?
Well, it's the inside of the some
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.
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
Any time we get no, you want to get a zero Instead,
Let's go ahead and run that and see what we get.
All right. Looks like we got zeros. So that's fine.
And it looks like we are done with this challenge.
So what were the interesting parts of this challenge? While we needed to use table expressions to make it more manageable,
we used one table expression and get the active employees.
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,
we joined the previous two table expressions on the department i ds
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.
So that completes this challenge. No, to see in the next lesson. Thank you