hi to all
i have a gridview where i show a schedule
first column is the iduser the following are the days, starting at day 1 of a month
what i want is to set the holidays days in the schedule
here is the sql code that i have
DECLARE @Date_Start AS DATETIME
DECLARE @Schedule TABLE(Idx NUMERIC IDENTITY(1,1) PRIMARY KEY, IdUser NVARCHAR(20), D01 CHAR(14), D02 CHAR(14), D03 CHAR(14), D04 CHAR(14), D05 CHAR(14), UNIQUE(IdUser))
DECLARE @Holidays TABLE(Idx NUMERIC IDENTITY(1,1) PRIMARY KEY NOT NULL, Holiday DATETIME NOT NULL, UNIQUE(Holiday))
SET @Date_Start = CAST('2014-01-01 00:00:00.000' AS DATETIME)
INSERT INTO @Schedule VALUES('0708', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Schedule VALUES('0899', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Schedule VALUES('0933', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Schedule VALUES('1097', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Schedule VALUES('1126', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Schedule VALUES('1513', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5')
INSERT INTO @Holidays VALUES(CAST('2014-01-02 00:00:00.000' AS DATETIME))
INSERT INTO @Holidays VALUES(CAST('2014-01-04 00:00:00.000' AS DATETIME))
UPDATE X
SET [D01] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '0' THEN 'Holiday' ELSE [D01] END,
[D02] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '1' THEN 'Holiday' ELSE [D02] END,
[D03] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '2' THEN 'Holiday' ELSE [D03] END,
[D04] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '3' THEN 'Holiday' ELSE [D04] END,
[D05] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '4' THEN 'Holiday' ELSE [D05] END
FROM @Schedule X INNER JOIN @Holidays Y ON Y.Idx IN ('1', '2')
SELECT * FROM @Schedule
SELECT * FROM @Holidays
the commented updates do what i want
the non commented is the result of my web search, but i don't understand this format of update
the "ON" part is my last experience trying to understand how it works... but i don't get it
some help on this would be nice or a diffrent way
thanks for the time and replys