1

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.

I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total field minus Quantity field. This is a structure table.

enter image description here

And this's a result.

enter image description here

This is my code but it's error at ORDER BY.

select t2.BaseRef 'PO',t2.BaseQty 'qty', t1.NumAtCard 'INV',
        t2.Quantity 'Quantity',
        t2.BaseQty - SUM(t2.Quantity) OVER (ORDER BY t1.DocDate) AS 'total'
from OPDN t1 
        INNER JOIN PDN1 t2 ON t1.DocEntry = t2.DocEntry 

Can I use other way for sort this data?

  • 1
    Unclear. Create a fiddle, please... – Akina Mar 7 '19 at 5:52
  • Row 1 total = 500(Qty) - 100(Quantity) Row 2 total = 400(Total from Row1) - 200(Quantity) Row 3 total = 200(Total from Row1) - 200(Quantity) – midtonight Mar 7 '19 at 6:35
  • We need to look at your table's structures and sample data which must give the result you show. – Akina Mar 7 '19 at 6:36
  • I add table structures in questions. – midtonight Mar 7 '19 at 6:44
  • I do not see DocDate field mentioned in OVER clause... – Akina Mar 7 '19 at 7:02
0
SELECT PO.PO, 
       PO.Qty, 
       INV.INV, 
       INV.Quantity, 
       PO.Qty - SUM(INV.Quantity) OVER (PARTITION BY PO.PO ORDER BY INV.DocDate) total
FROM PO, INV
WHERE PO.DocEntry = INV.DocEntry
ORDER BY PO.PO, INV.DocDate

fiddle

  • In SQL server 2008 can't use ORDER BY in OVER clause. Console display this ("Incorrect syntax near 'order'.") – midtonight Mar 7 '19 at 9:18
0

If SUM() OVER() is not supported, you may try with the following approach. Rows are ordered by INV number as text (INV100, INV105, ...), not as number (100, 105, ...).

Input:

CREATE TABLE #PO (
    PO varchar(10),
    DocEntry int,
    Qty int
)
CREATE TABLE #INV (
    INV varchar(10),
    DocEntry int,
    Qty int
)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('A123', 1, 500)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('B123', 2, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV100', 1, 100)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV102', 2, 10)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV105', 1, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV106', 2, 190)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV112', 1, 200)

Statement:

SELECT 
    p.PO, 
    p.Qty,
    i.INV,
    i.Qty AS Quantity,
    Total = p.Qty - 
            (
             SELECT SUM(rt.Qty) 
             FROM #INV rt 
             WHERE (rt.DocEntry = i.DocEntry) AND (rt.INV <= i.INV)
            )
FROM #PO p
LEFT JOIN #INV i ON (p.DocEntry = i.DocEntry)
ORDER BY p.DocEntry, i.INV

Output:

PO      Qty INV     Quantity    Total
A123    500 IV100   100         400
A123    500 IV105   200         200
A123    500 IV112   200         0
B123    200 IV102   10          190
B123    200 IV106   190         0
  • This approach will break down when Invoice "IV1000" is created, being [incorrectly] sorted between "IV100" and "IV101". Whilst using a purely numeric, surrogate key to uniquely identify each Invoice would improve the situation, things like Sequences are NOT guaranteed to be sequential (or even "always increasing") but only unique. You need to find a definitive way of getting these items into the right order; the database will not intrinsically do this for you. – Phill W. Jan 16 at 11:12
  • @PhillW. Of course, sort order is based on text values (as is mentioned in the answer). If the invoice numbers are greater than 999, ORDER BY p.DocEntry, CONVERT(int, REPLACE(i.INV, 'IV', '')) is one possible approach. – Zhorov Jan 16 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.