How much programming can I eliminate with my select

[Terris]

I have an employee detail file that contains bar coding scans for times clocked in and out. I want to select for a week all employees that are over 40 hours and have at least one record that the field type <> 1,5 or 6. Any suggestions or do I need to go to outside SQL to generate
Thanks
Terri

 

[Niv3k]

I'm sure what you want to do is possible, but I would need more information on the table layout....

Kevin

 

[Terris]

labor detail table:
employee #
type
begin timedate
end timedate

an entry is made in this table each time an employee clocks out of a mfg. job so for one week there may be many entries for each employee.

I need to total up all entries for an employee for a week
If that employee has clocked more then 40 hours in a week and has at least one record that is not a type 1, 5 or 6 during that week I want to pull the detail records. Time is calculated on each record end timedate - begin timedate
is this enough information?

 

[topher0303]

How about:

select employee, sum(enddate - begindate)
from detailtable dt
where dt.begindate between 'thestartdate' and 'theenddate'
and exists (select 1 from detailtable dt2
where dt.employee = dt2.employee
and type not in (1, 5, 6))
group by emaployee
having sum(enddate - begindate) > 40


Chris.

 

[Terris]

Received this error message when using this format. Will continue researching on my own but wonder if there is a suggestion on how to handle this?
The sum or average aggregate operation cannot take a datetime data type as an argument.

 

[Niv3k]

Use a CAST or CONVERT with the SUM, to make it an int, or a better way is to use the datediff function:

SUM(DATEDIFF(minute, startdate, enddate))

This will give you the breakdown in minutes, which is good because if you use datediff with hours, it might round down .25 hours, so some employees would not show up for over 40 hours. just change the having to > 2400.

 

[Terris]

tried the datediff gave the same error message. Guess i'm off to convert
:>)
thanks for your input

 

[sunila7]

Hi,

Try these Queries

select employee, sum(datediff(dd,begindate,enddate))
from detailtable dt
where dt.begindate between 'thestartdate' and 'theenddate'
and exists (select 1 from detailtable dt2
where dt.employee = dt2.employee
and type not in (1, 5, 6))
group by emaployee
having sum(datediff(dd,begindate,enddate)) > 40


OR
Select * From
(select employee, sum(datediff(dd,begindate,enddate)) SumTotDiff
from detailtable dt
where dt.begindate between 'thestartdate' and 'theenddate'
and exists (select 1 from detailtable dt2
where dt.employee = dt2.employee
and type not in (1, 5, 6))
group by emaployee) TBL Where SumTotDiff>40

Hope it helps

Sunil