Counting Changes to Database

JustinW
JustinW used Ask the Experts™
on
I'm trying to figure out the most pain free way to count the number of assets that transfer in and out of our database by month.

Our data has a similar set up to this in MySql, but maybe a DB Agnostic solution would work too.

CREATE DATABASE delete_this;

use `delete_this`;

CREATE TABLE transactions
(
    sys_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    asset_id CHAR(5) NOT NULL,
    report_date DATE
)
;

INSERT INTO `transactions`(asset_id, report_date) VALUES
('a','2011-09-30'),
('b','2011-09-30'),
('c','2011-09-30'),
('a','2011-10-31'),
('b','2011-10-31'),
('d','2011-10-31'),
('e','2011-11-30'),
('b','2011-11-30'),
('c','2011-11-30'),
('d','2011-11-30')
;

Open in new window


The query I'm trying to create should have the following results:

Report_Date -> Transfer_In -> Transfer_Out
2011-09-30 -> 3 -> 0
2011-10-31 -> 1 -> 1
2011-11-30 -> 3 -> 1

Any thoughts?
Thanks,
-JWW
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
You can't do that directly as you don't know which entry represents IN vs. OUT in my opinion but you could do somethinmg like below:

Select cast(report_date as date), count(asset_id) as asset_count from transactions
group by cast(report_date as date)

Commented:
I would start with creating a GroupBy query to have the asset_id, MIN(report_date) and MAX(report_date)
As I assume that each asset_id has only one in and one out date.

Next this query can be used in a union like:
select min_reportdate as Report_Date , count(*) as Transfer_In , 0 as Transfer_Out from qryGroupBy group by min_reportdate
UNION
select max_reportdate, 0, count(*) from qryGroupBy group by max_reportdate

And finally another GroupBy to get the MAX values from the UNION query per Report_Date will give the final result.

Author

Commented:
The really tricky part about this is that an asset can come in, stay for two months, leave for two months, come in for another two months, then leave again forever.
Commented:
Makes the matter more complex, but can be solved by adding in the first view a column with the sequencenumber of an asset_id like:

select a.asset_id, (select count(*) from transactions b where a.asset_id = b.asset_id and a.report_date <= b.report_date) as seqnum, a.report_date
from transactions a

Now we can use the seqnum to JOIN two queries to get the in and out dates.
The first query is selecting the fields from the above query, the other the same, but a seqnum - 1
Now you can JOIN these two queries on asset_id and seqnum to get the start and enddate. Make sure it's an OUTER JOIN for assets that only entered and didn't leave.

Finally my last query above can be used to get the view and count based on the dates.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial