1 Attachment(s)
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.
[ATTACH=CONFIG]10500[/ATTACH]
Bob
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
Re: Concurrent Transactions
Ben,
For other variations on this same or at least similar theme check out this thread: [URL="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
[/URL]
Let us know what you end up doing.
Bob
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())
[/CODE]
[U]One issue I ran into - Query Timeouts:[/U]
I kept experiencing "Query Timeouts" that I concluded to happen because of the near infinite (or simply too many) records the [I][B]date_calendar[/B][/I] table in join with the [I][B]Hours[/B][/I] values produces.
[U]Solution - Automated Date Restriction:[/U]
To solve this, I simply added the WHERE clause trying to constrain the data for only the last 365 days.
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
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'
[/CODE]
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?
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
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'
[/CODE]
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 [B]date_calendar [/B]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