Without using While Loop How to it Work

Question

Post reply

Without using While Loop How to it Work

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
September 28, 2011 at 5:06 am

#244595

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.