Skip to Content
0
Dec 16, 2022 at 10:44 AM

Need help for query DSO (days sales outstanding) - Almost done

90 Views

Hi,

The query below for calculating the DSO is not working. The problem is in this line:

SUM NULLIF  (T0.[Balance],0) / (T1.[DocTotal])  * 30 as 'test'

The balance needs to be devided DocTotal * 30. How can I do this?

 SELECT 
 T0.[CardCode], 
 T0.[CardName], 
 T0.[Balance], 
 SUM (T1.[DocTotal]) as 'Omzet',
 SUM NULLIF  (T0.[Balance],0) / (T1.[DocTotal])  * 30 as 'test'

 FROM 
 OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] 
 
 WHERE 
 T1.[DocDate] >=[%0] and T1.[DocDate] <=[%1] 
 
 GROUP BY 
 T0.[CardCode], 
 T0.[CardName], 
 T0.[Balance]