March 30, 2016 at 1:59 am
Hello
I have a table with days of the week as time columns. This is because I am working on an ASP.Net MVC 5 application in which a user needs to fill out the amount of time they wish to assign to a customer for any relevant day.
My table looks like this
CREATE TABLE [dbo].[Plans] (
[PlanId] INT IDENTITY (1, 1) NOT NULL,
[CustomerId] INT NOT NULL,
[AgreementId] INT NOT NULL,
[Timesheet_TimeSheetId] INT NULL,
[CustomerHasPlan] BIT DEFAULT ((0)) NOT NULL,
[Monday] TIME (7) DEFAULT ('00:00:00') NULL,
[Tuesday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
[Wednesday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
[Thursday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
[Friday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
[Saturday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
[Sunday] TIME (7) DEFAULT ('00:00:00') NOT NULL,
CONSTRAINT [PK_dbo.Plans] PRIMARY KEY CLUSTERED ([PlanId] ASC),
CONSTRAINT [FK_dbo.Plans_dbo.TimeSheets_Timesheet_TimeSheetId] FOREIGN KEY ([Timesheet_TimeSheetId]) REFERENCES [dbo].[TimeSheets] ([TimeSheetId]),
CONSTRAINT [FK_dbo.Plans_dbo.Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customers] ([CustomerId]) ON DELETE CASCADE
);
What I need is the ability to sum up all the days of the week and place it into a total column. This what I have as a screen http://prntscr.com/alwd1q
I have tried to just use a sum but soon found out that you can't use a sum on a TimeSpan
Hope someone can help me
Many thanks
March 30, 2016 at 5:23 am
If this is recording the amount of time allocated as opposed to the actual time of day wouldnt you be better to look to use a numeric field?
March 30, 2016 at 5:38 am
I agree with what Jon says. The time data type is intended to store a time-of-day, not a duration. (I once worked for an employer who payd double for work on Sundays, and those extra hours had to be entered int he timesheet as well. When I had to do emergency work on a Sunday that took me 13 hours, I dutifully entered 13 hours on the project and 13 hours on the "bonus for overtime" code - and then the time registration application halted because it couldn't handle more than 24 hours per day)
If you are stuck with this data model and cannot change it, then here is how I would sum the times.
First, convert them to a duration. If the precision you use is minutes, you can use DATEDIFF(minute, TimeColumn, CAST('0:00;00' AS time)) to convert the entered times to minutes. You can then add those.
To convert the total duration back to a human-readable representation, use TotalTime / 60 (integer division, truncate the fraction) to get the number of hours, and TotalTime % 60 (remainder) to get the number of minutes, convert to string and concatenate with a colon in between. Do not convert to time, because that will use time-of-day logic. (40:00:00 will be considered 16:00:00 the next day, and since you are not interested in the date you'll end up with just 16:00:00).
Final note - your table is not normalized well. The seven columns for the days of the week are a repeating group. They should be moved to their own table, with PlanId + weekday as the primary key and duration as the only other column. That will make your coding much easier, since you can aggregate instead of having to copy/paste the same expression for 7 columns.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply