Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Concurrent Transactions

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Concurrent Transactions

    I want to use Dynamic AI to build a report that displays a graph (with count) of all concurrent (parallel) transactions on a day or even better a date range.

    Every Transactions has StartDate, StartTime, StartHr, StopDate, StopTime and StopHr field.

    Basically, I want to find out, what time of day was the busiest and how many employees where logged in at one time.

    How would one go about this?

  2. #2
    Join Date
    Feb 2009
    Posts
    1,462

    Default Re: Concurrent Transactions

    If I understand what you're after then I think all you need to do is group on date and startHr and make a count total type. Here's an example using a job cost transactions from a shop floor data collection application. The count total was made on the recnum column, but any column in an MS-SQL table can be used to count -- as long as it is not null.

    Click image for larger version. 

Name:	TransactionsPerHourExample.png 
Views:	70 
Size:	11.6 KB 
ID:	10500

    Bob

  3. #3
    Join Date
    Feb 2009
    Location
    Netherlands
    Posts
    110

    Default Re: Concurrent Transactions

    Hi Ben,

    The challenge here is that a transaction can be active over multiple days and you want to have it listed on each of those days.
    To measure the concurrent transaction, you will need to have an extra table (outer table) that lists the moments at which you want to measure the concurrency. For example, if you would like to measure it every day at 12.00, you could have a table that has 1 row for each day (date being the key). Then you can write a query that takes this date table as the base table, and join the transaction table with that.

    For example:
    SELECT D.Date, T.TransactionId
    FROM Dates D -- table with row for each day
    LEFT OUTER JOIN Transactions T ON -- Transaction
    T.StartDate <= D.Date AND (T.StopDate IS NULL OR T.StopDate >= D.Date)
    AND T.StartTime <= 12 AND (T.StopTime IS NULL OR T.StopTime >= 12)

    When transactions runs for multiple days, it will be listed for each day it was active.

    If you would like to measure it at multiple times a day, for example at 8, 10, 12, 14 and 16 hours, then you can use a cross join like this:
    SELECT D.Date, Hours.Hr, T.TransactionId
    FROM Dates D -- table with row for each day
    CROSS JOIN (VALUES (8), (10), (12), (14), (16)) Hours(Hr) -- Virtual Hours table with Hr column that lists 5 rows: 8, 10, 12, 14, 16
    LEFT OUTER JOIN Transactions T ON -- Transaction
    T.StartDate <= D.Date AND (T.StopDate IS NULL OR T.StopDate >= D.Date)
    AND T.StartHr <= Hours.Hr AND (T.StopTime IS NULL OR T.StopTime >= Hours.Hr)

    This will create rows for each day in the Dates table for each specified hour (8, 10, 12, 14, 16) and try to join transactions that match that time. You can then in Dynamic AI build a report on this where you filter on date, and count the transactions.

    I'm curious to find out if this is of any help...

    Best regards,
    Eddy
    Last edited by Eddy Kleinjan; 20-Dec-2016 at 12:17 PM. Reason: Added Hours.Hr to select of second query

  4. #4
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Re: Concurrent Transactions

    Hi Eddy,

    After more than a year I finally got around to start working on the concurrent transaction report.

    I followed your instructions for the second query, extended the amount of times it is compared during a day (24hr).


    Code:
    SELECT date_calendar.calendar_date, Hours.Hr, Trns.TransIdno
    FROM date_calendar
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23)) AS Hours(Hr)
    LEFT OUTER JOIN dbo.TRANSACTIONS Trns ON 
    Trns.StartDate <= date_calendar.calendar_date AND (Trns.StopDate IS NULL OR Trns.StopDate >= date_calendar.calendar_date) 
    AND Trns.StartHr <= Hours.Hr AND (Trns.StopHr IS NULL OR Trns.StopHr >= Hours.Hr)
    WHERE date_calendar.calendar_date >= (GETDATE()-365) AND date_calendar.calendar_date <= (GETDATE())
    One issue I ran into - Query Timeouts:
    I kept experiencing "Query Timeouts" that I concluded to happen because of the near infinite (or simply too many) records the date_calendar table in join with the Hours values produces.

    Solution - Automated Date Restriction:
    To solve this, I simply added the WHERE clause trying to constrain the data for only the last 365 days.

  5. #5
    Join Date
    Feb 2009
    Posts
    1,462

    Default Re: Concurrent Transactions

    Thanks for posting that feedback & tip Ben.

    In case of a long-running query that is expected, and not a SQL programming bug, remember you can set whatever query time-out you want in report design on the options tab. Absent any setting in the report, the default defined in the database connection is used -- and absent any setting defined there, I think Dynamic AI defaults to 60 seconds. (I forget....) In my experience its rare that I've seen the need to extend the timeout in a report.

    Bob

  6. #6
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Re: Concurrent Transactions

    Eddy,
    In our company we have a lot of people working overnight. I have encountered a problem where transactions that go from day 1 into day 2 do not show up at all.

    Code:
    SELECT date_calendar.calendar_date, Hours.Hr, Trns.TransIdno
    FROM date_calendar
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23)) AS Hours(Hr)
    LEFT OUTER JOIN dbo.TRANSACTIONS Trns ON 
    Trns.StartDate <= date_calendar.calendar_date AND (Trns.StopDate IS NULL OR Trns.StopDate >= date_calendar.calendar_date)
    AND Trns.StartHr <= Hours.Hr AND (Trns.StopHr IS NULL OR Trns.StopHr >= Hours.Hr)
    WHERE date_calendar.calendar_date >= '2019-03-09' AND date_calendar.calendar_date <= '2019-03-10'
    My assumption is that a transaction that a transaction started on 03/09/19 at 22:00 going to 03/10/19 until 01:00 should be listed 4 times (At 22:00 | 23:00 | 00:00 | 01:00).
    It is not showing up at all. Any suggestion where to start looking?

  7. #7
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    3,676

    Default Re: Concurrent Transactions

    I think that because Trans.StartHr is > Trans.StopHr... this could be part of the problem

    You might have to create a variable that adds 24 to Trans.StopHr when the StartDate and StopDate are different and use that in your selection criteria.

    M

  8. #8
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Re: Concurrent Transactions

    I also tried following with the same result.
    Code:
    SELECT date_calendar.calendar_date, Hours.Hr, Trns.TransIdno
    FROM date_calendar
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23)) AS Hours(Hr)
    LEFT OUTER JOIN dbo.TRANSACTIONS Trns ON 
    
    
    (Trns.StartDate <= date_calendar.calendar_date AND Trns.StopDate >= date_calendar.calendar_date)
    AND (CONVERT(datetime, Trns.StartDate + Trns.StartHr) <= CONVERT(datetime, date_calendar.calendar_date + Hours.Hr)) 
    AND (CONVERT(datetime, Trns.StopDate + Trns.StopHr) >= CONVERT(datetime, date_calendar.calendar_date + Hours.Hr))
    
    
    WHERE date_calendar.calendar_date >= '2019-03-09' AND date_calendar.calendar_date <= '2019-03-10'

  9. #9
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    2,378

    Default Re: Concurrent Transactions

    Hi Ben,

    Before I jump in, I need to make sure what is the desired result you want to achieve... So, basically, you want to list all "concurrent" transaction that was running in a giving hour ? Regardless if the transaction ended at 10:01 , or 10:59, both will count as "ACTIVE/CONCURRENT" for 10th hour of day ? Is that assumption correct ?

    Also, What is date_calendar table for ? Does it have all calendar days, or what ?

    Can you send me a sample data of your Transaction table (specially with the rows you are facing issue), in csv format, so I can play around here ? My e-mail is samuel at pizarros dot com dot br

    Regards
    Samuel Pizarro

  10. #10
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    2,378

    Default Re: Concurrent Transactions

    ok, I did some tests based on my assumptions...

    You can make it easier if you use CTE (Common Table Expression) and recursive query to generate the desired range timestamps.

    Example... this query bellow, will generate ALL hourly timestamps between 2019-03-10 00:00 and 2019-03-16 00:00
    Code:
    WITH BASETIME( BASETS ) AS 
    ( 
       VALUES  
          (TIMESTAMP('2019-03-10-00.00.00'))
       UNION ALL
       SELECT 
            (BASETS + 1 HOUR)
          FROM BASETIME
          WHERE BASETS <= '2019-03-15-24.00.00'
    )
    SELECT * FROM BASETIME
    Will produce the following result-set :
    Code:
     BASETS
     ---------------------
     2019-03-10 00:00:00.0
     2019-03-10 01:00:00.0
     2019-03-10 02:00:00.0
    ...
     2019-03-15 21:00:00.0
     2019-03-15 22:00:00.0
     2019-03-15 23:00:00.0
     2019-03-16 00:00:00.0
    As I dont have your transactions table data, I also "simulated" some sample transactions data, based on coluns from your 1st post..

    Code:
    WITH TRANSACTIONS (ID, STARTDATE, STARTTIME, STARTHR, STOPDATE, STOPTIME, STOPHR) AS 
    ( VALUES ( 1, '2019-03-09', '22:45', 22, '2019-03-10', '00:45', 0) ,
             ( 2, '2019-03-10', '22:45', 22, '2019-03-11', '01:45', 1) ,
             ( 3, '2019-03-11', '22:45', 22, '2019-03-12', '02:45', 2) ,
             ( 4, '2019-03-12', '22:45', 22, '2019-03-13', '03:45', 3) ,
             ( 5, '2019-03-13', '22:45', 22, '2019-03-14', '04:45', 4) ,
             ( 6, '2019-03-14', '22:45', 22, '2019-03-15', '05:45', 5) ,
             ( 7, '2019-03-15', '22:45', 22, '2019-03-16', '06:45', 6) ,
             ( 8, '2019-03-16', '22:45', 22, '2019-03-17', '07:45', 7) ,
             ( 9, '2019-03-17', '22:45', 22, '2019-03-18', '08:45', 8) ,
             (10, '2019-03-18', '22:45', 22, '2019-03-19', '09:45', 9) 
    )
    SELECT * FROM TRANSACTIONS ;
    this will result in the following result-set:
    Code:
     ID STARTDATE  STARTTIME STARTHR STOPDATE   STOPTIME STOPHR
     -- ---------- --------- ------- ---------- -------- ------
      1 2019-03-09 22:45          22 2019-03-10 00:45         0
      2 2019-03-10 22:45          22 2019-03-11 01:45         1
      3 2019-03-11 22:45          22 2019-03-12 02:45         2
      4 2019-03-12 22:45          22 2019-03-13 03:45         3
      5 2019-03-13 22:45          22 2019-03-14 04:45         4
      6 2019-03-14 22:45          22 2019-03-15 05:45         5
      7 2019-03-15 22:45          22 2019-03-16 06:45         6
      8 2019-03-16 22:45          22 2019-03-17 07:45         7
      9 2019-03-17 22:45          22 2019-03-18 08:45         8
     10 2019-03-18 22:45          22 2019-03-19 09:45         9
    All of them stating at 22:45 in a particular day, and ending at some time on next day.

    Now, you join both together:
    Code:
    WITH BASETIME( BASETS ) AS 
    ( 
       -- SELECT
       VALUES  
          (TIMESTAMP('2019-03-10-00.00.00'))
       UNION ALL
       SELECT 
            (BASETS + 1 HOUR)
          FROM BASETIME
          WHERE BASETS <= '2019-03-15-24.00.00'
    ) , 
    TRANSACTIONS (ID, STARTDATE, STARTTIME, STARTHR, STOPDATE, STOPTIME, STOPHR) AS 
    ( VALUES ( 1, '2019-03-09', '22:45', 22, '2019-03-10', '00:45', 0) ,
             ( 2, '2019-03-10', '22:45', 22, '2019-03-11', '01:45', 1) ,
             ( 3, '2019-03-11', '22:45', 22, '2019-03-12', '02:45', 2) ,
             ( 4, '2019-03-12', '22:45', 22, '2019-03-13', '03:45', 3) ,
             ( 5, '2019-03-13', '22:45', 22, '2019-03-14', '04:45', 4) ,
             ( 6, '2019-03-14', '22:45', 22, '2019-03-15', '05:45', 5) ,
             ( 7, '2019-03-15', '22:45', 22, '2019-03-16', '06:45', 6) ,
             ( 8, '2019-03-16', '22:45', 22, '2019-03-17', '07:45', 7) ,
             ( 9, '2019-03-17', '22:45', 22, '2019-03-18', '08:45', 8) ,
             (10, '2019-03-18', '22:45', 22, '2019-03-19', '09:45', 9) 
    )
    
    SELECT * FROM BASETIME LEFT JOIN TRANSACTIONS ON (
     BASETS BETWEEN TIMESTAMP(STARTDATE, STARTHR || ':00' ) AND TIMESTAMP(STOPDATE, STOPHR || ':00') )
    This now, will produce the following result-set :
    Code:
    BASETS                ID   STARTDATE  STARTTIME STARTHR STOPDATE   STOPTIME STOPHR
     --------------------- ---- ---------- --------- ------- ---------- -------- ------
     2019-03-10 00:00:00.0    1 2019-03-09 22:45          22 2019-03-10 00:45         0
     2019-03-10 01:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 02:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 03:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 04:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 05:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-10 22:00:00.0    2 2019-03-10 22:45          22 2019-03-11 01:45         1
     2019-03-10 23:00:00.0    2 2019-03-10 22:45          22 2019-03-11 01:45         1
     2019-03-11 00:00:00.0    2 2019-03-10 22:45          22 2019-03-11 01:45         1
     2019-03-11 01:00:00.0    2 2019-03-10 22:45          22 2019-03-11 01:45         1
     2019-03-11 02:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 03:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 04:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 05:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-11 22:00:00.0    3 2019-03-11 22:45          22 2019-03-12 02:45         2
     2019-03-11 23:00:00.0    3 2019-03-11 22:45          22 2019-03-12 02:45         2
     2019-03-12 00:00:00.0    3 2019-03-11 22:45          22 2019-03-12 02:45         2
     2019-03-12 01:00:00.0    3 2019-03-11 22:45          22 2019-03-12 02:45         2
     2019-03-12 02:00:00.0    3 2019-03-11 22:45          22 2019-03-12 02:45         2
     2019-03-12 03:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 04:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 05:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-12 22:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-12 23:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-13 00:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-13 01:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-13 02:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-13 03:00:00.0    4 2019-03-12 22:45          22 2019-03-13 03:45         3
     2019-03-13 04:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 05:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-13 22:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-13 23:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 00:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 01:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 02:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 03:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 04:00:00.0    5 2019-03-13 22:45          22 2019-03-14 04:45         4
     2019-03-14 05:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-14 22:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-14 23:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 00:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 01:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 02:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 03:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 04:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 05:00:00.0    6 2019-03-14 22:45          22 2019-03-15 05:45         5
     2019-03-15 06:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 07:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 08:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 09:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 10:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 11:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 12:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 13:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 14:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 15:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 16:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 17:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 18:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 19:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 20:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 21:00:00.0 NULL NULL       NULL         NULL NULL       NULL       NULL
     2019-03-15 22:00:00.0    7 2019-03-15 22:45          22 2019-03-16 06:45         6
     2019-03-15 23:00:00.0    7 2019-03-15 22:45          22 2019-03-16 06:45         6
     2019-03-16 00:00:00.0    7 2019-03-15 22:45          22 2019-03-16 06:45         6
    This is what I understood from your post.. Let me know if I didnt get it right...

    Oh, of course, you need to change it a litte bit, as you have your real transaction table ... basically, you don't need the 2nd temporary table definition (after the "," character in my sample.

    Another change, is that my sample has only completed transactions.. but I am sure you can change the join expression to count the transactions that are still running ...

    Last Note: I tested this with DB2 (dont have MSSQL installed here), but from what I searched the web, it should work ! If not, I guess minimal changes only will be required.

    good luck

    Regards
    Samuel Pizarro

Posting Permissions

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