Click here to Skip to main content
15,885,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
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 X.[D01] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '0' 
--UPDATE X SET X.[D02] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '1' 
--UPDATE X SET X.[D03] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '2' 
--UPDATE X SET X.[D04] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '3' 
--UPDATE X SET X.[D05] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '4' 


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
Posted

1 solution

it looks that i am stuck
the content is short?!...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900