cancel
Showing results for 
Search instead for 
Did you mean: 

Total sales query with name select

Former Member
0 Kudos

Hi All

Trying to create a total sales query with following parameters -

Date from-to

Total invoice value less credit notes

Number of invoices

Where customer name contains xxxx

I can get to the point of individual invoices but would just like a sum of total value within that period.

Many thanks in advance.

Accepted Solutions (1)

Accepted Solutions (1)

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

try this:

select T0.BP, isnull(T0.INV,0) - isnull(T1.RIN,0) from

(select cardcode AS BP, isnull(sum(doctotal), 0) AS INV from oinv  where YOURCONDITIONS group by CardCode) T0

left join

(select cardcode AS BP,isnull(sum(doctotal), 0) AS RIN from orin wherer YOURCONDITIONS group by CardCode) T1

on T0.BP = T1.BP order by T0.BP

Kind regards

Agustín Marcos Cividanes

Former Member
0 Kudos

Unfortunately not working for me Agustin - no results at all (strange?)

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

Try this

/* SELECT T0.cardname FROM OCRD T0 */

DECLARE @cardname AS nvarchar(125)

SELECT @cardname =  '[%0]'

SET @cardname = '%' + @cardname + '%'

select T0.BP AS BP, isnull(T0.NUMI,0) AS Number, isnull(T0.INV,0) - isnull(T1.RIN,0) AS amount FROM

(select count(*) AS NUMI, cardcode AS BP, isnull(sum(doctotal), 0) AS INV from oinv  where cardname like  @cardname  group by CardCode) T0

left join

(select 0 AS NUMO, cardcode AS BP,isnull(sum(doctotal), 0) AS RIN from orin where  cardname like @cardname group by CardCode) T1 on T0.BP = T1.BP order by T0.BP

Kind regards

Agustín Marcos Cividanes

Answers (2)

Answers (2)

former_member205766
Active Contributor
0 Kudos

Hi Lisa

Can you check the Sales Analysis under A/R Invoice =>Sales Reports=> Sales Analysis?  I think it will fulfill you requirement.

With Regards

Balaji Sampath

Former Member
0 Kudos

Hi Balaji

Unfortunately not - with 10000 + records filtering is quite difficult.

Thanks to both Kennedy & Agustin - i think i should be able to modify and get what i need.

Cheers.

Lisa

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

when you have the right query please post and close the thread.

Kind regards.

Agustín Marcos Cividanes

KennedyT21
Active Contributor
0 Kudos

Try this

**************************************************/

SELECT T4.CardCode,

       T4.CardName,

       SUM(T0.Price * T0.Quantity) AS turnover,

       SUM(T0.Quantity) AS QTY,

       T2.ItmsGrpNam,

       T5.GroupName,

       T1.ItemCode,

       T1.ItemName

FROM   INV1 T0

       INNER JOIN OITM T1

            ON  T0.ItemCode = T1.ItemCode

       INNER JOIN OITB T2

            ON  T2.ItmsGrpCod = T1.ItmsGrpCod

       INNER JOIN OINV T3

            ON  T3.DocEntry = T0.DocEntry

       INNER JOIN OCRD T4

            ON  T1.CardCode = T4.CardCode

       INNER JOIN OCRG T5

            ON  T4.GroupCode = T5.GroupCode

WHERE  T4.CardName LIKE '[%0]%'

       AND T3.DocDate BETWEEN [%1] AND [%2]

GROUP BY

       T4.CardCode,

       T4.CardName,

       T2.ItmsGrpNam,

       T5.GroupName,

       T1.ItemCode,

       T1.ItemName

UNION ALL

SELECT T4.CardCode,

       T4.CardName,

       SUM(-T0.Price * T0.Quantity) AS turnover,

       SUM(-T0.Quantity) AS QTY,

       T2.ItmsGrpNam,

       T5.GroupName,

       T1.ItemCode,

       T1.ItemName

FROM   RIN1 T0

       INNER JOIN OITM T1

            ON  T0.ItemCode = T1.ItemCode

       INNER JOIN OITB T2

            ON  T2.ItmsGrpCod = T1.ItmsGrpCod

       INNER JOIN ORIN T3

            ON  T3.DocEntry = T0.DocEntry

       INNER JOIN OCRD T4

            ON  T1.CardCode = T4.CardCode

       INNER JOIN OCRG T5

            ON  T4.GroupCode = T5.GroupCode

WHERE  T4.CardName LIKE '[%0]%'

       AND T3.DocDate BETWEEN [%1] AND [%2]

GROUP BY

       T4.CardCode,

       T4.CardName,

       T2.ItmsGrpNam,

       T5.GroupName,

       T1.ItemCode,

       T1.ItemName

Regards

Kennedy