Assistance on simple query

Hi All

I am working on a query and have become stuck on how to proceed..

The query is returning data from 2 main tables ORDR (Customer Orders) and RDR1 (Lines on each order)

I'm trying to get the Total Discount amount per order but I'm not even sure how to go about it..

I believe I can get the total discount amount of each order line like this:
(Gross price each item - net price each item) * Quantity

(RDR1.PriceBefDi - RDR1.Price) * RDR1.Quantity

But how should I get the SUM of the discount amount of each line to find the Total amount of discount on the whole order?

I copy part of my query below for clarity and would very much appreciate any help or pointers..

SELECT
, [ORDR].[CardCode] AS [orderCustomerID]
, [ORDR].[PayToCode] AS [orderBillToCustomerID]
, [OTER].[TerritryID] AS [orderBillToTerritoryID]
, [OTER].[descript] AS [orderBillToTerritoryName]
, [ORDR].[DocNum] AS [orderNumber]
, CASE
WHEN [ORDR].[DocStatus] = 'C'
THEN 0
ELSE 1
END AS [orderOpen]
, CASE
WHEN [ORDR].[CANCELED] = 'Y'
THEN 1
ELSE 0
END AS [orderVoid]
, [ORDR].[NumAtCard] AS [orderCustomerPONumber]
, [ORDR].[DocDate] AS [orderDate]
, [ORDR].[GroupNum] AS [orderTerms]
, [RDR1].[LineStatus] AS [orderLineOpen]
, [RDR1].[LinManClsd] AS [orderLineVoid]
, [RDR1].[LineStatus] AS [orderLineStatus]
, [RDR1].[BaseRef] AS [orderQuoteNum]
, [RDR1].[BaseLine] AS [orderQuoteLine]
, [RDR1].[Quantity] AS [orderLineQty]
, [RDR1].[PriceBefDi] AS [orderPriceEAOrderedCurrency]
, ([RDR1].[PriceBefDi][RDR1].[DiscPrcnt]/100) AS [orderDiscountEAOrderedCurrency]
, [ORDR].[DocTotal]
[ORDR].[DocRate] AS [orderReleaseTotalPriceOrderedCurrency]
, ([ORDR].[DiscSum][ORDR].[DocRate]) AS [orderReleaseTotalDiscountOrderedCurrency]
, [ORDR].[VatSum] AS [orderReleaseTotalTaxOrderedCurrency]
, ([ORDR].[DocTotal]
[ORDR].[DocRate])-[ORDR].[VatSum] AS [orderReleaseAmountNetOrderedCurrency]
FROM ORDR
JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry
JOIN OCRD ON ORDR.CardCode = OCRD.CardCode
JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode AND CRD1.Address = ORDR.ShipToCode AND CRD1.AdresType = 'S'
JOIN OSLP ON ORDR.SlpCode = OSLP.SlpCode
JOIN OUSR ON ORDR.UserSign = OUSR.USERID
JOIN OTER ON OCRD.Territory = OTER.TerritryID
JOIN OITM ON RDR1.ItemCode = OITM.ItemCode
JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod

Hi Miro,

Can you give us the needed CREATE TABLE and INSERT INTO scripts for the tables with some sample data. Based on the sample data, what is the result you expect?

It's quite a query, with a lot of JOINs. Can you provide us with a simplified version? One that only contains ORDR and and RDR1? That will reduce the amount of work for you too (when writing the CREATE TABLE and INSERT INTO scripts).

No idea if this is correct, but it may get you started.

Create some tables with test data.

DROP TABLE IF EXISTS dbo.ORDR;	--Customer Orders
DROP TABLE IF EXISTS dbo.RDR1;	--Lines on each order
GO
CREATE TABLE dbo.ORDR(
	DocEntry	int	NOT NULL PRIMARY KEY,
	DocNum		int	NOT NULL,
	OrderDate	Date	NOT NULL
)

CREATE TABLE dbo.RDR1(
	id		int	IDENTITY(1, 1)	NOT NULL PRIMARY KEY,
	DocEntry	int	NOT NULL,
	PriceBefDi	float	NOT NULL,
	Price		float	NOT NULL,	
	Quantity	int	NOT NULL
)
GO
INSERT INTO dbo.ORDR (DocEntry, DocNum, OrderDate) VALUES
(1, 100, '20200201'),
(2, 101, '20200202')

INSERT INTO dbo.RDR1(DocEntry, PriceBefDi, Price, Quantity) VALUES
(1, 100.0, 98.35, 12),
(1, 50.0, 50.0, 120),
(1, 1000.0, 20.0, 1)
--no order lines for order 2
GO

This is the query to get the total discount for all the orders with an orderDate in the current year.

;WITH CTE AS (
SELECT ORDR.DocEntry
	, SUM(Price * Quantity) as OrderPrice
	, SUM((PriceBefDi - Price) * Quantity) AS OrderDiscount
FROM dbo.ORDR as ORDR
	INNER JOIN dbo.RDR1 as RDR1
		ON ORDR.DocEntry = RDR1.DocEntry
WHERE YEAR(ORDR.OrderDate) = 2020
GROUP BY ORDR.DocEntry
)
SELECT ORDR.DocEntry
	, ORDR.DocNum
	, ORDR.OrderDate
	, COALESCE(CTE.OrderPrice, 0.0) as OrderPrice
	, COALESCE(CTE.OrderDiscount, 0.0) as OrderDiscount
FROM dbo.ORDR as ORDR
	LEFT OUTER JOIN CTE
		ON ORDR.DocEntry = CTE.DocEntry

I've cleared out the other tables leaving just the ORDR & RDR1 tables below... this is a snip of the result based on ORDR.DocNum = 51741.
I'm trying to populate the empty field with the total discount amount for the order (in this case 6,828.82 being the sum of the amounts in TotalLineDiscount Column 6)
I'm worried about adding tables to the database ... is it not possible to get this result in another way?

Thanks for your help, it's appreciated. I feel I am trying to run before I can walk here but under some pressure to get a result..

SELECT
[RDR1].[Quantity] AS [orderLineQty]
, [RDR1].[PriceBefDi] AS [orderPriceEA]
, [RDR1].[DiscPrcnt] AS [klineDiscPercent]
, ([RDR1].[PriceBefDi][RDR1].[DiscPrcnt]/100) AS [orderDiscountEA]
, ([RDR1].[PriceBefDi]-[RDR1][Price])
[RDR1].[Quantity] AS [TotalLineDiscount]
, '' AS [orderReleaseTotalDiscountOrderedCurrency]
, [ORDR].[VatSum] AS [orderReleaseTotalTaxOrderedCurrency]
, ([ORDR].[DocTotal]*[ORDR].[DocRate])-[ORDR].[VatSum] AS [orderReleaseAmountNetOrderedCurrency]
FROM ORDR
JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry

Hey Miro,

Can you provide the CREATE TABLE and INSERT TABLE scripts? (not a picture of it)

I'm worried about adding tables to the database ... is it not possible to get this result in another way?

The people on this forum don't have access to your database.
To help you, we run the CREATE TABLE and INSERT INTO scripts (that you will kindly provide) on our own test databases. So we have something to write and tweak the query on.
That's why we want the scripts (in text), not a picture on it. We can't run that picture on our databases.

You should definitely not run DELETE TABLE, CREATE TABLE and INSERT INTO scripts on your production database. You should however run the CREATE TABLE and INSERT INTO scripts with sample data prior to posting them on this forum. You will test those scripts on one of your own test databases.

3 Likes

MIro's concern is legitimate in that he is afraid dropping and creating tables using the real table names would be a disaster.
But that said, Miro, you will need to provide us sample tables

Two options maybe?

  1. http://sqlfiddle.com/
  2. using the following temporary tables.
DROP TABLE IF EXISTS #ORDR;	--Customer Orders
DROP TABLE IF EXISTS #RDR1;	--Lines on each order
GO
CREATE TABLE #ORDR(
	DocEntry	int	NOT NULL PRIMARY KEY,
	DocNum		int	NOT NULL,
	OrderDate	Date	NOT NULL
)

CREATE TABLE #RDR1(
	id		int	IDENTITY(1, 1)	NOT NULL PRIMARY KEY,
	DocEntry	int	NOT NULL,
	PriceBefDi	float	NOT NULL,
	Price		float	NOT NULL,	
	Quantity	int	NOT NULL
)
1 Like

Thank you for the help so far and for the patient explanations!.
I am working on the scripts , please excuse slow progress..

Hi again

How does this look?

I ran the below script ..

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ORDR'
ORDER BY ORDINAL_POSITION

..and it actually returned datatype int for ORDR.DocEntry and RDR1.DocEntry but ‘numeric’ for all the others and the others also are NULLABLE (if this is relevant). However I didn't change the script you wrote for me in case I mess it up.. I just added the DiscPrcnt field and some more data in the INSERT INTO sample data including the discount percent field

SyntaxEditor Code Snippet

DROP TABLE IF EXISTS dbo.ORDR; --Customer Orders DROP TABLE IF EXISTS dbo.RDR1; --Lines on each order GO CREATE TABLE dbo.ORDR( DocEntry int NOT NULL PRIMARY KEY, DocNum int NOT NULL, OrderDate Date NOT NULL ) CREATE TABLE dbo.RDR1( id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, DocEntry int NOT NULL, PriceBefDi float NOT NULL, Price float NOT NULL, Quantity int NOT NULL DiscPrcnt int NOT NULL ) GO INSERT INTO dbo.ORDR (DocEntry, DocNum, OrderDate) VALUES (1, 100, '20200201'), (2, 101, '20200202') (3, 102, '20200207') (4, 103, '20200210') (5, 104, '20200214') INSERT INTO dbo.RDR1(DocEntry, PriceBefDi, Price, Quantity, DiscPrcnt) VALUES (1, 100.0, 95.00, 12, 5), (1, 50.0, 50.0, 120, 0), (1, 1000.0, 900.0, 1, 10), (2, 90.0, 81.0, 12, 10), (2, 50.0, 50.0, 10, 0), (2, 1000.0, 900.0, 1, 10), (3, 25.0, 20.0, 12, 20), (4, 35.0, 31.5, 10, 10), (4, 44.0, 39.6, 3, 10), (5, 88.0, 83.6, 4, 5) GO

DROP TABLE IF EXISTS dbo.ORDR;	--Customer Orders

DROP TABLE IF EXISTS dbo.RDR1; --Lines on each order
GO
CREATE TABLE dbo.ORDR(
DocEntry int NOT NULL PRIMARY KEY,
DocNum int NOT NULL,
OrderDate Date NOT NULL
)

CREATE TABLE dbo.RDR1(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
DocEntry int NOT NULL,
PriceBefDi float NOT NULL,
Price float NOT NULL,
Quantity int NOT NULL
DiscPrcnt int NOT NULL
)
GO
INSERT INTO dbo.ORDR (DocEntry, DocNum, OrderDate) VALUES
(1, 100, '20200201'),
(2, 101, '20200202')
(3, 102, '20200207')
(4, 103, '20200210')
(5, 104, '20200214')

INSERT INTO dbo.RDR1(DocEntry, PriceBefDi, Price, Quantity, DiscPrcnt) VALUES
(1, 100.0, 95.00, 12, 5),
(1, 50.0, 50.0, 120, 0),
(1, 1000.0, 900.0, 1, 10),
(2, 90.0, 81.0, 12, 10),
(2, 50.0, 50.0, 10, 0),
(2, 1000.0, 900.0, 1, 10),
(3, 25.0, 20.0, 12, 20),
(4, 35.0, 31.5, 10, 10),
(4, 44.0, 39.6, 3, 10),
(5, 88.0, 83.6, 4, 5)
GO

now with commas added!
DROP TABLE IF EXISTS dbo.ORDR; --Customer Orders
DROP TABLE IF EXISTS dbo.RDR1; --Lines on each order
GO
CREATE TABLE dbo.ORDR(
DocEntry int NOT NULL PRIMARY KEY,
DocNum int NOT NULL,
OrderDate Date NOT NULL
)

CREATE TABLE dbo.RDR1(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
DocEntry int NOT NULL,
PriceBefDi float NOT NULL,
Price float NOT NULL,
Quantity int NOT NULL,
DiscPrcnt int NOT NULL
)
GO
INSERT INTO dbo.ORDR (DocEntry, DocNum, OrderDate) VALUES
(1, 100, '20200201'),
(2, 101, '20200202'),
(3, 102, '20200207'),
(4, 103, '20200210'),
(5, 104, '20200214')

INSERT INTO dbo.RDR1(DocEntry, PriceBefDi, Price, Quantity, DiscPrcnt) VALUES
(1, 100.0, 95.00, 12, 5),
(1, 50.0, 50.0, 120, 0),
(1, 1000.0, 900.0, 1, 10),
(2, 90.0, 81.0, 12, 10),
(2, 50.0, 50.0, 10, 0),
(2, 1000.0, 900.0, 1, 10),
(3, 25.0, 20.0, 12, 20),
(4, 35.0, 31.5, 10, 10),
(4, 44.0, 39.6, 3, 10),
(5, 88.0, 83.6, 4, 5)
GO

Thank you for the CREATE and INSERT scripts :slight_smile:

When I run my query, it gives this as a result:

DocEntry DocNum OrderDate OrderPrice OrderDiscount
1 100 2020-02-01 8040 160
2 101 2020-02-02 2372 208
3 102 2020-02-07 240 60
4 103 2020-02-10 433,8 48,2
5 104 2020-02-14 334,4 17,6

Is this what you expect?

1 Like

Hi

This worked perfectly and I was able to insert it into my full query and run that successfully in SAP B1.
Thank you so much for you patient help on this, it's really appreciated.

On thing though...
Before trying in SAP B1 I tried pasting my (final version) CRATE TABLE and INSERT INTO scripts into the left panel of SQL fiddle (and changed the drop down to SQL server) and then pasted your query into the right panel and tried to run it but I keep getting the error "Invalid object name 'ORDR' "

I tried a few things like deleting the preceding semi-colon (which I did in SAP B1) but I still get the error.
Can you tell me where I am going wrong? I would like to understand for future reference even though the query worked in SAP B1

It means that on the database the query was run, the table ORDR does not exist.