Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have 3 tables:
SALES (with one row per sale of a product). Fields:
productid
date
price
PRODUCTS (with one row per product that can be sold). Fields:
productid
royaltyid
ROYALTIES (with one row per royalty amount per product and a date range)
royaltyid
amount
startdate
stopdate
How these tables are linked is pretty simple, with one product per sale. And one royalty per product and date of sale.
How would you calculate the sum of sales in this case? I would like to do something like "Sum(price - amount)" in my app, but that only works if I only have one fixed royalty for each product. In my case, some products have varying royalty amounts depending on date of sale, which makes things more complicated. I guess I could join the royalties table onto SALES during load-time using conditionals, or I could do some fancy Set Analysis in the App. How would you do it and why?
Cheers
I would use the intervalmatch() function on the startdate and stopdate of your ROYALTIES table.
This blog post IntervalMatch by Henric explains the method well..
I would use the intervalmatch() function on the startdate and stopdate of your ROYALTIES table.
This blog post IntervalMatch by Henric explains the method well..
Thanks, thats it!
I am a little bit afraid that I will count sales twice if I accidentally create overlapping periods in my ROYALTIES-table.