SQL Syntax help

2004hummer
2004hummer used Ask the Experts™
on
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')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What type are proc_start_date, proc_start_time, proc_end_date, and proc_end_time?

And what is your table schema?
2004hummer,

Try this.


SELECT
      ProcessId,
      ProcessStart,
      ProcessStop,
      TotalCount,
      DATEDIFF(MINUTE, ProcessStart, ProcessStop) As ElapsedTime

FROM

      (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')
      ) YourQuery

WHERE DATEDIFF(MINUTE, ProcessStart, ProcessStop) <= 10


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

Hope this helps.
SrinivasK

Author

Commented:
SrinivasK,

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

Your Rock!!

Thanks!
Cool. Thanks 2004hummer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial