Counting over time having only start and end date

[#1]

I have a table with members of a club called MEMBERS (memberID is PK), then a table MEMBER_SUBSCRIPTION with memberID, subscriptionID, subscr_startdate, subscr_enddate, and then a table called SUBSCRIPTION with subscriptionID and description.

How do I count the number of members per subscriptionID per month? I need to display it in a graph.

Please help! Thanks!

Collin

[#2]

Hi Collin

I can imaging a few solutions but they depend mostly on

- how much data do you have
- how many details should be available for the user for selections

You could create a month table based on min(startdate) and max(enddate), join and group with it and count the montly totals in your query? With longer time ranges and many different selectable subscriptions this might however give you a lot of data.

You could also iterate over the possible month's for each subscription in the load and create for each month/member/subscription an individual fact entry. This will give you great flexibility in the selections. Do the generation of additional fact entries in the QVD creation script as it will otherwise slow down the data loading.

Jürg

[#3]

Hi Juerg,

We're talking 50.000 records (member subscriptions).

Your second suggestion sounds good. What do you mean with the "QVD creation script"? Can you provide some more details pls?

Thanks!

Collin

[#4 · Accepted solution]

Collin

I assume you will run your data extract during the night and create a (daily or weekly) qvd file from your data. If you prepare all data into the QVD file it will be quick to load.

I have made a small example of what I mean. Depending on how much member data you need you can either load that as a separate table or include the member information in the facts.

Jürg

[#5]

works like a charm!

Thanks Joerg!