two queries show only one record from query one and multiple records from query two 1

[evr72]

Hello,

I have two tables reghours and work

my reghours table looks something like this

empnum      date       deptnum hours
1234     1/31/2012      42      8

then the work table looks like this

empnum      dat       deptnum  itemnum     opseq  workhours
1234     1/31/2012      42      04562       0011    2 
1234     1/31/2012      42      04744       0011    1 
1234     1/31/2012      42      04573       0011    5

what I am trying to do is:

empnum      dat       deptnum  itemnum     opseq  hours  workhours

1234     1/31/2012      42      04562       0011     8       2 
1234     1/31/2012      42      04744       0011     0       1 
1234     1/31/2012      42      04573       0011     0       5

i tried to to:

select reghours.empnum, reghours.date, reghours.deptnum, work.itemnum, work.opseq, reghours.hours, work.workhours 
From reghours left join work on(reghours.empnum = work.empnum) and (reghours.dat = work.dat) and (reghours.dptnum = work.dptnum);

this works but I get multiples for my hours

empnum      dat       deptnum  itemnum     opseq  hours  workhours

1234     1/31/2012      42      04562       0011     8       2 
1234     1/31/2012      42      04744       0011     8       1 
1234     1/31/2012      42      04573       0011     8       5

not sure how to tackle thisone. any advice is much appreciated!!!!!!

 

[SkipVought]

hi,

What did you expect to return?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Is this waht you wanted to do?

select
  reghours.empnum
, reghours.date
, reghours.deptnum
, work.itemnum
, work.opseq
, reghours.hours
, sum(work.workhours) as HrsWorked 

From reghours left join work 
  on (reghours.empnum = work.empnum)
 and (reghours.dat = work.dat)
 and (reghours.dptnum = work.dptnum)

group by
  reghours.empnum
, reghours.date
, reghours.deptnum
, work.itemnum
, work.opseq
, reghours.hours;

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[evr72]

SkipVought,

I like I mentioned, I got this

empnum    dat      deptnum itemnum opseq hours workhours 
1234    1/31/2012   42       04562 0011     8      2 
1234    1/31/2012   42       04744 0011     8      1 
1234    1/31/2012   42       04573 0011     8      5

But would like to get this

empnum    dat      deptnum itemnum opseq hours workhours 
1234    1/31/2012   42       04562 0011     8      2 
1234    1/31/2012   42       04744 0011     0      1 
1234    1/31/2012   42       04573 0011     0      5

Not sure if this can be done

 

[SkipVought]

I think that this might work

select
  reghours.empnum
, reghours.date
, reghours.deptnum
, work.itemnum
, work.opseq
, reghours.hours/Count(*)
, work.workhours 

From reghours left join work 
  on (reghours.empnum = work.empnum)
 and (reghours.dat = work.dat)
 and (reghours.dptnum = work.dptnum)

group by
  reghours.empnum
, reghours.date
, reghours.deptnum
, work.itemnum
, work.opseq
, reghours.hours; 
, work.workhours

that would result in...
[tt]
1234 1/31/2012 42 04562 0011 2.667 2
1234 1/31/2012 42 04744 0011 2.667 1
1234 1/31/2012 42 04573 0011 2.667 5
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[evr72]

if I do this at the end

reghours.hours; , work.workhours

I get an error
Characters found after end of SQL statement

if I thake the ";" out it gives me 8 hours not the 2.667

 

[SkipVought]

Check the semicolon. Should be at the end ONLY.

Sorry!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[evr72]

Skip, tride that but no luck I get this

empnum	dat	deptnum	itemnum	opseq	Expr1005	workhours
1234	6/7/2012	42	12322	0011	8	1
1234	6/7/2012	42	12345	0011	8	2
1234	6/7/2012	42	12355	0011	8	5

 

[SkipVought]

SELECT 
  a.empnum
, a.date
, a.deptnum
, a.hours/(
select count(*)
from work c
where a.empnum=c.empnum and a.date=c.dat and a.deptnum=c.deptnum)
, b.opseq
, b.workhours

FROM reghours a, work b

WHERE a.date = b.dat AND a.deptnum = b.deptnum AND a.empnum = b.empnum

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[evr72]

Thank you Skip that worked well. I really appreciate the help!!!!

 

[SkipVought]

Is there a reason to list the individual work hour rows rather than SUM the work hours and have one row returned?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}