Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
UI for denormalized timesheet entry
Message
 
To
13/09/2004 18:41:10
General information
Forum:
ASP.NET
Category:
Other
Miscellaneous
Thread ID:
00941744
Message ID:
00941765
Views:
25
Hey Nancy,

Assuming you're using SQL as the data backend you may could do something like the following (also assuming you're using a data grid component) ...

I used the below table structure for my demo:
CREATE TABLE [TaskTime] (
	[idTime] 		[int] 		IDENTITY (1, 1) NOT NULL ,
	[idEmp] 		[int] 		NULL ,
	[TaskDesc] 	[char] 		(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TaskHours] 	[float] 		NULL ,
	[dtTaskDate] 	[smalldatetime] 	NULL ,
	CONSTRAINT [PK_TaskTime] PRIMARY KEY  CLUSTERED 
	(
		[idTime]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO
First, create a stored procedure that you could pass an employee ID to, and use a SQL statement similar to below:
SELECT 
	TaskDesc, 
	SUM(CASE Day(dtTaskDate) WHEN 1 THEN TaskHours ELSE 0 END) AS Day1,
	SUM(CASE Day(dtTaskDate) WHEN 2 THEN TaskHours ELSE 0 END) AS Day2,
	SUM(CASE Day(dtTaskDate) WHEN 3 THEN TaskHours ELSE 0 END) AS Day3,
	SUM(CASE Day(dtTaskDate) WHEN 4 THEN TaskHours ELSE 0 END) AS Day4,
	SUM(CASE Day(dtTaskDate) WHEN 5 THEN TaskHours ELSE 0 END) AS Day5,
	SUM(CASE Day(dtTaskDate) WHEN 6 THEN TaskHours ELSE 0 END) AS Day6,
	SUM(CASE Day(dtTaskDate) WHEN 7 THEN TaskHours ELSE 0 END) AS Day7,
	SUM(CASE Day(dtTaskDate) WHEN 8 THEN TaskHours ELSE 0 END) AS Day8,
	SUM(CASE Day(dtTaskDate) WHEN 9 THEN TaskHours ELSE 0 END) AS Day9,
	SUM(CASE Day(dtTaskDate) WHEN 10 THEN TaskHours ELSE 0 END) AS Day10,
	SUM(CASE Day(dtTaskDate) WHEN 11 THEN TaskHours ELSE 0 END) AS Day11,
	SUM(CASE Day(dtTaskDate) WHEN 12 THEN TaskHours ELSE 0 END) AS Day12,
	SUM(CASE Day(dtTaskDate) WHEN 13 THEN TaskHours ELSE 0 END) AS Day13,
	SUM(CASE Day(dtTaskDate) WHEN 14 THEN TaskHours ELSE 0 END) AS Day14,
	SUM(CASE Day(dtTaskDate) WHEN 15 THEN TaskHours ELSE 0 END) AS Day15,
	SUM(CASE Day(dtTaskDate) WHEN 16 THEN TaskHours ELSE 0 END) AS Day16,
	SUM(CASE Day(dtTaskDate) WHEN 17 THEN TaskHours ELSE 0 END) AS Day17,
	SUM(CASE Day(dtTaskDate) WHEN 18 THEN TaskHours ELSE 0 END) AS Day18,
	SUM(CASE Day(dtTaskDate) WHEN 19 THEN TaskHours ELSE 0 END) AS Day19,
	SUM(CASE Day(dtTaskDate) WHEN 20 THEN TaskHours ELSE 0 END) AS Day20,
	SUM(CASE Day(dtTaskDate) WHEN 21 THEN TaskHours ELSE 0 END) AS Day21,
	SUM(CASE Day(dtTaskDate) WHEN 22 THEN TaskHours ELSE 0 END) AS Day22,
	SUM(CASE Day(dtTaskDate) WHEN 23 THEN TaskHours ELSE 0 END) AS Day23,
	SUM(CASE Day(dtTaskDate) WHEN 24 THEN TaskHours ELSE 0 END) AS Day24,
	SUM(CASE Day(dtTaskDate) WHEN 25 THEN TaskHours ELSE 0 END) AS Day25,
	SUM(CASE Day(dtTaskDate) WHEN 26 THEN TaskHours ELSE 0 END) AS Day26,
	SUM(CASE Day(dtTaskDate) WHEN 27 THEN TaskHours ELSE 0 END) AS Day27,
	SUM(CASE Day(dtTaskDate) WHEN 28 THEN TaskHours ELSE 0 END) AS Day28,
	SUM(CASE Day(dtTaskDate) WHEN 29 THEN TaskHours ELSE 0 END) AS Day29,
	SUM(CASE Day(dtTaskDate) WHEN 30 THEN TaskHours ELSE 0 END) AS Day30,
	SUM(CASE Day(dtTaskDate) WHEN 31 THEN TaskHours ELSE 0 END) AS Day31
FROM 
	TaskTime
WHERE
	idEmp = X
GROUP BY 
	TaskDesc
The above SQL will return a set of data very similar to what they are looking for... However, you may want to add a date restriction to the WHERE clause in order to specify that the records are for the correct month.

Next, in order to get the task totals per row, you could use the code behind / ItemDataBound event of the grid... Summing each "Day" column to derive the value of the total column, per row.

Finally, for the "Daily Hour Total" footer I would reccomend a seperate routine to loop through the data object (DataSet, DataTable, Etc..) and append totals to an array with 32 elements (31 Days and 1 Total). Once you have looped through every record, use the totals in the array to populate the footer.

I figure if nothing else, maybe a place to start : )

Hope that helps...
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform