Link to home
Start Free TrialLog in
Avatar of 2004hummer
2004hummer

asked on

SQL Syntax help

Hello,

I need some help modifying this query.

I need the syntax to display the time difference between ProcessStop and ProcessStart in minutes. The alias for this can be called ElaspedTime. I also only need to display records that have an ElaspedTime of 10 minutes or less.

How should the code look?

Thanks.
--------------------------------------------------------------------------------------------------
SELECT     cli_procid AS 'ProcessID',
                          (SELECT     RTRIM(proc_start_date) + ' ' + RTRIM(proc_start_time) AS Expr1
                            FROM          ProcessID
                            WHERE      (proc_id_num = b.cli_procid) AND (proc_step_num = dbo.fnGetFirstStep(b.cli_arnum, b.cli_appl, b.cli_sub_appl))) AS 'ProcessStart',
                          (SELECT     RTRIM(proc_end_date) + ' ' + RTRIM(proc_end_time) AS Expr1
                            FROM          ProcessID AS ProcessID_2
                            WHERE      (proc_id_num = b.cli_procid) AND (proc_step_num = dbo.fnGetLastStep(b.cli_arnum, b.cli_appl, b.cli_sub_appl))) AS 'ProcessStop',
                          (SELECT     MAX(proc_count) AS Expr1
                            FROM          ProcessID AS ProcessID_1
                            WHERE      (proc_id_num = b.cli_procid)) AS 'TotalCount'

FROM         ClientDataFiles AS b
WHERE     (cli_arnum = '111111')
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

What type are proc_start_date, proc_start_time, proc_end_date, and proc_end_time?

And what is your table schema?
ASKER CERTIFIED SOLUTION
Avatar of SrinivasK
SrinivasK

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SrinivasK
SrinivasK

if the ProcessStart and ProcessStop are not of smalldatetime datatype, use convert.

Hope this helps.
SrinivasK
Avatar of 2004hummer

ASKER

SrinivasK,

Your query worked perfect. I didn't have to modify a thing.

Your Rock!!

Thanks!
Cool. Thanks 2004hummer