September 28, 2011 at 5:06 am
SET @Date = '2011-01-01'
SET @Date1 = '2011-01-30'
WHILE (@Date<=CONVERT(DATE,@Date1))
BEGIN
INSERT INTO @Temptable(EmpCode,Name,Dept,Designation,Shift,ShiftDate,
InTime,OutTime,Remark,Late1,EarlyDepature1,EarlyCome1,DelayGo1)
--ELBalance,CLBalance,ReliveJoin)
SELECT
E.EmpCode,
E.FName,
D.DeptName,
De.Descriptions AS Designation,
'Shift'= ISNULL(S.Descriptions,'Default'),
'Date' = @Date,
'InTime'= ISNULL(RIGHT(CONVERT(VARCHAR,SP.InTime,100),7),'00:00'),
'OutTime'=ISNULL(RIGHT(CONVERT(VARCHAR,SP.OutTime,100),7),'00:00'),
'Remarks' = CASE
WHEN CONVERT(DATE,E.DOJ) > CONVERT(DATE,@Date) THEN ''
WHEN CONVERT(DATE,E.DOL) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,DATEADD(DAY,-1,@Date)) THEN ''
WHEN LR.FromDate = @Date AND LT.Descriptions = 'OD' AND SP.InTime IS NOT NULL THEN 'OD/PR'
WHEN LR.FromDate = @Date AND LT.Descriptions = 'OD' AND SP.InTime IS NULL THEN 'OD'
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL AND LR.LeaveTypeID = LT.HLeaveTypeID THEN LT.Descriptions
WHEN SP.InTime IS NOT NULL AND LR.LeaveTypeID = LT.HLeaveTypeID THEN LT.Descriptions+'/PR' --LeaveRequest
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL AND HOL.LeaveTypeID = LT1.HLeaveTypeID AND HOL.HoliLeaveDate = @Date THEN LT1.Descriptions --HolidayLeavePost
WHEN SP.InTime IS NOT NULL AND HOL.LeaveTypeID = LT1.HLeaveTypeID AND HOL.HoliLeaveDate = @Date THEN LT1.Descriptions+'/PR'
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL THEN 'AB'
WHEN SP.InTime IS NULL OR SP.OutTime IS NULL THEN 'OP'
ELSE 'PR'
END
FROM dbo.HEmployee E
INNER JOIN @EmpTempTable TE ON E.EmployeeID = TE.EmployeeID
LEFT OUTER JOIN HShiftPost SP ON SP.EmployeeID = E.EmployeeID AND CONVERT(DATE,SP.InTime) = @Date AND SP.IsDeleted = 0
LEFT OUTER JOIN Department D ON E.DeptID = D.DeptCode
LEFT OUTER JOIN Designation De ON E.DesignationID = De.DesignationID
LEFT OUTER JOIn HShift S ON SP.ShiftID = S.HShiftID
LEFT OUTER JOIN HHolidayLeavePost HOL ON E.EmployeeID = HOL.EmployeeID AND HOL.HoliLeaveDate = @Date AND HOL.IsActive = 1
LEFT OUTER JOIN HLeaveRequest LR ON E.EmployeeID = LR.EmployeeID AND @Date BETWEEN LR.FromDate AND LR.ToDate
LEFT OUTER JOIN HLeaveType LT ON LR.LeaveTypeID = LT.HLeaveTypeID AND LR.Status = 18 --LT.Descriptions = 'Leave Approved'
LEFT OUTER JOIN HLeaveType LT1 ON HOL.LeaveTypeID = LT1.HLeaveTypeID
--LEFT OUTER JOIN HolidayDetail HO ON HO.HolidayDate = @Date
WHERE E.DOJ<= CONVERT(DATE,@ToDate) AND E.DOL ='1900-01-01' OR CONVERT(DATE,E.DOL) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate) OR CONVERT(DATE,E.DOL) >=CONVERT(DATE,@ToDate)
GROUP BY E.EmployeeID,E.EmpCode,E.FName,D.DeptName,S.Descriptions,SP.InTime,SP.OutTime,SP.OutTimeDifference,
SP.InTimeDifference,LT.Descriptions,LR.LeaveTypeID,LT.HLeaveTypeID,HOL.LeaveTypeID,HOL.EmployeeID,LT1.HLeaveTypeID,
LT1.Descriptions,E.IsFixedOff,HOL.HoliLeaveDate,LR.FromDate,LR.ToDate,S.InTime,SP.InTime,S.HShiftID,SP.ShiftID,
SP.EmployeeID,SP.OutTime,S.OutTime,De.Descriptions,HOL.IsHalfDay,HOL.HoliLeaveDate,LR.EmployeeID,E.DOJ,
E.DOL
ORDER BY E.EmployeeID--D.DeptName,E.EmpCode
SET @AddDate = DATEADD(DD,1,@Date)
SET @Date = CONVERT(DATE,@AddDate,103)
END
September 28, 2011 at 5:35 am
how does this look to you?CREATE TABLE #Calendar (CalenderDate date)
INSERT INTO #Calendar
VALUES
('2011-01-01'),('2011-01-02'),('2011-01-03'),('2011-01-04'),('2011-01-05'),('2011-01-06'),('2011-01-07'),('2011-01-08'),('2011-01-09'),('2011-01-10'),
('2011-01-11'),('2011-01-12'),('2011-01-13'),('2011-01-14'),('2011-01-15'),('2011-01-16'),('2011-01-17'),('2011-01-18'),('2011-01-19'),('2011-01-20'),
('2011-01-21'),('2011-01-22'),('2011-01-23'),('2011-01-24'),('2011-01-25'),('2011-01-26'),('2011-01-27'),('2011-01-28'),('2011-01-29'),('2011-01-30')
INSERT INTO @Temptable(EmpCode,Name,Dept,Designation,Shift,ShiftDate,
InTime,OutTime,Remark,Late1,EarlyDepature1,EarlyCome1,DelayGo1)
--ELBalance,CLBalance,ReliveJoin)
SELECT
E.EmpCode,
E.FName,
D.DeptName,
De.Descriptions AS Designation,
'Shift'= ISNULL(S.Descriptions,'Default'),
'Date' = C.CalenderDate,
'InTime'= ISNULL(RIGHT(CONVERT(VARCHAR,SP.InTime,100),7),'00:00'),
'OutTime'=ISNULL(RIGHT(CONVERT(VARCHAR,SP.OutTime,100),7),'00:00'),
'Remarks' = CASE
WHEN CONVERT(DATE,E.DOJ) > CONVERT(DATE,C.CalenderDate) THEN ''
WHEN CONVERT(DATE,E.DOL) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,DATEADD(DAY,-1,C.CalenderDate)) THEN ''
WHEN LR.FromDate = C.CalenderDate AND LT.Descriptions = 'OD' AND SP.InTime IS NOT NULL THEN 'OD/PR'
WHEN LR.FromDate = C.CalenderDate AND LT.Descriptions = 'OD' AND SP.InTime IS NULL THEN 'OD'
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL AND LR.LeaveTypeID = LT.HLeaveTypeID THEN LT.Descriptions
WHEN SP.InTime IS NOT NULL AND LR.LeaveTypeID = LT.HLeaveTypeID THEN LT.Descriptions+'/PR' --LeaveRequest
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL AND HOL.LeaveTypeID = LT1.HLeaveTypeID AND HOL.HoliLeaveDate = C.CalenderDate THEN LT1.Descriptions --HolidayLeavePost
WHEN SP.InTime IS NOT NULL AND HOL.LeaveTypeID = LT1.HLeaveTypeID AND HOL.HoliLeaveDate = C.CalenderDate THEN LT1.Descriptions+'/PR'
WHEN SP.InTime IS NULL AND SP.OutTime IS NULL THEN 'AB'
WHEN SP.InTime IS NULL OR SP.OutTime IS NULL THEN 'OP'
ELSE 'PR'
END
FROM dbo.HEmployee E
CROSS JOIN #Calendar C
INNER JOIN @EmpTempTable TE ON E.EmployeeID = TE.EmployeeID
LEFT OUTER JOIN HShiftPost SP ON SP.EmployeeID = E.EmployeeID AND CONVERT(DATE,SP.InTime) = C.CalenderDate AND SP.IsDeleted = 0
LEFT OUTER JOIN Department D ON E.DeptID = D.DeptCode
LEFT OUTER JOIN Designation De ON E.DesignationID = De.DesignationID
LEFT OUTER JOIn HShift S ON SP.ShiftID = S.HShiftID
LEFT OUTER JOIN HHolidayLeavePost HOL ON E.EmployeeID = HOL.EmployeeID AND HOL.HoliLeaveDate = C.CalenderDate AND HOL.IsActive = 1
LEFT OUTER JOIN HLeaveRequest LR ON E.EmployeeID = LR.EmployeeID AND C.CalenderDate BETWEEN LR.FromDate AND LR.ToDate
LEFT OUTER JOIN HLeaveType LT ON LR.LeaveTypeID = LT.HLeaveTypeID AND LR.Status = 18 --LT.Descriptions = 'Leave Approved'
LEFT OUTER JOIN HLeaveType LT1 ON HOL.LeaveTypeID = LT1.HLeaveTypeID
WHERE E.DOJ<= CONVERT(DATE,@ToDate) AND E.DOL ='1900-01-01' OR CONVERT(DATE,E.DOL) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate) OR CONVERT(DATE,E.DOL) >=CONVERT(DATE,@ToDate)
GROUP BY E.EmployeeID,E.EmpCode,E.FName,D.DeptName,S.Descriptions,SP.InTime,SP.OutTime,SP.OutTimeDifference,
SP.InTimeDifference,LT.Descriptions,LR.LeaveTypeID,LT.HLeaveTypeID,HOL.LeaveTypeID,HOL.EmployeeID,LT1.HLeaveTypeID,
LT1.Descriptions,E.IsFixedOff,HOL.HoliLeaveDate,LR.FromDate,LR.ToDate,S.InTime,SP.InTime,S.HShiftID,SP.ShiftID,
SP.EmployeeID,SP.OutTime,S.OutTime,De.Descriptions,HOL.IsHalfDay,HOL.HoliLeaveDate,LR.EmployeeID,E.DOJ,
E.DOL
ORDER BY E.EmployeeID--D.DeptName,E.EmpCode
Also, I would pull out the group by and use a distinct, since this could potentially lead to duplicate rows. If you want to use sum, count, max or min, just copy all other grouped items in the select to the group by, including the whole case statement.
This is just a piece of code, while I don't have the ddl available and parameters aren't declared. That means, my code is guaranteed bugfree.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 28, 2011 at 2:02 pm
Of course I meant my code isn't guaranteed bugfree...
And Celko, many people who can't write sql very well reuse code many times and probably he's not the creator of both the code and database. Of course you can rant, but I don't think that will lead to better questions in the future.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 2, 2011 at 12:06 pm
CELKO (9/28/2011)
Where is the DDL? Why do you have so many OUTER JOINs? That is a really bad code smell. Why are you doing all that silly COBOL-style string handling? Why do you use reserved words for data element names? Why did you get so many data element names wrong? For example, there is no such thing as a “type_id” – the element is one or the other, never, never both! Today, we use CAST(), COALESCE and other ANSI/ISO Standard code, not the old 1970's dialect you have.That temp table is a scratch tape from the 1950's. The bit flags are from assembly language. Sometime the “department_name” is just “dept” and other elements change name from line to line in the code.
Things like “H_Leave_Type” ought to be an attribute and not a table. You have First Normal form violation everywhere.
We need to throw out the whole mess and start over.
Heh... Why are you chewing on the OP? Did you ever consider that the OP may not have had anything to do with the design aspect of the database and probably doesn't have the authority to redesign it to be correct? Use terms like "the" instead of "you"... you'll still get your point across and your posts will sound a whole lot more friendly instead of what they sound like now... ad hominem attacks. 😉
As for the 1950's scratch table note... scratch tables/tapes worked very well in the 1950's and they still work well now. 😉
--Jeff Moden
October 2, 2011 at 1:23 pm
It would help if you could provide the DDL for the tables, sample data for the tables, and expected results based on the sample data.
Please read the article I referenece first in my signature block regarding asking for help to see how to post this information if you are interested other alternatives that may be more efficient.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply