SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

vvasude2
Starting Member

5 Posts

Posted - 2013-04-21 : 14:32:38

Hi,

I have the following query that i need help with.

I have table with the following fields

employeeid jobtype hoursworked dateworked

I need a query that will result in

1)employeeid 2)TOTAL_HOURS_WORKED_PER_JOB_PER_EMPLOYEE 3) TOTAL_HOURS_WORKED_PER_JOB_PER_EMPLOYEE_FOR_A_PARTICULAR_DATERANGE

I can find the total hours worked per jobtype using groupby but how do i also show the total hours worked perjob type per employee for a particular date range..any help would be appreciated

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-21 : 17:23:25

how about using where clause and specifying the date range.
You can also try using "group by grouping sets.."
If you need more help, please post your DDL & data

vvasude2
Starting Member

5 Posts

Posted - 2013-04-21 : 18:36:45

Table Name: EMPL_History
Fields:

-> EMPL_ID INT
-> NTWK - INT
-> WORK_DATE - DATE
-> WORK_HOURS - FLOAT

Sample:

EMPL_ID NTWK WORK_DATE WORK_HOURS

1234 1111 01/02/2013 4.5
1234 1111 02/02/2013 4.5
1234 1111 03/02/2013 4.5
4321 3333 02/02/2012 8
4321 3333 05/01/2013 9

Sample output.

EMPL_ID NTWK WORK_HOURS(DATE> 02/02/2013) WORKHOURS
1234 1111 9 13.5
4321 3333 17 17

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-21 : 20:28:26

Try something like this:

[CODE]

select O.EMPL_ID, O.NTWK,
(Select SUM(O1.WORK_HOURS) from EMPL_History as O1 where O.EMPL_ID = O1.EMPL_ID and O1.WORK_DATE > '20130202'
group by O1.EMPL_ID, O1.NTWK) as HOURSWORKEDAFTER_FEB_03_2012,
SUM(O.WORK_HOURS) as HOURSWORKED from EMPL_History as O group by O.EMPL_ID, O.NTWK;

[/CODE]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 00:44:49

--Alternate is:

select O.EMPL_ID, O.NTWK, 
	SUM(CASE WHEN WORK_DATE > '20130202' THEN WORK_HOURS END) HOURSWORKEDAFTER_FEB_03_2012,
	SUM(WORK_HOURS) HOURSWORKED
from EMPL_History as O 
group by O.EMPL_ID, O.NTWK;

--
Chandu

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-22 : 10:52:05

That is better.
Thanks Chandu.

vvasude2
Starting Member

5 Posts

Posted - 2013-04-22 : 17:58:30

Thanks guys!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 23:58:34

quote:
Originally posted by MuMu88

That is better.
Thanks Chandu.

Welcome

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 23:58:52

quote:
Originally posted by vvasude2

Thanks guys!

welcome

--
Chandu

rmcox81
Starting Member

3 Posts

Posted - 2013-04-23 : 01:12:33

What is wrong with this statement:

SELECT DISTINCT AVG(numeric_grade) AS Grade, TRUNC(numeric_grade)
FROM grade
WHERE section_id = '106'

I get the average as 85.45454545454545454545 I want the TRUNC Function to make it 85.

rmcox81
Starting Member

3 Posts

Posted - 2013-04-23 : 01:14:36

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 01:18:48

use CAST() function...
SELECT CAST(85.45454545454545454545 AS DEC(3,0))

--
Chandu

vvasude2
Starting Member

5 Posts

Posted - 2013-04-25 : 10:55:20

I have a follow up question..i have the following tables:

Table1:

NETWORK HOURSWORKED DATEWORKED(mm/dd/yyyy)
A1 1 01/02/2012
A2 2 02/03/2012
A1 3 03/04/2013

Table2:
NETWORK HOURSWORKED DATEWORKED
B1 1 01/02/2012
A1 2 02/03/2012

RESUTLT TABLE

NETWORK HOURSWORKEDAFTERDATE>=FEB HOURWORKEDFORTHEENTIREYEAR
A1 5 6
A2 4 4
B1 0 1

Basically i need to merge these 2 tables and create a VIEW. 2nd column has all the hours summed up if any from feb. 3rd column has all the values summed up from anydate. I am using MS SQL server..Thanks

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 13:23:18

This might work:
[CODE]

SELECT T1.network, SUM(CASE WHEN T1.dateworked > '20120203' THEN T1.hrworked ELSE 0 END) AS HoursworkedAfterFeb,
SUM(T1.hrworked) as TotalHoursWorked
FROM (
SELECT network, dateworked, hrworked from Table1
UNION
SELECT network, dateworked, hrworked from Table2) AS T1
group by network;

[/CODE]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 00:06:55

@MuMu88
--small typo is that OP asked for FEB onwards count
SUM(CASE WHEN T1.dateworked >= '20120201' THEN T1.hrworked ELSE 0 END) AS HoursworkedAfterFeb,

@vvasude2,
How come the sum for NETWORK 'A2' is 4 as per your sample data?

--
Chandu

vvasude2
Starting Member

5 Posts

Posted - 2013-04-30 : 19:06:51

Sorry, that is a mistake. I will try the query out. Thanks guys!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-02 : 00:55:59

quote:
Originally posted by vvasude2

Sorry, that is a mistake. I will try the query out. Thanks guys!

any way you sorted out..
welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources