August 5, 2009 at 11:39 am
How do I merge this query with data from Table2 ?
Query 1:
Raw data:
[font="Courier New"]
Date Emp#LastFirstHours
8-1-2009 17SmithJohn2.3
8-1-2009 17SmithJohn1.6
8-1-2009 17SmithJohn3.3
8-1-2009 24Jonesmary1.7
8-1-2009 24Jonesmary4.2
8-1-2009 24Jonesmary1.1[/font]
select date, Last_name, First_name, sum(hours_worked)
from Daily_Hours
where Hours_type=18 -- production hours
group by Last_name, First_name, date, hours_worked
order by date, Last_name, First_name
Results:
[font="Courier New"]DateEmp#LastFirstHours
8-1-200917SmithJohn7.2
8-1-200924Jonesmary7.0[/font]
Table 2:
(Raw Data, already summarized by break_type)
[font="Courier New"]
Date Emp#LastFirstHoursBreak_type
8-1-2009 17SmithJohn.3Meeting
8-1-2009 17SmithJohn.1Bathroom
8-1-2009 17SmithJohn.7Lunch
8-1-2009 24Jonesmary.7Lunch
8-1-2009 24Jonesmary.2Training
8-1-2009 24Jonesmary1.0Meeting[/font]
I would like to run one query with a matrix (is that the correct term ?)
so that I end up with the the break data on just 1 line along with the original hours data.
(Sorry for the formatting - can't get the numbers spaced under the headings properly)
[font="Courier New"]DateEmp#LastFirstHoursMeetingBathroomLunchTraining
8-1-200917SmithJohn7.2 .3 .1 .7
8-1-200924Jonesmary7.0 1.0 .7 .2
[/font]
August 5, 2009 at 11:56 am
homebrew01 (8/5/2009)
How do I merge this query with data from Table2 ?Query 1:
Raw data:
[font="Courier New"]
Date Emp#LastFirstHours
8-1-2009 17SmithJohn2.3
8-1-2009 17SmithJohn1.6
8-1-2009 17SmithJohn3.3
8-1-2009 24Jonesmary1.7
8-1-2009 24Jonesmary4.2
8-1-2009 24Jonesmary1.1[/font]
select date, Last_name, First_name, sum(hours_worked)
from Daily_Hours
where Hours_type=18 -- production hours
group by Last_name, First_name, date, hours_worked
order by date, Last_name, First_name
Results:
[font="Courier New"]DateEmp#LastFirstHours
8-1-200917SmithJohn7.2
8-1-200924Jonesmary7.0[/font]
Table 2:
(Raw Data, already summarized by break_type)
[font="Courier New"]
Date Emp#LastFirstHoursBreak_type
8-1-2009 17SmithJohn.3Meeting
8-1-2009 17SmithJohn.1Bathroom
8-1-2009 17SmithJohn.7Lunch
8-1-2009 24Jonesmary.7Lunch
8-1-2009 24Jonesmary.2Training
8-1-2009 24Jonesmary1.0Meeting[/font]
I would like to run one query with a matrix (is that the correct term ?)
so that I end up with the the break data on just 1 line along with the original hours data.
(Sorry for the formatting - can't get the numbers spaced under the headings properly)
[font="Courier New"]DateEmp#LastFirstHoursMeetingBathroomLunchTraining
8-1-200917SmithJohn7.2 .3 .1 .7
8-1-200924Jonesmary7.0 1.0 .7 .2
[/font]
Something like this:
with WorkHours (
WorkDate,
EmpNo,
LastName,
FirstName,
TotalHours
0 as (
select
date,
EmpNo, -- Not sure of actual column name
Last_name,
First_name,
sum(hours_worked)
from
Daily_Hours
where
Hours_type=18 -- production hours
group by
EmpNo, Last_name, First_name, date, hours_worked
),
OtherHours (
WorkDate,
EmpNo,
Hours,
Meeting,
Lunch,
Training,
Bathroom
) as (
select
date,
EmpNo,
sum(case when Break_type = 'Meeting' then Hours else 0 end),
sum(case when Break_type = 'Lunch' then Hours else 0 end),
sum(case when Break_type = 'Training' then Hours else 0 end),
sum(case when Break_type = 'Bathroom' then Hours else 0 end)
from
Table2 -- what is the actual table name?
group by
date,
EmpNo
)
select
wh.WorkDate,
wh.LastName,
wh.FirstName,
wh.TotalHours,
oh.Meeting,
oh.Lunch,
oh.Training,
oh.Bathroom
from
WorkHours wh
inner join OtherHours oh
on (wh.WorkDate = oh.WorkDate
and wh.EmpNo = oh.EmpNo)
order by
wh.WorkDate,
wh.EmpNo
Date Emp#LastFirstHoursBreak_type
8-1-2009 17SmithJohn.3Meeting
8-1-2009 17SmithJohn.1Bathroom
8-1-2009 17SmithJohn.7Lunch
8-1-2009 24Jonesmary.7Lunch
8-1-2009 24Jonesmary.2Training
8-1-2009 24Jonesmary1.0Meeting[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply