Help needed on history data with deltas/diffs, to calculate complete totals

[#1]

Hi all,

I have a set of Data I imported which contains data in this format:

UID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	A	2010-02-02	Some info 1	Some info 2
002	2	A	2010-02-02	Some info 1	Some info 2
003	1	B	2010-02-05	Some info 123	Some info 245
004	3	A	2010-02-05	Some info ABC	Some info DEF

Essentially, it contains the changes that are happening to a clients table, keeping the history data for the client changes (active, inactive).

Based on this table I need to extract the active clients for the dates where the updates occurred.

My problem is that I only have the changes (diffs or deltas as I usually refer to) and I can't figure out a way to calculate the number of clients which are active for a given date.

What happens is that if I select just one of the dates I get the count for that date, which will only contain those that were updated on that date and no the ones that are active but didn't have a change on that date (which is most of them).

I was wondering if someone could help me out on this.

The output should be something similar to this:

2010-02-02 2010-02-05

Active 80 85

Inactive 10 5

What I'm getting after several attempts will eventually get similar to this:

2010-02-02 2010-02-05

Active 80 5

Inactive 10 -

[#2]

Hi,

There should be two active orders for Client 1 on the 5th, since a new order came in, and another one was still active.

[Anonymous]

Really? I count two total records for client 1 on the 5th. One is A (I assume active) and the other B (inactive I guess). Either way, something like this should work

load client_id, update_date, sum(if(status='A',1,0)) as Active, sum(if(status='B',1,0)) as Inactive
resient YourSourceData group by client_id, update_date

[#4]

Hi,

As I mentioned in the post, the data only contains the differences, so if you look at it you will find that order nr 2 is still active on day 5, but since there were no changes, there wasn't a record created for order nr 2 on day 5, yet it should still be accounted as active. And this is what is taking my time, trying to figure out a solution for this.

I will see if I can do something with what you mention, but from a quick view I think probably not.

Thanks.

[RicardoRamos]

In logical terms here is what you need:

1-All records where the date is bigger than the update date minus all the records where the date is smaller than the update date;

2-All records with Active status to true.

Here is something that might work:

count({<STATUS={'A'}, UPDATE_DATE={"<= $(vDate)"}-{>$(vDate)}>}CLIENT_ID)

[#6]

Hi ricardo,

This should work for the client scenario.

Did you get the chance to look into the order scenario?

Thanks.