I have 2 tables in my database, i want to select out the InvoiceID, ClientID and total Amount for the invoice.
I know i can simply sum all the amount in InvoiceItem and Group by InvoiceID, ClientID to select the data,
but the problem is the amount for the items are differ depending on group.
IF group 1, sum all item under group 1,
if group 2 , sum of group 1 - sum all item under group 2 etc.....
I tried to use User Defined Function (UDF) to calculate the amount per invoice in my select statement, it gave me error:
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Above error is cause of I have dynamic select statement in the User Defined Function (UDF).
I know that I cant have dynamic statement in UDF, and I can use if else statement here, but pls imagine I have more than 10 groups of different calculation.
I tried stored procedure, but I cant execute or call the stores procedure in my select statement.
Anyone out there can advise?
Below are the structure of my tables.
Invoice {
InvoiceID int,
ClientID int
}
InvoiceItem{
InvoiceID int,
ProductID int,
Price decimal(18,2),
Group int
}