PDA

View Full Version : Concurrent Transactions



Benjamin
19-Dec-2016, 02:50 PM
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?

Bob Cergol
20-Dec-2016, 08:51 AM
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.

10500

Bob

Eddy Kleinjan
20-Dec-2016, 12:16 PM
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

Bob Cergol
20-Dec-2016, 11:52 PM
Ben,

For other variations on this same or at least similar theme check out this thread: http://support.dataaccess.com/Forums/showthread.php?57340-From-two-dates-obtain-daily-bed-occupation-in-a-hospital
(http://support.dataaccess.com/Forums/showthread.php?57340-From-two-dates-obtain-daily-bed-occupation-in-a-hospital)
Let us know what you end up doing.

Bob

Benjamin
13-Mar-2018, 10:21 AM
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).



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.

Bob Cergol
15-Mar-2018, 08:28 AM
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

Benjamin
14-Mar-2019, 01:47 PM
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.



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?

Mike Cooper
14-Mar-2019, 02:11 PM
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

Benjamin
14-Mar-2019, 02:30 PM
I also tried following with the same result.


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'

Samuel Pizarro
14-Mar-2019, 06:15 PM
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
14-Mar-2019, 09:21 PM
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


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 :


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..



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:

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:


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 :


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

Benjamin
18-Mar-2019, 03:19 PM
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.


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.

Samuel Pizarro
18-Mar-2019, 05:06 PM
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