Help with a SQL Query

Question

Help with a SQL Query

Navigation

#1 by (0 votes)

1

In a SQL database, I have a part reference table with quantities, stock locations and stock entry date. I need to ask about to know what stock locations to get a certain number of pieces. Prioritize about locations that have the oldest entry date and have the number of pieces that I want to take out. The problem comes when in one location I do not have enough to catch and I would have to take another. I can not think of how to raise it, so that the result of the query are the columns to pick up.

Right now the code I have is:

SELECT 
  [Ctd],[Ubicacion] 
  FROM [dbo].[Ubicaciones]
 WHERE HoraFecha = (select MIN(HoraFecha) from [dbo].[Ubicaciones]) AND Ctd >= "CantidadExtraer"

The table I have would be of the following type:

CODIGO     Ctd       Ubicacion     FechaHora
5555        2           PK01        17/11/17
5555        2           PK02        18/11/17
5555        3           PK03        19/11/17
5555        3           PK04        20/11/17
5555        4           PK05        21/11/17

With the query that I have made in case of wanting to extract QuantityExtract = 4, the result would be 4 PK05. In the following order if I want to extract 4 again, the result should be: 2 PK01, 2 PK02.

Thanks for the help.

sql select

asked by panki 15.11.2017 в 16:14

source

1 answer

What to do so that the result of a calculation with a loop is saved in my df? See left join with 6 tables

score 0 · Answer 1

If I did not get it wrong, you have two "logical" ways to recover the locations:

If the amount to extract is less than that of any location, we obtain the oldest location

If we do not cover the required amount in any location, we must recover the oldest locations until the amount is completed.

The following code is a proof of concept of a solution to your problem:

CREATE TABLE #Ubicaciones (
    CODIGO      INT,
    Ctd         INT,
    Ubicacion   CHAR(4),
    FechaHora   DATETIME
)

INSERT INTO #Ubicaciones(CODIGO, Ctd, Ubicacion, FechaHora)
    VALUES  (5555, 2, 'PK01',  '2017/11/17'),
        (5555, 2, 'PK02',  '2017/11/18'),
        (5555, 3, 'PK03',  '2017/11/19'),
        (5555, 3, 'PK04',  '2017/11/20'),
        (5555, 4, 'PK05',  '2017/11/21')

CREATE TABLE #UbicacionesSolicitud (
    CODIGO      INT,
    Ubicacion   CHAR(4),
    Ctd         INT,
    FechaHora   DATETIME,
    Acumulado   INT
)

DECLARE @Cantidad   INT

-- CANTIDAD A BUSCAR ---------
SELECT @Cantidad = 5
------------------------------

-- 1. Hay alguna ubicación que cubre totalmente el pedido?
INSERT INTO #UbicacionesSolicitud (CODIGO, Ubicacion, Ctd, FechaHora, Acumulado)
SELECT  TOP 1 CODIGO, Ubicacion, Ctd, FechaHora, Ctd
    FROM #Ubicaciones
    WHERE Ctd >= @Cantidad
    ORDER BY FechaHora

-- 2. Si no hay casos 1, entonces listamos las ubicaciones necesarias para cubrir la cantidad
IF NOT EXISTS(SELECT 1 FROM #UbicacionesSolicitud)
BEGIN

    INSERT INTO #UbicacionesSolicitud (CODIGO, Ubicacion, Ctd, FechaHora, Acumulado)
    SELECT  U1.CODIGO,
        U1.Ubicacion, 
        U1.Ctd, 
        U1.FechaHora,
        SUM(ISNULL(U2.Ctd,0))
        FROM #Ubicaciones U1
        LEFT JOIN #Ubicaciones U2
            ON U2.CODIGO = U1.CODIGO
            AND U2.FechaHora <= U1.FechaHora
        GROUP BY U1.CODIGO, 
            U1.Ubicacion,
            U1.Ctd, 
            U1.FechaHora
        ORDER BY U1.FechaHora

    DELETE  #UbicacionesSolicitud
        FROM #UbicacionesSolicitud U
        LEFT JOIN (SELECT   TOP 1 CODIGO, Ubicacion, Acumulado
                FROM #UbicacionesSolicitud
                WHERE Acumulado >= @Cantidad
                ORDER BY Acumulado
            ) H
            ON H.CODIGO = U.CODIGO
        WHERE U.Acumulado > H.Acumulado
END

-- Salida final
SELECT  *
    FROM #UbicacionesSolicitud


DROP TABLE #UbicacionesSolicitud
DROP TABLE #Ubicaciones

Explanation

First we create a table #Ubicaciones with the data of your example and another table #UbicacionesSolicitud that will contain the list of locations to which the order would have to be made based on the quantity.
The first query is to check if there is a location with enough stock, in which case we bring only the oldest one
The second part is a bit more complex, but basically what you do is a Quantity Accumulator from the oldest to the newest location
Then we obtain the first location whose accumulator is equal to or greater than @Cantidad and delete from this location the ones that follow.
At the end we simply list #UbicacionesSolicitud where we will have the locations to request.

Important note: This query is defined to work on one product at a time, as you indicated in your example.