Need help converting Dates and calculating Hours
-
Ok, bear with me while I try to explain what I need to do and what I have done so far.
I need to calculate the total hours from the date and time received to the date and time resolved. I have 4 columns (DateReceived, TimeReceived, DateResolved, TimeResolved).
My thought is that I need to combine the Date/TimeReceived and Date/TimeResolved columns into two columns in a temporary table and then do the math (DateDiff) for the number of hours between them. Only problem is that I can't figure out if I need to do this in one or two queries.
Any assistance is GREATLY appreciated!!!
Chuck
-
Just merge those columns in the main design.. then it'll be much easier.
Are all those columns datetimes (as in not varchars or ints)??
-
Unfortunatley this is a 'live' DB and I can't merge them. The data has been gathered over the last 4-5 years.
The Date columns are datetime (8) and the Time columns are nvarchar (10).
-
Can we see some sample data?
-
ReceiveDate ReceiveTime ResolveDate ResolveTime 1/15/2003 8:10 1/15/2003 9:50 1/15/2003 8:00 1/15/2003 8:30 1/15/2003 8:30 1/15/2003 9:00 1/15/2003 10:00 1/15/2003 10:30 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 13:20 1/15/2003 14:13 1/15/2003 14:00 1/15/2003 14:18 1/15/2003 12:20 1/15/2003 13:40 -
create table #temp_dates (
DateReceived datetime,
TimeReceived varchar(10),
DateResolved datetime,
TimeResolved varchar(10) )
INSERT INTO #temp_dates VALUES ('2005-09-01', '8:00', '2005-09-01', '10:30')
INSERT INTO #temp_dates VALUES ('2005-09-02', '9:30', '2005-09-03', '8:30')
SELECT DateReceived + TimeReceived DateTimeReceived , DateResolved+TimeResolved DateTimeResolved,
DATEDIFF(mi,DateReceived + TimeReceived , DateResolved+TimeResolved) MinutesElapsed
FROM #temp_dates
DROP TABLE #temp_dates
-
ok, another newbie question:
This will be added after the "SELECT * FROM TableName" right?
There are over 100k records in this DB, my first query is:
SELECT PROGRAM, COUNT(PROGRAM) AS #Call
FROM pdb_records
GROUP BY PROGRAM
ORDER BY PROGRAM
I should have been a bit more clear I guess, I am still way into the nooB stage with SQL. I know I don't need the GROUP BY above, but that is there to please the boss (wannabe). This DB is the back end to a trouble ticket system and they want to know the average time that it takes to resolve an issue (including "standby" time where the tech is waiting for a part, etc.).
Thanks again for all the help, I am way ahead of where I would have been!
-
Yes you need the group by in here, also notice the count(*) instead (faster).
SELECT PROGRAM, COUNT(*) AS #Call
FROM pdb_records
GROUP BY PROGRAM
ORDER BY PROGRAM
We need more information to solve the rest of the problem, can you post the table ddl, sample data and expected output?
-
Sample data is posted above:
ReceiveDate ReceiveTime ResolveDate ResolveTime 1/15/2003 8:10 1/15/2003 9:50 1/15/2003 8:00 1/15/2003 8:30 1/15/2003 8:30 1/15/2003 9:00 1/15/2003 10:00 1/15/2003 10:30 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 9:15 1/15/2003 11:00 1/15/2003 13:20 1/15/2003 14:13 1/15/2003 14:00 1/15/2003 14:18 1/15/2003 12:20 1/15/2003 13:40 I am using enterprise manager, not sure what the ddl is. The expected output is the total time (in hours) that it took to resolve an issue. Example: If the issue was received at 12:30pm today and resolved at 14:00pm tomorrow, the time would be 25.5 hours. I will then be taking that output and getting an average time per call (I can do that in excel).
-
Read this (WHOLE THING PLS).
Please provide the final solution required... you reall don't need to use excel for this.
-
Ah, ok, sorry about that...
Here is the script that was generated with the "Script Object to New Window > Create" command:
CREATE TABLE [pdb_records] (
[EntryID] [int] IDENTITY (1, 1) NOT NULL ,
[Site] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ControlNumber] [int] NULL ,
[UIC] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneExt] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROGRAM] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReceivedBy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReceiveDate] [datetime] NULL ,
[ReceiveTime] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Problem] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherProblem] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KnownSolutions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Solution] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResolvedBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResolveDate] [datetime] NULL ,
[ResolveTime] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkTime] [real] NULL ,
[TotalProblemTime] [real] NULL ,
[Completed] [int] NULL ,
[SiteVisit] [int] NULL ,
[Workshop] [int] NULL ,
[DateEntered] [datetime] NULL CONSTRAINT [DF_pdb_records_DateEntered] DEFAULT (getdate()),
CONSTRAINT [PK_pdb_records] PRIMARY KEY CLUSTERED
(
[EntryID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The sample data that is needed is just dates and times, I am not sure why but they didn't put the time of day into the same field as the date (which appears possible), it is in the ReceiveTime/ResolveTime column. The other data isn't needed, I was going to take the total time per call, sort it by PROGRAM, then show the average time per call based on the PROGRAM.
As for the sample data, the posting you sent said not to put it in as tabular data and (again being a newbie) I am now trying to get the other function to work so I can get the data you need. I will keep working on that, can you work with this for now?
Thanks again!
-
I'll let you work on that... will become more usefull than you think in the future.
-
I can see that already, besides I am a glutton for punishing my 'lil brain!
-
If you say so.. will be waiting for that sample data script
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply