Multiple Dates

[celauritz]

I have a table that includes these four columns:

Account #
Service Code
Start Date
End Date

I have been able to see how many accounts have a certain service obviously quite easily by simply using the select expert and saying start date < x <= end date.

Is there any way to group a report for multiple dates? For instance, I would like to be able to see how many people had a service on each of the last seven days. I have done this with seven date formulas running totals, but it makes the report run inefficiently (and I would like to go back 30 days or more eventually).

 

[lbass]

Is there more than one row per Acct#? Or are you grouping by service code and then by Acct#? Or? It would help to see some sample data.

-LB

 

[celauritz]

Account Svc Start End
123 BAS 20080101 20080113
123 EBA 20080101 20080113
124 BAS 20080103
124 EBA 20080102 20080115
124 EBA 20080103 20080115
125 BAS 20071206 20071225
125 BAS 20080101
125 EBA 20081206 20071225
125 EBA 20080101

I would like to group it by date and then by service so, for instance, for the table above, it would look like

20080102
BAS 2
EBA 3
20080103
BAS 3
EBA 3

 

[lbass]

Maybe I'm missing something. What you are showing has nothing to do with whether someone had a service in the last 7 days. In fact all you would need to do to get the above display is to group on start date and then on service and then insert a count on acct #.

-LB

 

[celauritz]

I guess I am not being clear.

I want to know how many accounts (distinct count) as well as how many instances of a service (count) had a certain service on each day of a given date range.

I have accomplished this by writing 31 formulas (one for each day of the current month), and making a running total for each day of the month with the selection formula inside it start<x<end for all 31 formulas, and grouping it by service. This is inefficient, however, I don't think there is any other way, but I thought I would ask.