Duplicate automated log SQL server

Question

Duplicate automated log SQL server

Navigation

#1 by (1 votes)

1

I have the following problem:

In the BDD I have records of thousands of products with the invoice date and the amount that was sold. What I need is to add records for all the product codes that I have registered, taking all the dates from the first registered to the last (regardless of the product code) and add all the months that are not registered for each product and add one zero amount for that month.

For example, you can visualize the problem with the following table:

What I need to achieve

I can not think of any sentence in sql to solve this problem automatically, without hard data, I appreciate any help, thanks in advance.

PS: The version I'm using is sql server 2008

sql insert sql-server-2008-r2

asked by A V 27.08.2018 в 21:19

source

1 answer

Upload data and images of a form with Node.js - form / data Block past weeks in input week

score 1 · Accepted Answer

This is not as simple as it seems, but basically what you have to do is either have a calendar table with data for each month, or create that table at the time. I recommend that you create a table that already contains the months you need.

In any case, one solution is the following:

-- Creación de datos de ejemplo
CREATE TABLE #TuTabla(Cod varchar(3), Cantidad int, Fecha date);

INSERT INTO #TuTabla 
VALUES  ('AAA',2,'20180125'),
        ('AAA',3,'20180426'),
        ('BBB',4,'20180527')
;

-- Código para obtener el resultado deseado
WITH Meses AS
(
    SELECT DATEADD(MONTH,number,B.MinMes) Mes
    FROM master.dbo.spt_values A
    CROSS JOIN (SELECT MIN(CONVERT(varchar(6),Fecha,112) + '01') MinMes, MAX(CONVERT(varchar(6),Fecha,112) + '01') MaxMes
                FROM #TuTabla) B
    WHERE A.type = 'P'
    AND DATEADD(MONTH,number,B.MinMes) <= B.MaxMes
)
SELECT  C.Cod,
        ISNULL(T.Cantidad,0) Cantidad,
        ISNULL(T.Fecha,M.Mes) Fecha
FROM Meses M
CROSS JOIN (SELECT DISTINCT Cod
            FROM #TuTabla) C
LEFT JOIN #TuTabla T
    ON C.Cod = T.Cod
    AND CONVERT(varchar(6),M.Mes,112) = CONVERT(varchar(6),T.Fecha,112)
ORDER BY C.Cod
;

Here is a demo online for you to review the code.

And the result is:

╔═════╦══════════╦════════════╗
║ Cod ║ Cantidad ║   Fecha    ║
╠═════╬══════════╬════════════╣
║ AAA ║        2 ║ 2018-01-25 ║
║ AAA ║        0 ║ 2018-02-01 ║
║ AAA ║        0 ║ 2018-03-01 ║
║ AAA ║        3 ║ 2018-04-26 ║
║ AAA ║        0 ║ 2018-05-01 ║
║ BBB ║        4 ║ 2018-05-27 ║
║ BBB ║        0 ║ 2018-04-01 ║
║ BBB ║        0 ║ 2018-03-01 ║
║ BBB ║        0 ║ 2018-02-01 ║
║ BBB ║        0 ║ 2018-01-01 ║
╚═════╩══════════╩════════════╝