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 dateworkedI need a query that will result in1)employeeid 2)TOTAL_HOURS_WORKED_PER_JOB_PER_EMPLOYEE 3) TOTAL_HOURS_WORKED_PER_JOB_PER_EMPLOYEE_FOR_A_PARTICULAR_DATERANGEI 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_HistoryFields: -> EMPL_ID INT -> NTWK - INT -> WORK_DATE - DATE -> WORK_HOURS - FLOATSample:EMPL_ID NTWK WORK_DATE WORK_HOURS1234 1111 01/02/2013 4.51234 1111 02/02/2013 4.51234 1111 03/02/2013 4.54321 3333 02/02/2012 84321 3333 05/01/2013 9Sample output. EMPL_ID NTWK WORK_HOURS(DATE> 02/02/2013) WORKHOURS1234 1111 9 13.54321 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) HOURSWORKEDfrom 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 gradeWHERE 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
|
What is wrong with this statement:SELECT DISTINCT AVG(numeric_grade) AS Grade, TRUNC(numeric_grade)FROM gradeWHERE section_id = '106'I get the average as 85.45454545454545454545 I want the TRUNC Function to make it 85. |
|
|
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/2012A2 2 02/03/2012A1 3 03/04/2013Table2:NETWORK HOURSWORKED DATEWORKEDB1 1 01/02/2012A1 2 02/03/2012RESUTLT TABLENETWORK HOURSWORKEDAFTERDATE>=FEB HOURWORKEDFORTHEENTIREYEARA1 5 6A2 4 4B1 0 1Basically 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 Table1UNIONSELECT network, dateworked, hrworked from Table2) AS T1group 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 countSUM(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 |
|
|
|