September 5, 2019 at 8:52 pm
hello
i have 2 table
first is holiday date
second is calendar
me need to update calendar table working day to 0 from holiday data
but need to update only working day
for example this is 2 tables
DECLARE @HOLIDAY TABLE (ID INT NOT NULL IDENTITY(1,1),DATEHOLIDAY DATE)
DECLARE @TIME TABLE (ID INT NOT NULL IDENTITY(1,1),DATEMONTH DATE, IS_WORK INT)
INSERT INTO @HOLIDAY(DATEHOLIDAY)
VALUES('2012-01-01'),('2012-02-01')
INSERT INTO @TIME(DATEMONTH,IS_WORK) VALUES('2012-01-01',0),('2012-01-02',0),('2012-01-03',1),
('2012-02-01',0),('2012-02-02',0),('2012-02-03',1)
SELECT * FROM @HOLIDAY
SELECT * FROM @TIME
ID DATEHOLIDAY
----------- -----------
1 2012-01-01
2 2012-02-01
ID DATEMONTH IS_WORK
----------- ---------- -----------
1 2012-01-01 0
2 2012-01-02 0
3 2012-01-03 1
4 2012-02-01 0
5 2012-02-02 0
6 2012-02-03 1
and hier is_work =0 is not working day week (for example saturday sunday or another holiday day)
need give update with that
in holiday table 2012-01-01 is holiday
if we will see @time table in 2012-01-01 hier IS_WORK=0 and also 2012-01-02 IS_WORK =0
then need to update 2012-01-03 IS_WORK from 1 to 0
September 5, 2019 at 9:46 pm
Something like this?
use tempdb;
GO
CREATE TABLE HOLIDAY (DATEHOLIDAY DATE);
CREATE TABLE Times (DATEMONTH DATE, IS_WORK BIT);
GO
INSERT INTO HOLIDAY(DATEHOLIDAY) VALUES('2012-01-01'),('2012-02-01');
INSERT INTO Times(DATEMONTH,IS_WORK) VALUES('2012-01-01',0),('2012-01-02',0),('2012-01-03',1),
('2012-02-01',0),('2012-02-02',0),('2012-02-03',1);
Then the update is pretty simple, because the join removes all Times records that are not in the Holiday table:
UPDATE Times
SET Is_Work = 0
FROM Times t INNER JOIN holiday h
ON t.DateMonth = h.dateholiday;
September 5, 2019 at 9:57 pm
THANKS FOR REPLY
BUT IN @TIME TABLE ALSO '2012-01-01'),('2012-02-01') IS_WORK COLUMN=0
MEE NEED TO UPDATE WITHOUT IS_WORK COLUMN=0 ONLY IS_WORK COLUMN=1
FOR EXAMPLE NEED RESULT THAT AFTER UPDATE
DATEMONTH IS_WORK
---------- -------
2012-01-01 0
2012-01-02 0
2012-01-03 0
2012-02-01 0
2012-02-02 0
2012-02-03 0
September 6, 2019 at 4:11 am
Could you explain the context of what you're doing? If you have a table of days and some are marked as holidays, you don't need to store that information in the Time table at all, because you can retrieve it using a join.
Is this what you wanted?
UPDATE times
SET Is_Workday = 0
WHERE times.DateMonth IN (SELECT DateHoliday
FROM holidays)
AND Is_workday = 1;
Or are you trying to do something like a timesheet?
CREATE TABLE TimeSheet( EmployeeID INT NOT NULL,
DateWorked DATE NOT NULL,
HoursWorked DECIMAL(4,2) NOT NULL);
GO
INSERT INTO Timesheet VALUES (1, '1/1/2012',4.5);
INSERT INTO Timesheet VALUES (1,'2/1/2012',3.5);
Then calculate "billable hours" (holiday hours are at 2 times hourly rate)
SELECT ts.EmployeeID
, ts.DateWorked
, ts.HoursWorked
, CASE WHEN h.DateHoliday IS NULL THEN ts.HoursWorked ELSE ts.HoursWorked * 2 END AS BillableHrs
FROM TimeSheet ts
LEFT JOIN Holiday h
ON ts.DateWorked = h.DATEHOLIDAY;
In a word, I am not sure your design is right. Whether a date is a holiday or not depends only on a date, so there's no reason for a flag to be in your Times table at all. You can derive that by joining the Times table to the Calendar table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply