Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
UI for denormalized timesheet entry
Message
 
À
13/09/2004 18:41:10
Nancy Folsom
Pixel Dust Industries
Washington, États-Unis
Information générale
Forum:
ASP.NET
Catégorie:
Autre
Divers
Thread ID:
00941744
Message ID:
00941765
Vues:
24
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...
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform