Can u solve this?

rajanikrishna · May 28th, 2004, 12:45 AM

Hi friends,

I have leaves table. The structure is

indexno empid nodays startdate enddate leavetype(CL/SL/VA)
1 0001 2 1/25/2004 1/26/2004 CL(Same months&year)
2 0001 5 2/27/2004 3/2/2004 SL(diff months&same year)
3 0001 10 12/25/2003 1/3/2004 VA(diff year and months)
4 0001 7 12/28/2004 1/3/2005 VA(diff year&months)

I want to generate a payslip for the empid. I want to calculate the no of leaves taken by emp in the selected year and month and total no of leaves in the selected year. And i need to generate pay slip for any month and year.
Can anyone suggest the solution?

DaveGerard · May 28th, 2004, 08:52 AM

Assuming that each record is one leave and you're not trying to count days.

EmpId = 1
MyYear = 2004
sql = "select count(indexno) from LEAVES"
sql = sql & " where EmpId = " & EmpId
sql = sql & " and StartDate >= '1/1/" & MyYear & "'"
sql = sql & " and EndDate <= '12/31/" & MyYear & "'"

If you wanted to find out the number of days then you would have to use something like a DateDiff comparison.

happygv · May 28th, 2004, 01:26 PM

What database are you using? ACCESS or MSSQL server?

_________________________
-Vijay G

Strive for Perfection

happygv · May 28th, 2004, 03:50 PM

DaveGerard,

May be by mistake the solution given counts the Indexno. Actually what RajaniKrishna might want is to calcualte the sum of leave for an employee for a month or a year given. As it shows the nodays and startdate - enddate there in his post, I believe he should be asking for calculating the total number of leave taken for a period.

RajaniKrishna,

As far as I have observed the table structure and the data posted there, it seemed a little tough to calculate that. May be I would have stored it as a seperate record for every day an employee takes leave.

But still I have got a solution to your question.

YES, ONE CAN SOLVE THIS.

I have done this for the Month part alone. You can test it out changing the Year and Month part in the code below marked as red.

This is purely done based on the data you have posted here. If you find any issues with it post here, let me take a look at it.

I would be online for another 4 hrs or so. Then I am travelling for the next couple of days.

All the best.

Code:

SET NOCOUNT ON
Declare @MonthStart datetime, @MonthEnd datetime
Select @MonthStart='2005-01-01 00:00:00.000'
Select @MonthEnd=dateadd(dd,-1,dateadd(mm,1,@MonthStart))

DECLARE @empid varchar(25), @nodays int, @startdate datetime, @enddate datetime, @LeaveCnt int
Select @LeaveCnt=0

Declare Generate_Leave_details CURSOR FOR
Select empid, nodays, startdate, enddate from Test
OPEN Generate_Leave_details 
    FETCH NEXT FROM Generate_Leave_details into @empid, @nodays, @startdate, @enddate 
    while @@FETCH_STATUS = 0 
    BEGIN
        If month(@startdate)=month(@enddate) and year(@startdate)=year(@enddate) and Month(@MonthStart)=Month(@startdate) and Year(@MonthStart)=Year(@startdate)-- If Leaves taken is in same month & year
            Set @LeaveCnt = @LeaveCnt + @nodays
        ELSE
        If year(@startdate)=year(@enddate) and month(@startdate)<>month(@enddate) -- If Leaves taken is in diff month & same year
            If Year(@MonthStart)=Year(@startdate) and Month(@MonthStart)=Month(@startdate)
                set @LeaveCnt = @LeaveCnt + datediff(dd,@startdate,@MonthEnd)+1
            Else
            If Month(@MonthStart)=Month(@enddate) and Year(@MonthStart)=Year(@startdate)
                Set @LeaveCnt = @LeaveCnt + datediff(dd,@MonthStart,@enddate)+1

        If year(@startdate)<>year(@enddate) -- If Leaves taken is in diff month & diff year
            If Year(@MonthStart)=Year(@startdate) and Month(@MonthStart)=Month(@startdate) and @startdate<@MonthEnd
                Set @LeaveCnt = @LeaveCnt + datediff(dd,@startdate,cast(cast(year(@MonthEnd) as varchar(4)) + "-12-31" as datetime))+1
            Else
            If Year(@MonthStart)=Year(@enddate) and Month(@MonthStart)=Month(@enddate) and @enddate>@MonthStart
                Set @LeaveCnt = @LeaveCnt + datediff(dd,cast(cast(year(@MonthStart) as varchar(4)) + "-01-01" as datetime),@enddate)+1

        FETCH NEXT FROM Generate_Leave_details into @empid, @nodays, @startdate, @enddate 
    END
    Select @LeaveCnt as Result
Close Generate_Leave_details 
Deallocate Generate_Leave_details 
SET NOCOUNT OFF

Cheers!

_________________________
-Vijay G

Strive for Perfection

happygv · May 28th, 2004, 03:54 PM

Hi RajaniKrishna,

I haven't got time to work on summing up Year-wise leaves Total. May be you can give a try on that based on my previous post.

I would suggest you to make this a stored procedure, passing value for @MONTHSTART and EMPID as parameters to it, if you find this working perfect. Also you will have to make changes such that the cursor fetches data for the given EMPID, which I haven't done in my previous post.

Cheers!

_________________________
-Vijay G

Strive for Perfection

happygv · May 28th, 2004, 04:00 PM

Please change the table name there. I have used it as "TEST"

Code:

Declare Generate_Leave_details CURSOR FOR
Select empid, nodays, startdate, enddate from Test

Sorry to not have mentioned on that.

_________________________
-Vijay G

Strive for Perfection

rajanikrishna · May 28th, 2004, 08:31 PM

Hi,
Thanx for the reply. But i am using MSAccess. I need to find the
no of days in the current month(in which i want to generate payslip) and year.
Ex: current month and year is 5/2004
Startdate : 4/29/2004 and no of days : 4, enddate:5/2/2004
So the no of leaves in the current month is 2 only. If startdate and enddate are same month and year
we can find directly "nodays".

If it is different years(12/26/2003 to 1/5/2004) and the current month is Jan/2004.
I think u got idea.

Thanx

happygv · June 1st, 2004, 04:41 AM

Oh... You are using MSaccess! Sorry, that does not support CURSOR or Stored procs. But I can suggest you to use the same algorithm to get there from your front end(ASP) or from access forms.

As said, this algorithm, for a given month and year, generates the startdate and enddate of that month, the calculates the nodays(Leave) for that month alone, be it for same month & year, or different month & same year, or different month and different year, everything is taken care in that procedure that I had posted here. The result is NoDays(Leave) for the given month/year. Even the instance that you have mentioned in your previous post has been taken care of.

Here is what I get based on the data you have posted in your original post.

If given month and year are as follows.

2005-01-01 00:00:00.000
Result
-----------
3

2004-01-01 00:00:00.000
Result
-----------
5

2004-02-01 00:00:00.000
Result
-----------
3

2004-03-01 00:00:00.000
Result
-----------
2

2003-12-01 00:00:00.000
Result
-----------
7

2004-12-01 00:00:00.000
Result
-----------
4

If I could get time to do that in Access/ASP for you, let me post that here later.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

rajanikrishna · June 2nd, 2004, 06:37 AM

Hi Happygv,
Many many thanx for u r afford in this.
Thanx.