on 08-22-2017 1:31 PM
I am trying to do a slaes analysis report. The info I am trying to end up with is
Customer (Code and Name)
Sales Order number
Sales Order Total
GM for Each Sales Order
I tried doing the following which gives me all of the line detail:
SELECT
T1.[DocNum] AS 'Document Number',
T1.[CardCode] AS 'Customer/Vendor Code',
T1.[CardName] AS 'Customer/Vendor Name',
T1.[DocDate] AS 'Posting Date',
T1.[TaxDate] AS 'Document Date',
T0.[ItemCode] AS 'Item No.',
T0.[Dscription] AS 'Item/Service Description',
T0.[Quantity] AS 'Quantity',
T0.[GrossBuyPr] AS 'Gross Buy Price',
T0.[Price] AS 'Unit Price'
FROM [dbo].[RDR1] T0 INNER JOIN [dbo].[ORDR] T1 ON T1.[DocEntry] = T0.[DocEntry]
Then I would export to Excel and calculate the GM for each line. The problem is when I have to total each Sales Order and then group by Customer. I would appreciate any help possible.
Thank you,
Keith H
Hi Keith,
try this
SELECT Distinct T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], Sum(T1.[LineTotal]) [TotalAmount Without Tax] ,(SUM(T1.LineTotal) - SUM(T1.GrossBuyPr * T1.Quantity)) [Gross Profit] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
Group By T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.