Help needed for a multiple update command

Transact-SQL
1

I'm not succeeding to get this update command right:

IDSemana    Lunes    Martes    Miercoles    Jueves    Viernes
---------------------------------------------------------------
XXXXXXX     null     null      null         null      null

UPDATE Semanas set Lunes = CASE WHEN datepart(weekday,q.fecha) = 1 THEN @DiaLibre ELSE Lunes END,
                  Martes = CASE WHEN datepart(weekday,q.fecha) = 2 THEN @DiaLibre ELSE Martes END,
               Miercoles = CASE WHEN datepart(weekday,q.fecha) = 3 THEN @DiaLibre ELSE Miercoles END,
                  Jueves = CASE WHEN datepart(weekday,q.fecha) = 4 THEN @DiaLibre ELSE Jueves END,
                 Viernes = CASE WHEN datepart(weekday,q.fecha) = 5 THEN @DiaLibre ELSE Viernes END
FROM (
      Select TableA.fecha, SM.IDSemana
      From Semanas SM
           inner join #TableA as TableA on SM.Ejercicio = year(TableA.fecha) and 
                                        SM.NumeroSemana = DATEPART(week,TableA.fecha) and 
                                        SM.IDCalendario = TableA.IDCalendario
      WHERE TableA.Cnt = 1 and TableA.Type = 1 and TableA.Accepted = 1 and TableA.value = 1) q
WHERE q.IDSemana = Semanas.IDSemana

To make it more easy to understand here is what the bracket (Table q) looks like:

fecha      IDSemana
--------------------
20.2.2017  XXXXXXX
21.2.2017  XXXXXXX
22.2.2017  XXXXXXX

so what I would expect is that for IDSemana XXXXXX

IDSemana    Lunes    Martes    Miercoles    Jueves    Viernes
---------------------------------------------------------------
XXXXXXX     DiaLibre DiaLibre  DiaLibre     null      null

but I get:

IDSemana    Lunes    Martes    Miercoles    Jueves    Viernes
---------------------------------------------------------------
XXXXXXX     DiaLibre null      null         null      null

it is not related to the SQL culture. I place a SET LANGUAGE Spanish on top, so Lunes (Monday) is 1 a.s.o. The 20.2.2017 is Monday (1), 21.2.2017 is Tuesday (2) and 22.2.2017 Wednesday (3).

2

I suspect you have a one to many relationship between Semanas and #TableA so you will need to Group the results.
Try something like the following. If it does not work post some DDL and test data.

WITH TableAGrouped
AS
(
    SELECT X.Ejercicio, X.NumeroSemana, A.IDCalendario
        ,MAX(CASE WHEN DATEPART(weekday, A.fecha) = 1 THEN 1 ELSE 0 END) AS IsLunes
        ,MAX(CASE WHEN DATEPART(weekday, A.fecha) = 2 THEN 1 ELSE 0 END) AS IsMartes
        ,MAX(CASE WHEN DATEPART(weekday, A.fecha) = 3 THEN 1 ELSE 0 END) AS IsMiercoles
        ,MAX(CASE WHEN DATEPART(weekday, A.fecha) = 4 THEN 1 ELSE 0 END) AS IsJueves
        ,MAX(CASE WHEN DATEPART(weekday, A.fecha) = 5 THEN 1 ELSE 0 END) AS IsViernes
    FROM #TableA A
        CROSS APPLY ( VALUES(YEAR(A.fecha), DATEPART(week, A.fecha)) ) X (Ejercicio, NumeroSemana)
    WHERE A.Cnt = 1 AND A.[Type] = 1 AND A.Accepted = 1 AND A.value = 1
    GROUP BY X.Ejercicio, X.NumeroSemana, A.IDCalendario
)
UPDATE S
    SET Lunes = CASE WHEN G.IsLunes = 1 THEN @DiaLibre ELSE Lunes END
        ,Martes = CASE WHEN G.IsMartes = 1 THEN @DiaLibre ELSE Martes END
        ,Miercoles = CASE WHEN G.IsMiercoles = 1 THEN @DiaLibre ELSE Miercoles END
        ,Jueves = CASE WHEN G.IsJueves = 1 THEN @DiaLibre ELSE Jueves END
        ,Viernes = CASE WHEN G.IsViernes = 1 THEN @DiaLibre ELSE Viernes END
FROM Semanas S
    JOIN TableAGrouped G
        ON S.Ejercicio = G.Ejercicio
            AND S.NumeroSemana = G.NumeroSemana
            AND S.IDCalendario = G.IDCalendario;
1 Like
3

Sorry for replying late, but I only came back on this today after a long weekend.

I was really scared to run that code on my tables, because -to be honest- I didn't understand the logic of what you are doing there. But I applied them now on a copy of that Table and it really works!!! Thank you so much! I'm still puzzling and feel uncomfortable to use some code I don't understand; I was actually going to do this iteratively as an alternative but I guess I have to move on to catch up the time I've been losing here. So I'll better use it! Thank you!

Martin

4

I am glad the code worked.

You can probalby make if more efficient by using PIVOT in the CTE. I was not going to even attempt that without DDL and consumable test data.