Discount amounts in SQL without using a cursor (SQL)

Question

Discount amounts in SQL without using a cursor (SQL)

Navigation

#1 by (1 votes)

2

I need to discount or download quantities, I have a query that yields the following result:

This result I have to download the balance based on the amount, the column to calculate is descontar ; that is, for an article I have an amount of 65 and three balances 50, 30 and 10 therefore the column to discount should be at 50, 15 and 0, example:

The result of query I put it in a table and I copy it below:

CREATE TABLE [dbo].[prueba_descarga](
[articulo] [VARCHAR](10) NULL,
[cantidad] [DECIMAL](16, 6) NULL,
[lote] [VARCHAR](10) NULL,
[saldo] [DECIMAL](16, 6) NULL,
[fecha] [DATETIME] NULL,
[descontar] [DECIMAL](16, 6) NULL) 

INSERT [dbo].[prueba_descarga] ([articulo], [cantidad], [lote], [saldo], [fecha], [descontar]) VALUES (N'A0001', CAST(65.000000 AS Decimal(16, 6)), N'452', CAST(50.0000000 AS Decimal(22, 7)), CAST(N'2018-06-07T00:00:00.000' AS DateTime), CAST(0.000000 AS Decimal(16, 6)))
INSERT [dbo].[prueba_descarga] ([articulo], [cantidad], [lote], [saldo], [fecha], [descontar]) VALUES (N'A0001', CAST(65.000000 AS Decimal(16, 6)), N'455', CAST(30.0000000 AS Decimal(22, 7)), CAST(N'2018-06-05T00:00:00.000' AS DateTime), CAST(0.000000 AS Decimal(16, 6)))
INSERT [dbo].[prueba_descarga] ([articulo], [cantidad], [lote], [saldo], [fecha], [descontar]) VALUES (N'A0001', CAST(65.000000 AS Decimal(16, 6)), N'456', CAST(10.0000000 AS Decimal(22, 7)), CAST(N'2018-06-11T00:00:00.000' AS DateTime), CAST(0.000000 AS Decimal(16, 6)))
INSERT [dbo].[prueba_descarga] ([articulo], [cantidad], [lote], [saldo], [fecha], [descontar]) VALUES (N'A0002', CAST(20.000000 AS Decimal(16, 6)), N'457', CAST(15.0000000 AS Decimal(22, 7)), CAST(N'2018-06-11T00:00:00.000' AS DateTime), CAST(0.000000 AS Decimal(16, 6)))
INSERT [dbo].[prueba_descarga] ([articulo], [cantidad], [lote], [saldo], [fecha], [descontar]) VALUES (N'A0002', CAST(20.000000 AS Decimal(16, 6)), N'458', CAST(25.0000000 AS Decimal(22, 7)), CAST(N'2018-06-11T00:00:00.000' AS DateTime), CAST(0.000000 AS Decimal(16, 6)))

Thank you very much !!!

sql sql-server sql-server-2014

asked by Matías 11.06.2018 в 16:44

source

1 answer

React, Google map: "Unexpected token" and "google is not defined" when deployed save image in django python

score 1 · Accepted Answer

The problem is not that simple, but it is made easier by the fact that you are using SQL Server 2014.

In this case I'm sure there is some simpler way to do it, but the following code gives me the correct results

WITH CTE1 AS
(
    SELECT  *, 
            aux1 = cantidad - SUM(saldo) OVER(PARTITION BY articulo ORDER BY fecha, lote)
    FROM dbo.prueba_descarga A
), CTE2 AS
(
    SELECT  *, 
            aux2 = ROW_NUMBER() OVER(PARTITION BY articulo ORDER BY CASE WHEN aux1 < 0 THEN 0 ELSE 1 END, aux1 DESC) 
    FROM CTE1 A
)
SELECT  articulo,
        cantidad,
        lote,
        saldo,
        fecha,
        descontar = CASE 
                        WHEN aux1 >= 0 THEN saldo
                        WHEN aux1 < 0 AND aux2 = 1 THEN saldo + aux1
                        ELSE 0 
                    END 
FROM CTE2
ORDER BY articulo, fecha, lote
;

Here is a demo with this code.

And the results are:

╔══════════╦═══════════╦══════╦═══════════╦═════════════════════════╦═══════════╗
║ articulo ║ cantidad  ║ lote ║   saldo   ║          fecha          ║ descontar ║
╠══════════╬═══════════╬══════╬═══════════╬═════════════════════════╬═══════════╣
║ A0001    ║ 65.000000 ║  455 ║ 30.000000 ║ 2018-06-05 00:00:00.000 ║ 30.000000 ║
║ A0001    ║ 65.000000 ║  452 ║ 50.000000 ║ 2018-06-07 00:00:00.000 ║ 35.000000 ║
║ A0001    ║ 65.000000 ║  456 ║ 10.000000 ║ 2018-06-11 00:00:00.000 ║  0.000000 ║
║ A0002    ║ 20.000000 ║  457 ║ 15.000000 ║ 2018-06-11 00:00:00.000 ║ 15.000000 ║
║ A0002    ║ 20.000000 ║  458 ║ 25.000000 ║ 2018-06-11 00:00:00.000 ║  5.000000 ║
╚══════════╩═══════════╩══════╩═══════════╩═════════════════════════╩═══════════╝