Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to simply group some SUMS by an ID whilst if possilbe setting a date for a date range my first issue is an expression issue and I can't work out why and I can't get the dates set-up to work either.
I have an InvoiceLine table
with
IntEnrold, dteFromDate, dteToDate daily_total calculated using (curBasic) / (interval(dteToDate-dteFromDate, 'D')) AS curBasicDaily_Total,
I would like to produce a single table grouped by IntEnrolId with a SUM(daily_total) for each day.
My Code below if someone could suiggest where I might be going wrong it would be appreciated:
Invoice_x_Dates:
Load
intEnrolId,
SUM(InvoiceLine.curBasicDaily_Total) ,
SUM(InvoiceLine.curDiscountDaily_Total) ,
SUM(InvoiceLine.curDueDaily_Total) ,
SUM(InvoiceLine.curCommissionBasicDaily_Total)
Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate
Resident InvoiceLine
While IterNo() <= InvoiceLine.dteToDate - InvoiceLine.dteFromDate + 1
Group By intEnrolId;
Many Thanks
David
Invoice_x_Dates:
Load
intEnrolId,
SUM(InvoiceLine.curBasicDaily_Total) ,
SUM(InvoiceLine.curDiscountDaily_Total) ,
SUM(InvoiceLine.curDueDaily_Total) ,
SUM(InvoiceLine.curCommissionBasicDaily_Total),
Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate
Resident InvoiceLine
While IterNo() <= InvoiceLine.dteToDate - InvoiceLine.dteFromDate + 1
Group By intEnrolId;
Try this
Invoice_x_Dates:
Load
intEnrolId,
SUM(InvoiceLine.curBasicDaily_Total) ,
SUM(InvoiceLine.curDiscountDaily_Total) ,
SUM(InvoiceLine.curDueDaily_Total) ,
SUM(InvoiceLine.curCommissionBasicDaily_Total) ,
Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate //If error comes below code and comment this one
// Date(InvoiceLine.dteFromDate + rowno() -1 ) AS INDate
Resident InvoiceLine
While InvoiceLine.dteFromDate+iterno()-1<= InvoiceLine.dteToDate
Group By intEnrolId;
Thanks for the quick reply but still getting invalid Expression...however when I remove the SUMS and group by the dates part of the script does work!
confused still !!
Ps now sorted thanks..I had an error in my code I had not added all the group by fields I needed...thanks again