SQLTeam.com | Weblogs | Forums

Help needed for a multiple update command



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
      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).


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
    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
    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;


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!



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.