Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table of Orders: Order Date, Shipping date and Selled_Price.
If the Price is 100 and the shipping date is 10 days after order day than the customer pay 10$ each day.
I need to create table showing how much money recieved per each order day.
Should i make a new table on script with a loop
Tnks,
It's always good to post some sample lines of input data and your expected result to play with and for verification, so I built something myself:
INPUT:
LOAD *, recno() as OrderID INLINE [
OrderDate, ShippingDate,SelledPrice
2013-06-19,2013-06-30, 100
2013-06-19,2013-06-30, 90
2013-06-09,2013-06-30, 100
2013-06-25,2013-06-30, 100
];
LOAD OrderID,
rangemax(0, if(SelledPrice=100, (ShippingDate-OrderDate-10)*10)) as Fee
RESIDENT INPUT;
Or if you want to group by OrderDate, replace last LOAD with
LOAD OrderDate,
sum(rangemax(0, if(SelledPrice=100, (ShippingDate-OrderDate-10)*10))) as SummedFee
RESIDENT INPUT order by OrderDate;