Sales by Bill To State Query question

[Former Member]

I have the following query that shows sales by Bill to State for a specified period of time.

SELECT T2.[State] AS 'Bill-To State', SUM(T0.[Max1099]) AS 'Gross Sales Amount' FROM  [dbo].[OINV] T0  INNER  JOIN [dbo].[OCRD] T1  ON  T1.[CardCode] = T0.[CardCode]  INNER  JOIN [dbo].[CRD1] T2  ON  T2.[CardCode] = T1.[CardCode]  AND  T2.[Address] = T0.[PayToCode]   WHERE T1.[GroupCode] <> 28 AND T0.[DocDate]>=[%0] AND T0.[DocDate]<=[%1] AND T2.[AdresType]='B' GROUP BY T2.[State]

If I create another query to capture credit memos (below), I get the following error:

"Can not add row without complete selection of batch/serial numbers"

SELECT T2.[State] AS 'Bill-To State', SUM(T0.[Max1099]) AS 'Gross Sales Amount' FROM  [dbo].[ORIN] T0  INNER  JOIN [dbo].[OCRD] T1  ON  T1.[CardCode] = T0.[CardCode]  INNER  JOIN [dbo].[RIN1] T2  ON  T2.[CardCode] = T1.[CardCode]  AND  T2.[Address] = T0.[PayToCode]   WHERE T1.[GroupCode] <> 28 AND T0.[DocDate]>=[%0] AND T0.[DocDate]<=[%1] AND T2.[AdresType]='B' GROUP BY T2.[State]

Does anyone have advice?

Thanks

Gary

[Former Member · Accepted solution]

Hi Gary,

[RIN1] is not the right table to join. It is CRD1.

Thanks,

Gordon

[Former Member]

Thank you so much for the advice on the incorrect table in the query.

It works correctly now.

Gary

[KennedyT21]

Try This

SELECT T2.[State] AS 'Bill-To State',

   SUM(T0.[Max1099]) AS 'Gross Sales Amount'

FROM [dbo].[ORIN] T0

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

INNER  JOIN [dbo].[CRD1] T2 ON T2.[CardCode] = T1.[CardCode]

AND T2.[Address] = T0.[PayToCode]

WHERE T1.[GroupCode] <> 28

  AND T0.[DocDate]>=[%0]

  AND T0.[DocDate]<=[%1]

  AND T2.[AdresType]='B'

GROUP BY T2.[State]

Regards,

Kennedy

[former_member212181]

Hi Gary,

Please try below Query.

SELECT T2.[State] AS 'Bill-To State', SUM(T0.[Max1099]) AS 'Gross Sales Amount'

FROM  [dbo].[ORIN] T0

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

  INNER  JOIN [dbo].[CRD1] T2  ON  T2.[CardCode] = T1.[CardCode] AND  T2.[Address] = T0.[PayToCode]

WHERE T1.[GroupCode] <> 28

  AND T0.[DocDate]>=[%0] AND T0.[DocDate]<=[%1]

  AND T2.[AdresType]='B'

GROUP BY T2.[State]

Thanks

Unnikrishnan