SELECT Holyday.uid, Users.name, Holyday.cate, (CASE cate WHEN '외출' THEN 1 ELSE Holyday.term END) AS CrDay, Holyday.termDr AS DrDay, Holyday.DrTime, Holyday.CrTime, Holyday.gcode, EETGW_GroupUser.Process, Holyday.sdate, Holyday.edate, Holyday.iwol FROM Users WITH (NOLOCK) INNER JOIN Holyday ON Users.id = Holyday.uid LEFT OUTER JOIN EETGW_GroupUser WITH (NOLOCK) ON Holyday.uid = EETGW_GroupUser.uid AND Holyday.gcode = EETGW_GroupUser.gcode WHERE (Holyday.gcode = @gcode) AND (Holyday.sdate BETWEEN @sd AND @ed) AND (EETGW_GroupUser.level > 0) AND (ISNULL(EETGW_GroupUser.useUserState, 0) = 1) AND (ISNULL(EETGW_GroupUser.exceptHoly, 0) = 0) ORDER BY Holyday.uid, CrDay DELETE FROM [Holyday] WHERE (([idx] = @Original_idx) AND ((@IsNull_sdate = 1 AND [sdate] IS NULL) OR ([sdate] = @Original_sdate)) AND ((@IsNull_uid = 1 AND [uid] IS NULL) OR ([uid] = @Original_uid)) AND ((@IsNull_cate = 1 AND [cate] IS NULL) OR ([cate] = @Original_cate)) AND ((@IsNull_Dr = 1 AND [termDr] IS NULL) OR ([termDr] = @Original_Dr)) AND ((@IsNull_Cr = 1 AND [term] IS NULL) OR ([term] = @Original_Cr))) SELECT Holyday.gcode, Holyday.idx, Holyday.sdate, Holyday.uid, Holyday.cate, dbo.GetKuntaeJan_Day(Holyday.gcode, Holyday.uid, Holyday.sdate, Holyday.idx - 1) AS jun, Holyday.termDr AS Dr, Holyday.term AS Cr, Holyday.contents, Users.name, EETGW_GroupUser.Process, Holyday.iwol FROM Holyday WITH (NOLOCK) LEFT OUTER JOIN EETGW_GroupUser WITH (NOLOCK) ON Holyday.gcode = EETGW_GroupUser.gcode AND Holyday.uid = EETGW_GroupUser.uid LEFT OUTER JOIN Users ON Holyday.uid = Users.id WHERE (Holyday.gcode = @gcode) AND (Holyday.uid LIKE @uid) AND (Holyday.sdate BETWEEN @sd AND @ed) AND (ISNULL(Holyday.DrTime, 0) + ISNULL(Holyday.CrTime, 0) = 0) AND (EETGW_GroupUser.useUserState = 1) ORDER BY Holyday.sdate, Holyday.idx UPDATE [Holyday] SET [sdate] = @sdate, [uid] = @uid, [cate] = @cate, [termDr] = @Dr, [term] = @Cr, [contents] = @contents WHERE (([idx] = @Original_idx) AND ((@IsNull_sdate = 1 AND [sdate] IS NULL) OR ([sdate] = @Original_sdate)) AND ((@IsNull_uid = 1 AND [uid] IS NULL) OR ([uid] = @Original_uid)) AND ((@IsNull_cate = 1 AND [cate] IS NULL) OR ([cate] = @Original_cate)) AND ((@IsNull_Dr = 1 AND [termDr] IS NULL) OR ([termDr] = @Original_Dr)) AND ((@IsNull_Cr = 1 AND [term] IS NULL) OR ([term] = @Original_Cr))); SELECT idx, sdate, uid, cate, dbo.GetKuntaeJan_Day(gcode, uid, sdate, idx - 1) AS jun, termDr AS Dr, term AS Cr, contents FROM Holyday WHERE (idx = @idx) ORDER BY sdate, idx SELECT Holyday.gcode, Holyday.idx, Holyday.sdate, Holyday.uid, Holyday.cate, dbo.GetKuntaeJan_Time(Holyday.gcode, Holyday.uid, Holyday.sdate, Holyday.idx - 1) AS jun, Holyday.DrTime AS Dr, Holyday.CrTime AS Cr, Holyday.contents, Users.name, EETGW_GroupUser.Process, Holyday.iwol FROM Holyday WITH (NOLOCK) LEFT OUTER JOIN EETGW_GroupUser WITH (NOLOCK) ON Holyday.gcode = EETGW_GroupUser.gcode AND Holyday.uid = EETGW_GroupUser.uid LEFT OUTER JOIN Users WITH (NOLOCK) ON Holyday.uid = Users.id WHERE (Holyday.gcode = @gcode) AND (Holyday.uid LIKE @uid) AND (Holyday.sdate BETWEEN @sd AND @ed) AND (ISNULL(Holyday.DrTime, 0) + ISNULL(Holyday.CrTime, 0) <> 0) AND (EETGW_GroupUser.useUserState = 1) AND (Holyday.cate <> 'PMS') ORDER BY Holyday.sdate, Holyday.idx SELECT Holyday.gcode, Holyday.idx, Holyday.sdate, Holyday.uid, Holyday.cate, dbo.GetKuntaeJan_TimePMS(Holyday.gcode, Holyday.uid, Holyday.sdate, Holyday.idx - 1) AS jun, Holyday.DrTimePMS AS Dr, Holyday.CrTime AS Cr, Holyday.contents, Users.name, EETGW_GroupUser.Process, Holyday.iwol FROM Holyday WITH (NOLOCK) LEFT OUTER JOIN EETGW_GroupUser WITH (NOLOCK) ON Holyday.gcode = EETGW_GroupUser.gcode AND Holyday.uid = EETGW_GroupUser.uid LEFT OUTER JOIN Users WITH (NOLOCK) ON Holyday.uid = Users.id WHERE (Holyday.gcode = @gcode) AND (Holyday.uid LIKE @uid) AND (Holyday.sdate BETWEEN @sd AND @ed) AND (EETGW_GroupUser.useUserState = 1) AND (Holyday.cate = 'PMS') AND (ISNULL(Holyday.DrTimePMS, 0) + ISNULL(Holyday.CrTime, 0) <> 0) ORDER BY Holyday.sdate, Holyday.idx