Sql Query which includes daily detail, daily totals, and weekly totals

[MonitorMonkey]

I need to display the following in a .NET gridview control for a time tracking system:

Date each task was done
a description of each task
amount of time spent completing each task
other details about each task
Total Hrs Worked Each Day
Total Hrs Worked each week

The detail records are selected based on a field which identifies the tasks that belong to the current user.

I'm using a stored procedure which inserts the daily task detail into a work table and then updates fields in the work table with calculations for total hours daily and total hours weekly. Then I use the work table to create a datasource for the gridview. This produces the desired result, but it's running too slowly and it seems like a sloppy solution. How can I create a single SQL select query or view for the gridview datasource?

Here's my my Microsoft SQL server 2005 stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[WkTotals]

@wdname varchar(50)

AS

DELETE FROM tblWeeklyTotals

-- insert all of the work detail records for this employee into tblWeeklyTotals
DECLARE @d as datetime, @WeekNo as Integer, @WkHours as decimal(18, 2), @Wk_RecNo as Integer, @Wk_Owner as varchar(50)

DECLARE wk_cursor CURSOR FOR
SELECT DISTINCT wdDateWorkDone, datepart(ww, wdDateWorkDone), wdWorkLength, wdNumber, wdName
FROM tblWorkDetail
WHERE (wdname = @wdname) AND (NOT (WdDateWorkDone IS NULL))
ORDER BY wdDateWorkDone

OPEN wk_cursor
FETCH NEXT FROM wk_cursor
INTO @d, @WeekNo, @WkHours, @Wk_RecNo , @Wk_Owner

WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;

Insert into tblWeeklyTotals(Wk_date, Wk_WeekNo, Wk_WeekHrs, Wk_DetailId, Wk_Owner)
Values (@d, datepart(ww, @d), @WkHours, @Wk_RecNo, @Wk_Owner)

FETCH NEXT FROM wk_cursor
INTO @d, @WeekNo, @WkHours, @Wk_RecNo, @Wk_Owner

END

CLOSE wk_Cursor
DEALLOCATE wk_Cursor

-- calculate weekly hr totals

DECLARE @h2 as datetime, @WeekNo2 as Integer, @WkHours2 as decimal(18, 2), @WkYear as Integer
DECLARE hr_cursor CURSOR FOR

SELECT Wk_date, DATEPART(ww, Wk_date), Wk_WeekHrs, DATEPART(yyyy, Wk_date)
FROM tblWeeklyTotals
WHERE Wk_WeekTotHrs = 0.00
ORDER BY Wk_date, Wk_WeekNo

OPEN hr_cursor
FETCH NEXT FROM hr_cursor
INTO @h2, @WeekNo2, @WkHours2, @WkYear

WHILE @@FETCH_STATUS = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE tblWeeklyTotals
SET Wk_WeekTotHrs =
(SELECT SUM(Wk_WeekHrs)
FROM tblWeeklyTotals
WHERE (Wk_WeekNo = @WeekNo2) AND DATEPART(yyyy, Wk_date) = @WkYear)
WHERE (Wk_WeekNo = @WeekNo2) AND (DATEPART(yyyy, Wk_date) = @WkYear)

FETCH NEXT FROM hr_cursor
INTO @h2, @WeekNo2, @WkHours2, @WkYear

END

CLOSE hr_Cursor
DEALLOCATE hr_Cursor

--calculate daily hour totals
DECLARE @d2 as datetime, @WkHoursd as decimal(18, 2)

DECLARE day_cursor CURSOR FOR
SELECT Wk_Date, Wk_WeekHrs
FROM tblWeeklyTotals
WHERE Wk_DayTotHrs IS NULL
ORDER BY Wk_date

OPEN day_cursor
FETCH NEXT FROM day_cursor
INTO @d2, @WkHoursd

WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;

UPDATE tblWeeklyTotals
SET Wk_DayTotHrs =
(SELECT SUM(Wk_WeekHrs)
FROM tblWeeklyTotals
WHERE (Wk_Date = @d2))
WHERE (Wk_Date = @d2)

FETCH NEXT FROM day_cursor
INTO @d2, @WkHoursd

END

CLOSE day_Cursor
DEALLOCATE day_Cursor

 

[gmmastros]

So... your question is... How to remove the cursors? Good question!

I suspect that it can be done. However... it might be better to start over. As such, I suggest you post some sample data and expected results. With this information, it will be easier for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Never use a cursor.

use a select statement instead of the values clause for the insert instead.

Updates can also be done in a set-based fashion. You should never ever consider using a cursor again for an insert, update or delete statement. Working one row at a time is an extremely poor programming practice in T-SQL if it can be avoided. It should never be you first choice.

"NOTHING is more important in a database than integrity." ESquared

 

[MonitorMonkey]

I'm up against the wall with the deadline for this. I'll struggle a while longer on my own instead of using my time to post sample data and additional information about the expected results. I may have to tuck my tail and beg for your help later. Thanks for your offer.

 

[gmmastros]

Any solution that involves a cursor will be many times slower than a set based approach. In your case... you have 3 cursors. I'd be willing to bet that an optimized set based solution will be 100 times (or more) faster. It may be worth your time to post some sample data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom