Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Concurrent Transactions

  1. #11
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    2,006

    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

  2. #12
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    164

    Default Re: Concurrent Transactions

    Hi Samuel,
    Thank you for your extensive help on this.

    To answer a few of your previous questions:
    - I have a table (date_calendar) with data that I use in my applications and Dynamic AI.
    - The transactions look exactly like what you have in your example.

    The trick for me was to combine date + time properly, like seen in your example. It took me a few tries (and a bit of reading) to get it right. Seems like TIMESTAMP does not exist in MSSQL/DynamicAI but DATEADD accomplishes the same.

    Code:
    SELECT
    date_calendar.calendar_date,
    Hours.Hr,
    DATEADD(hour, Hours.Hr, date_calendar.calendar_date) AS BASETS
    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)
    WHERE date_calendar.calendar_date >= '2019-03-08' AND date_calendar.calendar_date <= '2019-03-09'
    ) AS BASETIME
    LEFT JOIN dbo.TRANSACTIONS Trns ON (
    BASETS BETWEEN DATEADD(hour, Trns.StartHr, Trns.StartDate) AND DATEADD(hour, Trns.StopHr, Trns.StopDate))
    Thanks again.

  3. #13
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    2,006

    Default Re: Concurrent Transactions

    Glad to help!

    PS. I think MSSQL equivalent to DB2 TIMESTAMP is DATETIME or DATETIME2. But I guess you sorted it out with dateadd function already..

    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
  •