SQL Query Help, Leave System Query

Question

0.00/5 (No votes)

See more:

Good Afternoon.

I have Two Tables that I need to use for my Query.

The Query Must get Each active user.
And From the 'Leave' table get all their leave, split it into the
types of leave from the leave_type column and do an internal sum with the types of
leave so that the end result shows:

Each User and how many leave types they have left for the year.

Relevant Tables/Columns
Table1 - user_detail

user_name (string) <---- Primary key
dept_code (int)
active (true/false)

TAble2 - leave (Cosists of leave entries by all users)

leave_id (string) <---- Foreign key
no_of_working_days (int)
leave_type (string) <----- ('Sick', Vacational', 'Study' etc)

Typical result set would look like:

User---Department---VacationalTOTAL---SickTOTAL---StudyTOTAL
Bob ------9 ----------------- 12 ---------------- 7 ---------------- 3

I am fairly a Noob with hardcore SQL, all my efforts (two of them) have failed.
If somebody could just get me on the right track that would be awesome.

Stuff like, only showing the one person, with all his/her leave but leave also spit into different kinds of leave, and using the no_of_working_days to get the totals.

Thank You.

Posted 26-Mar-12 1:54am

jdogdewit

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kishore Rajendran · Answer 1 · 2012-03-26T02:19:00

Check this,

table leave
*************
leave_id no_of_working_days leave_type

Bob 5 Sick

Bob 2 Vacational

Bob 1 Study

table user_detail
*****************

user_name dept_code active
Bob 9 True

Query
*****

Select leave_id as Name,(select dept_code from user_detail where USER_NAME='Bob') as Department,(select SUM(no_of_working_days) from leave where leave_type='Vacational' and leave_id='Bob')  as  VacationalTOTAL,(select SUM(no_of_working_days) from leave where leave_type='Sick' and leave_id='Bob')  as  SickTOTAL,(select SUM(no_of_working_days) from leave where leave_type='Study' and leave_id='Bob')  as  StudyTOTAL  from leave where leave_id='Bob' group by leave_id

Saral S Stalin · Answer 2 · 2012-03-26T03:35:00

There is one issue in your question. How will you join user_detail
and leave tables? Since you have said that the above answer pulls correct results I assume leave_id ~= user_name. I am posting a new answer since the above approach is not the best way and will not work if you have to pull results for multiple users.

The below example uses PIVOTS and executes much faster.

SQL

--Temp tables
DECLARE @user_detail TABLE (user_name VARCHAR(50), dept_code INT, active BIT)
DECLARE @leave TABLE (user_name VARCHAR(50), no_of_working_days INT, leave_type VARCHAR(50)) -- note that leave_id is changed to user_name.

--Let's have three users
INSERT INTO @user_detail VALUES ('Bob', 9 , 1)
INSERT INTO @user_detail VALUES ('Tracy', 9 , 1)
INSERT INTO @user_detail VALUES ('Maria', 9 , 1)

--Their leaves
INSERT INTO @leave VALUES ('Bob', 5, 'Sick')
INSERT INTO @leave VALUES ('Bob', 2, 'Vacational')
INSERT INTO @leave VALUES ('Bob', 1, 'Study')
INSERT INTO @leave VALUES ('Tracy', 2, 'Study')
INSERT INTO @leave VALUES ('Maria', 1, 'Sick')
INSERT INTO @leave VALUES ('Maria', 6, 'Vacational')
INSERT INTO @leave VALUES ('Maria', 9, 'Study')
INSERT INTO @leave VALUES ('Tracy', 8, 'Vacational')


--Query to pull results using PIVOT
SELECT user_name, dept_code,[Sick], [Vacational], [Study]
FROM
(SELECT l.user_name, dept_code, no_of_working_days, leave_type
FROM @leave l
JOIN @user_detail ud ON l.user_name = ud.user_name) AS Source
PIVOT (MAX(no_of_working_days) FOR leave_type IN ([Sick], [Vacational], [Study])) as p

Result

SQL

user_name dept_code Sick  Vacational  Study
--------- --------- ----- ----------- -----
Bob        9         5    2           1
Maria      9         1    6           9
Tracy      9         NULL 8           2

SQL Query Help, Leave System Query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0