Calculating duration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating duration

Author

Topic

nigelc
Starting Member

20 Posts

Posted - 2007-01-18 : 08:53:56

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scos	pps_scos	pps_transcript_details	pps_principals	pps_principals
SCO_ID	NAME	                      DATE_CREATED   PRINCIPAL_ID    NAME
136850	MTM-106 The Dangers of Smoking	08:17:25	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:17:25	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:17:40	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:18:25	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:18:57	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:19:14	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:19:47	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:20:21	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:20:44	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:21:26	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:22:13	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:24:55	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:25:12	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:25:29	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:26:49	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:27:02	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:27:29	16287	Nigel Cordiner
136850	MTM-106 The Dangers of Smoking	08:27:43	16287	Nigel Cordiner

Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-18 : 09:13:56

[code]
select duratino = datediff(second, min(DATE_CREATED), max(DATE_CREATED))
from
(
SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
)a
[/code]