SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vvasude2
Starting Member

USA
5 Posts

Posted - 04/21/2013 :  14:32:38  Show Profile  Reply with Quote
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

Edited by - vvasude2 on 04/21/2013 14:33:30

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/21/2013 :  17:23:25  Show Profile  Reply with Quote
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
Go to Top of Page

vvasude2
Starting Member

USA
5 Posts

Posted - 04/21/2013 :  18:36:45  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/21/2013 :  20:28:26  Show Profile  Reply with Quote
Try something like this:



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;

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/22/2013 :  00:44:49  Show Profile  Reply with Quote
--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

Edited by - bandi on 04/22/2013 00:45:19
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/22/2013 :  10:52:05  Show Profile  Reply with Quote
That is better.
Thanks Chandu.
Go to Top of Page

vvasude2
Starting Member

USA
5 Posts

Posted - 04/22/2013 :  17:58:30  Show Profile  Reply with Quote
Thanks guys!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/22/2013 :  23:58:34  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

That is better.
Thanks Chandu.


Welcome

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/22/2013 :  23:58:52  Show Profile  Reply with Quote
quote:
Originally posted by vvasude2

Thanks guys!


welcome

--
Chandu
Go to Top of Page

rmcox81
Starting Member

3 Posts

Posted - 04/23/2013 :  01:12:33  Show Profile  Reply with Quote
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.
Go to Top of Page

rmcox81
Starting Member

3 Posts

Posted - 04/23/2013 :  01:14:36  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/23/2013 :  01:18:48  Show Profile  Reply with Quote
use CAST() function...
SELECT CAST(85.45454545454545454545 AS DEC(3,0))

--
Chandu
Go to Top of Page

vvasude2
Starting Member

USA
5 Posts

Posted - 04/25/2013 :  10:55:20  Show Profile  Reply with Quote
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

Edited by - vvasude2 on 04/25/2013 10:57:21
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/25/2013 :  13:23:18  Show Profile  Reply with Quote
This might work:


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;

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/26/2013 :  00:06:55  Show Profile  Reply with Quote
@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
Go to Top of Page

vvasude2
Starting Member

USA
5 Posts

Posted - 04/30/2013 :  19:06:51  Show Profile  Reply with Quote
Sorry, that is a mistake. I will try the query out. Thanks guys!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/02/2013 :  00:55:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000