Add number total invoices to query

[jeroenw]

Hi,

I would like to add the total number of invoices created per customer to the query below.

Is this possible?

SELECT 
 T1.[CreateDate] as 'Datum klant ingegeven in SAP', 
 T0.[CardCode], 
 T0.[CardName] as 'Klantnaam op factuur', 
 T0.[DocDate] as 'Factuurdatum',
 T0.[DocDueDate] as 'Vervaldatum',
 DATEDIFF (DAY, GETDATE(), T0.DocDueDate) AS 'Dagen over tijd',
 T0.[DocNum] as 'Factuurnummer', 
 T0.[Installmnt] as 'Aantal termijnen',
 T2.[Status],
 T0.[DocTotal] as 'Totaalbedrag factuur incl. BTW',
 T0.[PaidSum],
 T0.[DocTotal] - T0.[PaidSum] as 'Openstaande bedrag', 
 T1.[Balance] as 'Totaal openstaande posten',
 T1.[DunnLevel],
 CASE
 WHEN T1.[QryGroup6]='Y' THEN 'JG'
 WHEN T1.[QryGroup7]='Y' THEN 'JG nieuw'
 WHEN T1.[QryGroup8]='Y' THEN 'SDJ'
 WHEN T1.[QryGroup9]='Y' THEN 'SDJ nieuw'
 WHEN T1.[QryGroup15]='Y' THEN 'PE'
 WHEN T1.[QryGroup14]='Y' THEN 'PE nieuw'
 ELSE 'Geen' END AS "Budi",
 T1.[CreditLine],
   CASE
   WHEN T1.[QryGroup64] = 'Y' THEN 'Alleen contant'
   WHEN T1.[QryGroup64] = 'N' THEN ' '
   END AS 'Alleen contant'
 FROM OINV T0  
 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
 INNER JOIN INV6 T2 ON T0.[DocEntry] = T2.[DocEntry]
 WHERE 
 T1.[CardType] = 'C' AND
 T0.[DocStatus] = 'O' AND
 T2.[Status] = 'O'
 ORDER BY 
 T1.[CreateDate] DESC

[Kuldeep]

Hello Jeroen,

I can understand that you want to add the total number of invoices to the query but your query already contains the DocNum i.e. Invoice Number field which is a unique number.

Do you want to see the DocNum as well in your query or just the total number of Invoices?

Regards,

Kuldeep

[jeroenw]

Hi Kuldeep,

Just only the total number of invoices.

Example:

A customer has received 56 invoices in 2022 and 40 invoices in 2023. The outcome must be then 96.

Thanks

[Johan_H]

Hi Jeroen,

Add this after 'Alleen contant':

, (select count(h.*) from OINV h where h.CardCode = T0.CardCode) AS [Totaal aantal fakturen voor dit klantnummer]

Regards,

Johan

[jeroenw]

Thanks Johan (again 🙂 )

But I get an error: "incorrect syntax near '*'"

  SELECT 
 T1.[CreateDate] as 'Datum klant ingegeven in SAP', 
 T0.[CardCode], 
 T0.[CardName] as 'Klantnaam op factuur', 
 T0.[DocDate] as 'Factuurdatum',
 T0.[DocDueDate] as 'Vervaldatum',
 DATEDIFF (DAY, GETDATE(), T0.DocDueDate) AS 'Dagen over tijd',
 T0.[DocNum] as 'Factuurnummer', 
 T0.[Installmnt] as 'Aantal termijnen',
 T2.[Status],
 T0.[DocTotal] as 'Totaalbedrag factuur incl. BTW',
 T0.[PaidSum],
 T0.[DocTotal] - T0.[PaidSum] as 'Openstaande bedrag', 
 T1.[Balance] as 'Totaal openstaande posten',
 T1.[DunnLevel],
 CASE
 WHEN T1.[QryGroup6]='Y' THEN 'JG'
 WHEN T1.[QryGroup7]='Y' THEN 'JG nieuw'
 WHEN T1.[QryGroup8]='Y' THEN 'SDJ'
 WHEN T1.[QryGroup9]='Y' THEN 'SDJ nieuw'
 WHEN T1.[QryGroup15]='Y' THEN 'PE'
 WHEN T1.[QryGroup14]='Y' THEN 'PE nieuw'
 ELSE 'Geen' END AS "Budi",
 T1.[CreditLine],
   CASE
   WHEN T1.[QryGroup64] = 'Y' THEN 'Alleen contant'
   WHEN T1.[QryGroup64] = 'N' THEN ' '
   END AS 'Alleen contant',
 (select count(h.*) from OINV h where h.CardCode = T0.CardCode) AS [Totaal aantal fakturen voor dit klantnummer]
 FROM OINV T0  
 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
 INNER JOIN INV6 T2 ON T0.[DocEntry] = T2.[DocEntry]
 WHERE 
 T1.[CardType] = 'C' AND
 T0.[DocStatus] = 'O' AND
 T2.[Status] = 'O'
 ORDER BY 
 T1.[CreateDate] DESC

[Johan_H]

Okay, I was afraid B1 might not like that syntax. Could you please try this:

(select count(*) from OINV h where h.CardCode = T0.CardCode) AS [Totaal aantal fakturen voor dit klantnummer]

[Kuldeep]

Hello Jeroen,

Please use the below query:

SELECT

T1.[CreateDate] as 'Datum klant ingegeven in SAP',

T0.[CardCode],

T0.[CardName] as 'Klantnaam op factuur',

T0.[DocDate] as 'Factuurdatum',

T0.[DocDueDate] as 'Vervaldatum',

DATEDIFF (DAY, GETDATE(), T0.DocDueDate) AS 'Dagen over tijd',

T0.[DocNum] as 'Factuurnummer',

(select count(T6.DocNum) from OINV T6 where T6.CardCode = T0.CardCode) AS [Totaal aantal fakturen voor dit klantnummer],

T0.[Installmnt] as 'Aantal termijnen',

T2.[Status],

T0.[DocTotal] as 'Totaalbedrag factuur incl. BTW',

T0.[PaidSum],

T0.[DocTotal] - T0.[PaidSum] as 'Openstaande bedrag',

T1.[Balance] as 'Totaal openstaande posten',

T1.[DunnLevel],

CASE

WHEN T1.[QryGroup6]='Y' THEN 'JG'

WHEN T1.[QryGroup7]='Y' THEN 'JG nieuw'

WHEN T1.[QryGroup8]='Y' THEN 'SDJ'

WHEN T1.[QryGroup9]='Y' THEN 'SDJ nieuw'

WHEN T1.[QryGroup15]='Y' THEN 'PE'

WHEN T1.[QryGroup14]='Y' THEN 'PE nieuw'

ELSE 'Geen' END AS "Budi",

T1.[CreditLine],

CASE

WHEN T1.[QryGroup64] = 'Y' THEN 'Alleen contant'

WHEN T1.[QryGroup64] = 'N' THEN ' '

END AS 'Alleen contant'

FROM OINV T0

INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN INV6 T2 ON T0.[DocEntry] = T2.[DocEntry]

WHERE

T1.[CardType] = 'C' AND

T0.[DocStatus] = 'O' AND

T2.[Status] = 'O'

ORDER BY

T1.[CreateDate] DESC