Posted by Erland Sommarskog on 07/19/05 01:09

Will (billrigby@hotmail.com) writes:
> So, for example, the insert to make the Smith record compliant for 18
> July would be
>
> INSERT INTO TimesheetItem
> (EmployeeID,
> Start,
> DurationMins,
> WorkID)
> VALUES
> (1,
> '18 July 2005 11:30:00',
> 270,
> 2)
>
> Can I do this without using a cursor? I apologise if this is badly
> expressed - I'm doing my best!

Sure, no need for a cursor:

INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)

SELECT EmployeeID, dateadd(MINUTE, DurationMins, LastStart),
7.5 * 60 - DurationMins, 2
FROM (SELECT TI.EmployeeID,
convert(char(8), TI.Start, 112) As TimesheetDate,
MAX(TI.Start) AS LastStart,
SUM(TI.DurationMins) AS DurationMins
FROM TimesheetItem TI
/* WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID ) */
GROUP BY TI.EmployeeID, convert(char(8), TI.Start, 112)
HAVING SUM(TI.DurationMins) < 7.5*60) AS x

However, there are still a number of issues that are not entirely
clear to me:

1) The Work table does not seem to be relevant to the query at all.
We do need a work code to insert, but you have not mentioed how
we determine this. I've assumed that the 2 you used is a hard-coded
value.

2) The table TimesheetItem is problematic. It appears logical that
(Employee, Start) is unique, but there is no constraint saying
this as I could see. Your primary key is an IDENTITY column, but
if my assumption is correct, you would need that column.

3) In your original post, you restricted the query to employees of a
certain department. Here you seem to have relaxed that requirement.
For this reason, I've commented the part of the query that accesses
Employee.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]