Viewing Total Cumulative Purchases For BP Customer

[Former Member]

Hello,

Is there any way that we can add a column to the below Query that shows the total cumulative YTD sales for each account?

In the Query below, the Balance column shows current outstanding balance on the account. However, this is different than cumulative sales and would really help us if we could gauge how much the customer has purchased from us within the past year.

If possible, can we put a column with this information at the end of the report -- with header 'YTD Sales'

Thanks!!

Mike

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address

' Address', T0.City ' City', T0.State1 State, T0.ZipCode

'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',

T1.SlpName 'Sales Rep',

T2.PymntGroup Terms, T3.GroupName 'Group'

FROM OCRD T0

LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode

LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum

LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode

WHERE T0.CardType = 'C'

[Former Member · Accepted solution]

Mike,

The query you asked for will only show sales info up to the current month. If it is in the beginning ot the year such as January, the query will become not that useful. Do you want a one year moving total calculated from the current day?

Thanks,

Gordon

[Former Member · Accepted solution]

We want the total sales Year To Date. So, I think this is a Moving Total, right? Is this what Suda's Query does?

For example, today it would show Jan 1, 08 - Sept 30, 08. Then tomorrow it would show sales for Jan 1, 08 - Oct 1, 08.

The next year, 2009, it would show the YTD total for 2009 and forget 2008.

Does that make sense?

Thanks!

Mike

[former_member583013 · Accepted solution]

Mike,

That is what my query will do. It just takes the current year into consideration.

You should be good to go

Suda

[Former Member · Accepted solution]

Suda's query can be used to fulfil your goal. In the meantime, another moving sale query might be useful to you too. Because in the beginning of year, you can only get too little data for your sales.

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Balance ' Balance', ((SELECT SUM(LINETOTAL) FROM INV1 INNER JOIN OINV 
ON INV1.DocEntry = OINV.DocEntry WHERE OINV.CardCode = T0.CardCode AND 
Datediff(DD, INV1.DocDate,GetDate()) < 365)-(SELECT SUM(LINETOTAL) 
FROM RIN1 INNER JOIN ORIN ON RIN1.DocEntry = ORIN.DocEntry
WHERE ORIN.CardCode = T0.CardCode AND Datediff(DD, RIN1.DocDate,GetDate()) < 365)) [12M Sales],
T1.SlpName 'Sales Rep',T2.PymntGroup Terms, T3.GroupName 'Group'
, T0.Address ' Address', T0.City ' City', T0.State1 State, T0.ZipCode
'Billing Zip', T0.Phone1 Phone
FROM OCRD T0
LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum
LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode
WHERE T0.CardType = 'C'

Thanks,

Gordon

[Former Member · Accepted solution]

Hey Guys --

Thanks for the help!

Gordon -- I'm not sure what the difference is between your Query and Suda's Query except where the column for YTD sales is placed.

Also, for both Queries, it doesn't look like the YTD sales is correct. When I look at the total YTD for all our BP Customers, the number should be much higher than what I am seeing?

Is it possible that it is only taking into account 1 month of sales rather than multiple months (YTD)? The total YTD should have been 3-4 times higher than what I am seeing.

Please advise?

Thanks!!

Mike

[former_member583013 · Accepted solution]

Mike,

Current month's Sales query

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address
' Address', T0.City ' City', T0.State1 State, T0.ZipCode
'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',
T1.SlpName 'Sales Rep',
T2.PymntGroup Terms, T3.GroupName 'Group', ((SELECT ISNULL(SUM(INV1.LINETOTAL),0) 
FROM INV1 INNER JOIN OINV ON INV1.DocEntry = OINV.DocEntry 
WHERE OINV.CardCode = T0.CardCode AND 
Month(OINV.DocDate) = Month(GetDate()))-(SELECT 
ISNULL(SUM(RIN1.LINETOTAL),0) FROM RIN1 INNER JOIN 
ORIN ON RIN1.DocEntry = ORIN.DocEntry 
WHERE ORIN.CardCode = T0.CardCode AND Month(ORIN.DocDate) = Month(GetDate()))) [Monthly Sales] 
FROM OCRD T0
LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum
LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode
WHERE T0.CardType = 'C'

[Former Member · Accepted solution]

Suda --

Something is not right here.

The Current Month Query you just posted shows Total Monthly sales way below what it should have been.

Actually, the YTD Query seems closer in line to our total monthly sales than the monthly in this recent posting. The recent posting is about 8-9X off from what it actually is.

Are we pulling data from the right source?

I am looking for Total Year To Date Sales for each customer. So, the total of their purchase (minus credit memos) from Jan-current day. In our case, since we launched on July 1, it would just have cumulative sales data for July-current day.

Does this make more sense?

Thanks!

Mike

[former_member583013 · Accepted solution]

When no Credit Memo's existed the values where NULL so I had to change the query. Both the monthly and yearly query should work now.

YEARLY QUERY

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address
' Address', T0.City ' City', T0.State1 State, T0.ZipCode
'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',
T1.SlpName 'Sales Rep',
T2.PymntGroup Terms, T3.GroupName 'Group', ((SELECT ISNULL(SUM(INV1.LINETOTAL),0) 
FROM INV1 INNER JOIN OINV ON INV1.DocEntry = OINV.DocEntry 
WHERE OINV.CardCode = T0.CardCode AND Year(INV1.DocDate) = Year(GetDate()))-(SELECT ISNULL(SUM(RIN1.LINETOTAL),0) 
FROM RIN1 INNER JOIN ORIN ON RIN1.DocEntry = ORIN.DocEntry 
WHERE ORIN.CardCode = T0.CardCode AND Year(RIN1.DocDate) = Year(GetDate()))) [YTD Sales] 
FROM OCRD T0
LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum
LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode
WHERE T0.CardType = 'C'

You can use the monthly report from my previous post

Suda

[Former Member]

Hi Mike,

This query will only pull all info from BP tables only.

What you need now is totally different. Should two reports be fine for your purpose?

Thanks,

Gordon

[Former Member]

Sure, making another report is fine!

Just to clarify, do you mean you will create a separate report b/c we cannot add a column showing the total YTD cumulative sales of each account to the existing Query?

Thanks!

Mike

[former_member583013]

Mike,

By Sales, are you referring to Invoice Totals? If then it is very much possible to add a column.

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address

' Address', T0.City ' City', T0.State1 State, T0.ZipCode

'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',

T1.SlpName 'Sales Rep',

T2.PymntGroup Terms, T3.GroupName 'Group', (SELECT SUM(LINETOTAL) FROM INV1 INNER JOIN OINV ON INV1.DocEntry = OINV.DocEntry WHERE OINV.CardCode = T0.CardCode AND Year(INV1.DocDate) = Year(GetDate())) [YTD Sales\]

FROM OCRD T0

LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode

LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum

LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode

WHERE T0.CardType = 'C'

Suda

[Former Member]

Hi Suda --

Yes, by total YTD sales I am referring to (Invoice Total - Credit Memo). So the total amount that this customer has ordered from us YTD.

Thanks!

Mike

[former_member583013]

Mike,

This one takes into account both Invoices and Credit Memos

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address
' Address', T0.City ' City', T0.State1 State, T0.ZipCode
'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',
T1.SlpName 'Sales Rep',
T2.PymntGroup Terms, T3.GroupName 'Group', ((SELECT ISNULL(SUM(INV1.LINETOTAL),0) FROM INV1 INNER JOIN OINV ON INV1.DocEntry = OINV.DocEntry WHERE OINV.CardCode = T0.CardCode AND Year(INV1.DocDate) = Year(GetDate()))-(SELECT ISNULL(SUM(RIN1.LINETOTAL),0) FROM RIN1 INNER JOIN ORIN ON RIN1.DocEntry = ORIN.DocEntry WHERE ORIN.CardCode = T0.CardCode AND Year(RIN1.DocDate) = Year(GetDate()))) [YTD Sales] 
FROM OCRD T0
LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum
LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode
WHERE T0.CardType = 'C'

PS: I have replied you on the other thread on the issue of the Header breaking into Rows.