CODEWITHSUNDEEP
sqlsql-serversql-update
YanetP1988
YanetP1988

Reputation: 1356

Generalize Update query in Sql for date columns

I have a SQL Server table named Pedidos with this structure:

enter image description here

Every month (at its last days) I have to run this queries:

UPDATE PEDIDOS
SET PedFchAct = CONVERT(DATETIME, '2017-08-28 00:00:00', 102), 
    PrgFchAct = CONVERT(DATETIME, '2017-08-28 00:00:00', 102)
WHERE     
    (PedFecCap = CONVERT(DATETIME, '2017-09-01 00:00:00', 102))

UPDATE PEDIDOS
SET PedFchAct = CONVERT(DATETIME, '2017-08-29 00:00:00', 102), 
    PrgFchAct = CONVERT(DATETIME, '2017-08-29 00:00:00', 102)
WHERE (PedFecCap = CONVERT(DATETIME, '2017-09-02 00:00:00', 102))

UPDATE PEDIDOS
SET PedFchAct = CONVERT(DATETIME, '2017-08-30 00:00:00', 102), 
    PrgFchAct = CONVERT(DATETIME, '2017-08-30 00:00:00', 102)
WHERE 
    (PedFecCap > CONVERT(DATETIME, '2017-09-02 00:00:00', 102)) 
    AND (PedFecCap <= CONVERT(DATETIME, '2017-09-30 00:00:00', 102))

The idea is the following, I have to segment the registers by PedFecCap in three.

Filter dates would be:

Then I have to update PedFchAct and PrgFchAct, and set three different dates of current month for each filtered segment. For example:

For PedFecCap=1/nextmonth/2017, PedFchAct and PrgFchAct= 28/currentmonth/2017

For PedFecCap=2/nextmonth/2017, PedFchAct and PrgFchAct= 29/currentmonth/2017

For lastdayOfnextMonth/nextmonth/2017<=PedFecCap>2/nextmonth/2017, PedFchAct and PrgFchAct= 30/currentmonth/2017

I need to generalize this query and eliminate fixed values so every month I only run the script and not rewrite the query.

Upvotes: 1

Views: 168

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Sample data

pedfeccap               pedfchaact  prgfchact
2017-07-01 00:00:00.000 NULL        NULL
2017-07-02 00:00:00.000 NULL        NULL
2017-07-03 00:00:00.000 NULL        NULL
2017-07-28 00:00:00.000 NULL        NULL
2017-07-29 00:00:00.000 NULL        NULL
2017-07-30 00:00:00.000 NULL        NULL
2017-07-31 00:00:00.000 NULL        NULL
2017-08-01 00:00:00.000 NULL        NULL
2017-08-02 00:00:00.000 NULL        NULL
2017-08-03 00:00:00.000 NULL        NULL
2017-08-27 00:00:00.000 NULL        NULL
2017-08-28 00:00:00.000 NULL        NULL
2017-08-29 00:00:00.000 NULL        NULL
2017-08-30 00:00:00.000 NULL        NULL
2017-08-31 00:00:00.000 NULL        NULL
2017-09-01 00:00:00.000 NULL        NULL
2017-09-02 00:00:00.000 NULL        NULL
2017-09-03 00:00:00.000 NULL        NULL
2017-09-27 00:00:00.000 NULL        NULL
2017-09-28 00:00:00.000 NULL        NULL
2017-09-29 00:00:00.000 NULL        NULL
2017-09-30 00:00:00.000 NULL        NULL

You can use the first day and last day computation for each date

UPDATE pedidos
   SET pedfchaact = CASE WHEN pedfeccap = DATEADD(MONTH, DATEDIFF(MONTH, 0, pedfeccap), 0)
                         THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 27
                         WHEN pedfeccap = DATEADD(MONTH, DATEDIFF(MONTH, 0, pedfeccap), 0) + 1
                         THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 28
                         ELSE DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 29
                     END,
       prgfchact = CASE WHEN pedfeccap = DATEADD(MONTH, DATEDIFF(MONTH, 0, pedfeccap), 0)
                        THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 27
                        WHEN pedfeccap = DATEADD(MONTH, DATEDIFF(MONTH, 0, pedfeccap), 0) + 1
                        THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 28
                        ELSE DATEADD(MONTH, DATEDIFF(MONTH, -1, pedfeccap) -2, 0) + 29
                    END;

Result

pedfeccap               pedfchaact              prgfchact
2017-07-01 00:00:00.000 2017-06-28 00:00:00.000 2017-06-28 00:00:00.000
2017-07-02 00:00:00.000 2017-06-29 00:00:00.000 2017-06-29 00:00:00.000
2017-07-03 00:00:00.000 2017-06-30 00:00:00.000 2017-06-30 00:00:00.000
2017-07-28 00:00:00.000 2017-06-30 00:00:00.000 2017-06-30 00:00:00.000
2017-07-29 00:00:00.000 2017-06-30 00:00:00.000 2017-06-30 00:00:00.000
2017-07-30 00:00:00.000 2017-06-30 00:00:00.000 2017-06-30 00:00:00.000
2017-07-31 00:00:00.000 2017-06-30 00:00:00.000 2017-06-30 00:00:00.000
2017-08-01 00:00:00.000 2017-07-28 00:00:00.000 2017-07-28 00:00:00.000
2017-08-02 00:00:00.000 2017-07-29 00:00:00.000 2017-07-29 00:00:00.000
2017-08-03 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-08-27 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-08-28 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-08-29 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-08-30 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-08-31 00:00:00.000 2017-07-30 00:00:00.000 2017-07-30 00:00:00.000
2017-09-01 00:00:00.000 2017-08-28 00:00:00.000 2017-08-28 00:00:00.000
2017-09-02 00:00:00.000 2017-08-29 00:00:00.000 2017-08-29 00:00:00.000
2017-09-03 00:00:00.000 2017-08-30 00:00:00.000 2017-08-30 00:00:00.000
2017-09-27 00:00:00.000 2017-08-30 00:00:00.000 2017-08-30 00:00:00.000
2017-09-28 00:00:00.000 2017-08-30 00:00:00.000 2017-08-30 00:00:00.000
2017-09-29 00:00:00.000 2017-08-30 00:00:00.000 2017-08-30 00:00:00.000
2017-09-30 00:00:00.000 2017-08-30 00:00:00.000 2017-08-30 00:00:00.000

Upvotes: 1

Tom H
Tom H

Reputation: 47444

You should be able to do this with a CASE statement:

UPDATE PEDIDOS
SET
    PedFchAct = CASE
                    WHEN PedFecCap = '2017-09-01' THEN '2017-08-28'
                    WHEN PedFecCap = '2017-09-02' THEN '2017-08-29'
                    ELSE '2017-08-30'
                END,
    PrgFchAct = CASE
                    WHEN PedFecCap = '2017-09-01' THEN '2017-08-28'
                    WHEN PedFecCap = '2017-09-02' THEN '2017-08-29'
                    ELSE '2017-08-30'
                END
WHERE
    PedFecCap BETWEEN '2017-09-01' AND '2017-09-30'

Upvotes: 0

Related Questions