I need advanced help please.

Here's one for one of you SQL genuises out there. I really appreciate any help that I can get. There are several steps to this.
Step 1:
I have a database that holds timesheet records for users. These records are by week. I need a query that will return the username of the people that have entered 0 time for all of their timesheets in the database.
Step 2:
I need a query that will delete and timesheets that have 0 time entered after the last timesheet with time entered. If they entered time in week 3 but have 0 time for week 4 and 5 and then are withdrawn at week 5 I need to remove timesheets for week 4 and 5. Note, they could have entered 0 for week 2 as long as week three has time entered.
Step 3: - THIS IS THE MONSTER-
Since the timesheets are by week, I need to find the date that the last time was entered. The timesheet has a start-date then suntime, montime, tuetime, wedtime etc. Sunday would equal the week start-date. So, if time was last entered on Friday, the date that I'm looking for is the start-date + 5. My problem is, How do I go about doing this? I've been trying to do it all in a query to no avail.
I really appreciate any assistance that anyone can offer.
Thank you,
Still learning......
-ScottAll of the below is assuming that Username is unique, and that the"time" columns contain a 0 when there is no time entered. (Ifthey contain a NULL then the ISNULL function will need to beused.) This should hopefully be enough to get you going; Ihaven't tested the code so there may be some syntax errors.

spfeiffer13 wrote:

Step 1:
I have a database that holdstimesheet records for users. These records are by week. Ineed a query that will return the username of the people that haveentered 0 time for all of their timesheets in the database.

SELECT DISTINCT
Username
FROM
yourTable
INNER JOIN
(SELECT UserName, COUNT(*) AS TotalTimeSheetsPerUsername FROMyourTable GROUP BY UserName) AS Totals ON yourTable.Username =Totals.Username
WHERE
suntime+montime+tuetime+wedtime+thutime+fritime+sattime = 0
GROUP BY
yourTable.Username
HAVING
COUNT(*) = Totals.TotalTimeSheetsPerUsername

spfeiffer13 wrote:

Step 2:
I need a query that willdelete and timesheets that have 0 time entered after the last timesheetwith time entered. If they entered time in week 3 but have 0 timefor week 4 and 5 and then are withdrawn at week 5 I need to removetimesheets for week 4 and 5. Note, they could have entered 0 forweek 2 as long as week three has time entered.

DELETE
yourTable
FROM
yourTable
INNER JOIN
(
SELECT
Username,
MAX(StartDate) AS StartDate
FROM
yourTable
WHERE
suntime+montime+tuetime+wedtime+thutime+fritime+sattime = 0
GROUP BY
Username
) AS LastBlankTimesheet ON yourTable.Username =LastBlankTimesheet.Username AND yourTable.StartDate >=LastBlankTimesheet.StartDate

spfeiffer13 wrote:

Step 3: - THIS IS THE MONSTER-
Sincethe timesheets are by week, I need to find the date that the last timewas entered. The timesheet has a start-date then suntime,montime, tuetime, wedtime etc. Sunday would equal the weekstart-date. So, if time was last entered on Friday, the date thatI'm looking for is the start-date + 5. My problem is, How do I goabout doing this? I've been trying to do it all in a query to noavail.

This is what came to mind; maybe someone else will have something more elegant:
SELECT
UserName
MAX(TimesheetDate)
FROM
(
SELECT
UserName,
StartDate AS TimesheetDate,
SunTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,1,StartDate) AS TimesheetDate,
MonTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,2,StartDate) AS TimesheetDate,
TueTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,3,StartDate) AS TimesheetDate,
WedTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,4,StartDate) AS TimesheetDate,
ThuTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,5,StartDate) AS TimesheetDate,
FriTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,6,StartDate) AS TimesheetDate,
SatTime AS TimeEntered
FROM
yourTable
) AS TimesheetDates
WHERE
TimeEntered <> 0
GROUP BY
UserName

|||Thanks for the help. It all worked great except for one thing on Step 3. I keep getting this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ATTSTARTDT'.
I know that it is probably somehting stupid that I'm doing. I have the code below. Thanks in advance for any help that anyone can offer.
SELECT
schlstuid,
MAX(ATTSTARTDT)
FROM
(
SELECT
schlstuid,
ATTSTARTDT AS TimesheetDate,
sunmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
ATTSTARTDT AS TimesheetDate,
sunhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,1,ATTSTARTDT) AS TimesheetDate,
monmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,1,ATTSTARTDT) AS TimesheetDate,
monhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,2,ATTSTARTDT) AS TimesheetDate,
tuemns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,2,ATTSTARTDT) AS TimesheetDate,
tuehrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,3,ATTSTARTDT) AS TimesheetDate,
wedmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,3,ATTSTARTDT) AS TimesheetDate,
wedhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,4,ATTSTARTDT) AS TimesheetDate,
Thrmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,4,ATTSTARTDT) AS TimesheetDate,
Thrhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,5,ATTSTARTDT) AS TimesheetDate,
Frimns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,5,ATTSTARTDT) AS TimesheetDate,
Frihrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,6,ATTSTARTDT) AS TimesheetDate,
Satmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,6,ATTSTARTDT) AS TimesheetDate,
Sathrs AS TimeEntered
FROM
TimeSheetDailyAttendance
) AS TimesheetDates
WHERE
TimeEntered <> 0
GROUP BY
schlstuid
--------
I know that this is one long query. Thanks again for any help.
Still learning....
-Scott|||Use MAX(TimesheetDate) instead. When we added days to ATTSTARTDT we used TimesheetDate as the column name.
|||Worked like a charm. Thank you!!!!!!