% of open invoices by service month

[Former Member]

Service month is =T0.[TaxDate] How can I get the % open by service month. Not sure how I would write this formula. TO.[DocTota], T0.[DocStatus]= '0'/TO.[DocTota],T0.[DocStatus]='C' * 100 From OINV T0

or Select TO.[DocTota],(Sum(Where T0.[DocStatus]= '0'))/ TO.[DocTota],(Sum(Where T0.[DocStatus]= 'C')) * 100 From OINV TO Where To.[TaxDate] = '7/31/2016'

[Former Member · Accepted solution]

Thank you so much Augusto. This is very helpful. If I want the amount still open. How would I get this? Example below I also think the formula is wrong as if you have $100 Total Invoiced, and $80  are still open you would have a formula =SUM(80/100)*100 as 80% open.

[Former Member · Accepted solution]

Basically I would like the percent of "Total Open" Sum(T0.DocTotalSy - T0.PaidSys) as 'Total Open Amount'

[former_member186712 · Accepted solution]

Hi Danielle,

You just need to replace the "COUNT(T1.DocEntry)" by "Sum(T1.DocTotalSy - T1.PaidSys)".Be carefull with the T0 and T1.

Here is how it should look like.

Select Sum(T0.DocTotalSy) as 'Total Invoices', Sum(T1.DocTotalSy - T1,Paydsys) as 'Total Open', ((Sum(T0.DocTotalSy) - Sum(T1.DocTotalSy - T1,Paydsys)) * 100) / Sum(T1.DocTotalSy - T1,Paydsys)  as '% Open'   From OINV T0 left outer join oinv T1 on t0.docentry = t1.docentry and T1.DocStatus = 'O' Where T0.[TaxDate] between  '2016/06/01' and  '2016/06/30'

Don't forget to mark the asnwers has Helpfull or Correct answer.

[Former Member · Accepted solution]

Thank you for this Augusto! I do still get an error around the sum function. It says it requires 1 argument. 1). [Microsoft][SQL Server Native Client 10.0][SQL Server]The Sum function requires 1 argument 'Service Contracts' (OCTR) (s). 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

[former_member186712 · Accepted solution]

My query is wrong because I wrote " T1,paydsys" and should be "T1.Paidsys".

the problem is the comma and the name of the field

[Former Member · Accepted solution]

Thanks it is now working, but would the total open match the AR for what is open? They are way off.. Thank you though , you did help me understand writing a formula.

[Former Member · Accepted solution]

I will check June 2016 and let you know if it is accurate.

[Former Member · Accepted solution]

yes June is correct. Thank you for your help!!

[Former Member · Accepted solution]

I changed the formula a bit as I was not getting the correct percentage, I just divided the open by the total and it worked. Select Sum(T0.DocTotalSy) as 'Total Invoices', Sum(T1.DocTotalSy - T1.Paidsys) as 'Total Open', (( Sum(T1.DocTotalSy - T1.Paidsys) / Sum(T0.DocTotalSy)) * 100) From OINV T0 left outer join oinv T1 on t0.docentry = t1.docentry and T1.DocStatus = 'O' Where T0.[TaxDate] between  '2016/06/01' and  '2016/06/30'

[former_member186712]

Hi Danielle,

Try the below query and adjust for what you need.

Select COUNT(T0.DocEntry) as 'Total Invoices', COUNT(T1.DocEntry) as 'Total Open', ((COUNT(T0.DocEntry) - COUNT(T1.DocEntry)) * 100) / COUNT(T1.DocEntry)  as '% Open'   From OINV T0 left outer join oinv T1 on t0.docentry = t1.docentry and T1.DocStatus = 'O'

Where T0.[TaxDate] between  '2016/06/01' and  '2016/06/30'

Regards,

Augusto