declare @e table(EmployeeID varchar(10),CostCenter varchar(10),PayDate datetime,
PayCode varchar(3),PayHours int)

insert @e
select 
   a.EmployeeID,
   a.CostCenter,
   a.PayDate,
   a.PayCode,
   a.PayHours
from  AutoTACurrentPayDaily a
join
  (
   select EmployeeID,PayDate
   from AutoTACurrentPayDaily
   where PayCode in ('REG','CSW')
   group by EmployeeID,PayDate having count(distinct paycode)=2
   ) b
on a.EmployeeID=b.EmployeeID and a.PayDate=b.paydate

select 
   a.EmployeeID,
   a.CostCenter,
   a.PayDate,
   a.PayCode,
   a.PayHours
from @e a
join
   (select EmployeeID,month(paydate)mo, sum(payhours) h
    from @e 
    group by EmployeeID,month(paydate)
    having sum(payhours)>=80
   ) b
on a.EmployeeID=b.EmployeeID and month(a.paydate)=b.mo

quote:

---

Originally posted by Gary Costigan

Vlad,

I just tried your new code and this is what came back.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near '@e'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near '@e'.


Thanks.

Gary

---

quote:

---

which may be caused by having duplicate data for the same PayDate but with different PayPeriodStart / PayPeriodEnd dates

---

declare @e table(EmployeeID varchar(10),CostCenter varchar(10),PayDate datetime,
PayCode varchar(3),PayHours int)

insert @e
select 
a.EmployeeID,
a.CostCenter,
a.PayDate,
a.PayCode,
sum(a.PayHours) PayHours /* or a.PayHours */
from AutoTACurrentPayDaily a
join
(
select EmployeeID,PayDate
from AutoTACurrentPayDaily a1
WHERE a1.CompanyCode = 'AA'
AND a1.PayCode IN ('REG', 'CSW')
AND a1.PayHours >'0'
AND a1.PayDate <= a1.PayPeriodEnd
AND DATEPART (MM,a1.PayDate)= '07'
AND DATEPART (YY,a1.PayDate)= '2005'
AND SUBSTRING (a1.CostCenter, 1, 4) IN ('0600', '0691', '0702', '0752', '0801', '0841')
AND SUBSTRING (a1.CostCenter, 6, 4) IN ('7930', '7935')
group by EmployeeID,PayDate having count(distinct paycode)=2
) b
on a.EmployeeID=b.EmployeeID and a.PayDate=b.paydate AND a.PayCode IN ('REG', 'CSW')
group by a.EmployeeID,a.CostCenter,a.PayDate,a.PayCode /*,a.PayHours */ 

select 
a.EmployeeID,
a.CostCenter,
a.PayDate,
a.PayCode,
a.PayHours
from @e a
join
(select EmployeeID,month(paydate)mo, sum(payhours) h
from @e
group by EmployeeID,month(paydate)
having sum(payhours)>=80
) b
on a.EmployeeID=b.EmployeeID and month(a.paydate)=b.mo

insert @e
select 
a.EmployeeID,
a.CostCenter,
a.PayDate,
a.PayCode,
a.PayHours 
from AutoTACurrentPayDaily a
join
(
select EmployeeID,PayDate
from AutoTACurrentPayDaily a1
WHERE a1.CompanyCode = 'AA'
AND a1.PayCode IN ('REG', 'CSW')
AND a1.PayHours >'0'
AND a1.PayDate <= a1.PayPeriodEnd
AND DATEPART (MM,a1.PayDate)= '07'
AND DATEPART (YY,a1.PayDate)= '2005'
AND SUBSTRING (a1.CostCenter, 1, 4) IN ('0600', '0691', '0702', '0752', '0801', '0841')
AND SUBSTRING (a1.CostCenter, 6, 4) IN ('7930', '7935')
group by EmployeeID,PayDate having count(distinct paycode)=2
) b
on a.EmployeeID=b.EmployeeID and a.PayDate=b.paydate AND a.PayCode IN ('REG', 'CSW') AND a.PayHours >'0' AND a.CompanyCode = 'AA'
AND a.PayDate <= a.PayPeriodEnd
AND SUBSTRING (a.CostCenter, 1, 4) IN ('0600', '0691', '0702', '0752', '0801', '0841')
AND SUBSTRING (a.CostCenter, 6, 4) IN ('7930', '7935')