CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TheProvost
TheProvost

Reputation: 1893

Select invoices based on quantity needed

I have a table that looks like this:

+---------------+---------------+------------------+--------------+
| InvoiceNumber | ProductNumber | ReceivedQuantity | ReceivedDate |
+---------------+---------------+------------------+--------------+
| INV001        | P001          |              500 |   09/01/2015 |
| INV002        | P001          |              600 |   09/02/2015 |
| INV003        | P001          |              700 |   09/03/2015 |
+---------------+---------------+------------------+--------------+

When a product is ordered. System needs to know which invoice it gets it from. First in first out.

For example I need 1000 quantity of product number P001. It should select the following invoices. It does not display the last invoice since 500 + 600 is already sufficient quantity

+---------------+---------------+------------------+--------------+
| InvoiceNumber | ProductNumber | ReceivedQuantity | ReceivedDate |
+---------------+---------------+------------------+--------------+
| INV001        | P001          |              500 |   09/01/2015 |
| INV002        | P001          |              600 |   09/02/2015 |
+---------------+---------------+------------------+--------------+

I can replicate this by making a cursor and looping through the table but looking for the best way to achieve this. Any nudge to the right direction would help a lot.

Upvotes: 1

Views: 89

Answers (2)

shA.t
shA.t

Reputation: 16958

I think you can use a query like this:

;WITH t As (
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY ReceivedDate, InvoiceNumber) As RowNo
    FROM yourTable
), firstOverflow AS (
    SELECT TOP(1)
        t1.RowNo
    FROM t t1
        LEFT JOIN
        t t2 ON t1.ProductNumber = t2.ProductNumber AND t1.ReceivedDate >= t2.ReceivedDate
    GROUP BY t1.RowNo, t1.InvoiceNumber, t1.ProductNumber, t1.ReceivedQuantity, t1.ReceivedDate
    HAVING SUM(t2.ReceivedQuantity) >= 1000
    ORDER BY SUM(t2.ReceivedQuantity) - 1000)
SELECT *
FROM t 
    JOIN 
    firstOverflow ON t.RowNo <= firstOverflow.RowNo;

A better solution is this:

DECLARE @value int = 1000;

WITH t As (
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY ReceivedDate, InvoiceNumber) As seq
    FROM yourTable
), s As (
    SELECT t.InvoiceNumber, t.ProductNumber, t.ReceivedQuantity, t.ReceivedDate, SUM(tt.ReceivedQuantity) As currentTotal
    FROM t
        LEFT JOIN
        t tt ON t.ProductNumber = tt.ProductNumber AND  t.seq >= tt.seq
    GROUP BY t.InvoiceNumber, t.ProductNumber, t.ReceivedQuantity, t.ReceivedDate
), st As (
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY (CASE WHEN s.currentTotal > @value THEN -currentTotal ELSE Null END) DESC) As seq
    FROM s)
SELECT st.InvoiceNumber, st.ProductNumber, st.ReceivedQuantity, st.ReceivedDate
FROM st
WHERE currentTotal < @value
UNION ALL
SELECT st.InvoiceNumber, st.ProductNumber, st.ReceivedQuantity, st.ReceivedDate
FROM st
WHERE currentTotal >= @value AND st.seq = 1;

Upvotes: 2

Hemus San
Hemus San

Reputation: 399

Try this query and give some feedback:

DECLARE @table TABLE (InvoiceNumber nvarchar(100),
                      ProductNumber nvarchar(100),
                      ReceivedQuantity int)
INSERT INTO @table VALUES ('inv001', 'p001', 500)
INSERT INTO @table VALUES ('inv002', 'p001', 600)
INSERT INTO @table VALUES ('inv003', 'p001', 600)
INSERT INTO @table VALUES ('inv004', 'p001', 600)

SQL 2012:

SELECT v.* FROM
(
    SELECT t.*,
      SUM(ReceivedQuantity) OVER (PARTITION BY ProductNumber ORDER BY InvoiceNumber) AS sum
    FROM @table t
) v
WHERE sum <= 1000

SQL 2008:

SELECT v.* FROM
(
SELECT
    a.InvoiceNumber
    , a.ProductNumber
    , SUM(b.ReceivedQuantity) AS sum
FROM
    @table a
    INNER JOIN @table b
    ON  a.InvoiceNumber >= b.InvoiceNumber AND a.ProductNumber = b.ProductNumber
GROUP BY 
    a.InvoiceNumber
    , a.ProductNumber
) v
WHERE sum <= 1000

Upvotes: 0

Related Questions