how to split the days between two months

Can you give the structure of your table and the Query which are using you?

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

@Mohammed Rifayee,
Your code is working properly.It is based on month only.You are right man.

Thanks & Regards
Hari

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

this is table structure
empcode varchar(50) Checked
empname varchar(50) Checked
compname varchar(50) Checked
leavetype varchar(50) Checked
month varchar(50) Checked
fdate datetime Checked
tdate datetime Checked
reason varchar(400) Checked
sl int Checked
pl int Checked
lop int Checked
Unchecked

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

declare @tdate as datetime

declare @fdate as datetime

declare @monthdiff as int

set @fdate = '2012-01-25'

set @tdate = '2012-02-03'

set @monthdiff = DATEDIFF(mm,@fdate,@tdate)





Select DATEDIFF(dd,@fdate,@tdate)'Total Days',

Case 

When @monthdiff>0 Then DATEDIFF(d,@fdate, DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,@fdate)+1,0)))

When @monthdiff=0 Then DATEDIFF(d,@fdate,@tdate)

END as 'DaysInMonth'

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

how to execute this...if i select month and employee id in the dropdownlist ,it will calculate the total days of january month for the selected empid ,,,,,,,,how?

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

@Rifayeem What is your current Query, thats you are running for get the number of days. send it here and I will modify that.

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

hi,i am using like this to find sl leave,pl leave and lossofpay(lop)leave....
cmd = New SqlCommand("select sum(sl) from leavedepot where empcode like '" + Label4.Text + "'", connection)
cmd = New SqlCommand("select sum(pl) from leavedepot where empcode like '" + Label4.Text + "'", connection)
cmd = New SqlCommand("select sum(lop) from leavedepot where empcode like '" + Label4.Text + "'and month like'" + DropDownList2.Text + "'", connection)
this is your query ,i dont no how to execute in my program..actually in my payslip generation web form i am having lot of controls.the first control is to select the employee id,if the employee id is selected and the user is allowed to select the date from date in textbox1 and to date in textbox2,if 1st january to 31 january,as per the values,the total sl,pl,lop will be displayed in the respective textbox,,,how can i pass the textbox1 datevalue and textbox2 datevalue to this stored procedure
declare @tdate as datetime
declare @fdate as datetime
declare @monthdiff as int
set @fdate = '2012-01-25'
set @tdate = '2012-02-03'
set @monthdiff = DATEDIFF(mm,@fdate,@tdate)
Select DATEDIFF(dd,@fdate,@tdate)'Total Days',
Case
When @monthdiff>0 Then DATEDIFF(d,@fdate, DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,@fdate)+1,0)))
When @monthdiff=0 Then DATEDIFF(d,@fdate,@tdate)
END as 'DaysInMonth'

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

Hi...I got the point ..please check the following url.it may helps you..

http://www.developerscode.com/2012/01/implementing-date-search-functionality.html

let me know if have any queries

Tanisha Sayyad
.Net and Android Developer
INDIA(Andhra Pradesh)

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

@Rifayeem Best practice for this type of leave is that create Two entries for this e.g. one row for Jan(25-31) and second row for Feb(1-3). then you have to no need to change in query.
any ways I am trying to write a query for your currently situation.

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

i didnt get you properly....pls make me clear

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

Hi Rifayeem,

Please find the attached code for your query.


-- Sourabh
Please mark the answer, if it resolved your problem.
 Download source file

Sourabh07

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

in the run how can i pass the date to this query...

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

@Rifayeem for example if employee take leave from 25Jan to 3Feb, then make two entry in your database. One for 25Jan to 31jan and second for 1Feb to 3Feb, by this type entry you will not required any changes in your query.

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

can you show me an example...i dont no how to implement this ...during the run time

Mohamed Rifayee

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator

@Rifayeem You can do it from your application UI, from where you are currently entering the value.

Rifayeem, if this helps please login to Mark As Answer. | Alert Moderator