Skip to Content

Need help with query - Sales Analysis with GM

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:


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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 23, 2017 at 08:30 AM

    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]
    Add comment
    10|10000 characters needed characters exceeded