September 13, 2009 at 5:11 pm
Hi,
I know straight up that I have set up a field in my table incorrectly, so I am after some advice on fixing this and then calculating the data. I have a field which is set to varchar(8) and stores the length of time for an exercise session. This allows me to store the data as hh:mm:ss and I don't have dates or AM/PM to worry about. The problem is that now I am ready to start summing the total of time spent exercising each month and I am unsure of how I can do this. I did consider using a Split function to separate the values and then calculate the hours, minutes and seconds as total seconds and then convert back to hh:mm:ss but Split functions always seem to create new rows in a table, and not columns in a row. Does anyone have a good suggestion for handling this?
Cheers
ab
September 13, 2009 at 5:16 pm
Could you post the CREATE TABLE definition please?
A few rows of sample data would probably help us give you the best answers too.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:30 pm
USE [Gym]
GO
/****** Object: Table [dbo].[tblSessions] Script Date: 09/14/2009 08:56:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSessions](
[nSessionPK] [int] IDENTITY(1,1) NOT NULL,
[nGymSessionID] [int] NULL,
[dtDate] [smalldatetime] NOT NULL,
[stTime] [varchar](8) NOT NULL,
[stZone] [varchar](8) NOT NULL,
[nMaxHeartRate] [int] NOT NULL,
[nAvgHeartRate] [int] NOT NULL,
[nCalories] [int] NOT NULL,
CONSTRAINT [PK_tblSessions] PRIMARY KEY CLUSTERED
(
[nSessionPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
50231616/07/2007 12:00:00 AM0:52:350:47:40187159831
50331718/07/2007 12:00:00 AM1:12:290:59:011841621126
504NULL22/07/2007 12:00:00 AM0:48:130:36:33183157755
50531823/07/2007 12:00:00 AM2:02:171:27:531751511762
50631924/07/2007 12:00:00 AM0:44:140:28:01182144577
September 13, 2009 at 5:50 pm
There are many valid approaches. Here's one:
CREATE TABLE #Session
(
session_id INT IDENTITY PRIMARY KEY,
fk_user_id INT NOT NULL, -- Foreign key to User table
start_dt DATETIME NOT NULL,
end_dt DATETIME NOT NULL,
session_date AS DATEADD(DAY, DATEDIFF(DAY, 0, start_dt), 0) PERSISTED NOT NULL,
duration_mins AS DATEDIFF(MINUTE, start_dt, end_dt) PERSISTED NOT NULL,
CHECK (end_dt >= start_dt)
);
INSERT #Session
(fk_user_id, start_dt, end_dt)
SELECT 187, '2009-08-17 12:30:00', '2009-08-17 13:00:00' UNION ALL
SELECT 251, '2009-08-18 09:15:00', '2009-08-18 09:30:00' UNION ALL
SELECT 393, '2009-08-23 11:00:00', '2009-08-23 13:30:00'
SELECT session_id, fk_user_id, start_dt, end_dt, session_date, duration_mins
FROM #Session
ORDER BY session_id;
DROP TABLE #Session;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 6:25 pm
Cheers for the quick reply, I'll give that a go a bit later and let you know how it went.
ab
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply