StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL pro rata counting of distinct values in last period
text
Body
copied!<p>This one is quite tricky i've been scratching my head all day.</p> <p>I have a table containing billing periods</p> <pre><code>ID | DATEBEGIN | DATEEND | CUSTOMER_ID =======+===========+==========+================= 1 | 1/1/2011 | 30/1/2011 | 1 </code></pre> <p>I have a table containing 'sub customers'</p> <pre><code>ID | NAME =======+=========== 1 | JOHN 2 | JACK 3 | Elwood </code></pre> <p>I have a table containing items purchased on a subscription (wholesale account</p> <pre><code>ID | DATE | SUBCUSTOMER_ID | CUSTOMER ID =======+===========+================+============== 1 | 15/1/2011 | 1 | 1 2 | 18/1/2011 | 1 | 1 3 | 25/1/2011 | 2 | 1 4 | 28/1/2011 | 3 | 1 </code></pre> <p>So I want to count 'credits' to deduct from their account. So the subscription is per 'sub customer'.</p> <p>So at the end of the billing period (30/1/2011 from first table). I need to count the distinct sub customers (there are 3). They are charged pro-rata from the first purchase they make during the billing period.</p> <pre><code>Days Having Subscription | SUBCUSTOMER_ID | Pro Rata Rate | CUSTOMER_ID ==========================+===================+==================+============== 3 | 3 | 3/30 | 1 6 | 2 | 6/30 | 1 16 | 1 | 16/30 | 1 </code></pre> <p>The output should therefore be</p> <pre><code>CUSTOMER_ID | BILLING CREDITS ============+======================== 1 | 25/30 </code></pre> <p>I have to count it pro rata, previously it would be unfair to bill a full period even if they purchase an item 1 day prior to the billing date</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload