Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: TSQL help...please

    I am trying to determine concurrency of a program running based on start and stop times. Right now I have 8863 records to compare, but here is a small sample.

    Start Time Finish Time
    1)2003-12-01 00:31:12.000 2003-12-01 01:14:23.000
    2)2003-12-01 06:55:43.000 2003-12-01 15:50:47.000
    3)2003-12-01 07:19:12.000 2003-12-01 16:30:06.000
    4)2003-12-01 07:22:10.000 2003-12-01 15:56:44.000
    5)2003-12-01 07:27:46.000 2003-12-01 18:36:05.000
    6)2003-12-01 07:34:53.000 2003-12-01 09:57:15.000

    I need to compare the times for overlap to determine concurrency. For instance:
    Comparing record 1 to record 2:
    If record1.starttime <= record2.finishtime AND record1.finishtime >= record2.starttime, then count = 1, esle 0
    If record1.starttime <= record3.finishtime AND record1.finishtime >= record3.starttime, then count = 1, else 0
    AND SO ON AND SO ON...
    when you encounter a 0, SUM the count as Concurrent.

    Then, I need it to move to record to and do the same thing...

    Comparing record 2to record 1:
    If record2.starttime <= record1.finishtime AND record2.finishtime >= record1.starttime, then count = 1, esle 0
    Comparing record2 to record 3:
    If record2.starttime <= record3.finishtime AND record2.finishtime >= record3.starttime, then count = 1, else 0
    AND SO ON AND SO ON...
    when you encounter a 0, SUM the count as Concurrent.

    Finally, when the loop (or cursor??) is finished, select MAX(count) as maximum concurrency.

    I tend to stay more on the system side and less on programming so I am really out of my realm. Any help polishing off the logic, and some pointers on how to write this in T-SQL would be much appreciated.

    Thanks in advance.

    Ryan Hunt
    Last edited by AS400Admin; 02-25-04 at 18:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •