how to split the days between two months

Posted by Rifayeem under Sql Server on 1/3/2013 | Points: 10 | Views : 1434 | Status : [Member] | Replies : 15
hi i am developing a web application,actually my doubt is if the person is taking a leave fromdate 25th january to 3rd febraury,total days is 9 days,it will be working properly,my problem is,when i preparing a payslip for the particular employee,if i select the empcode the total number of leave days for the month of january is displayed in the textbox,for the month of january 6days the person takes leave,the remaining 3 days for the month febraury,so i want to retrieve the january days only not febrarury days,,can anyone write a query for this.

Mohamed Rifayee


Responses

Posted by: Kundan64 on: 1/11/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Hariinakoti on: 1/15/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Rifayeem on: 1/15/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kundan64 on: 1/15/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Rifayeem on: 1/15/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kundan64 on: 1/16/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Rifayeem on: 1/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Taanu51 on: 1/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kundan64 on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Rifayeem on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
i didnt get you properly....pls make me clear

Mohamed Rifayee

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

Posted by: Sourabh07 on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Rifayeem on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kundan64 on: 1/18/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Rifayeem on: 1/18/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kundan64 on: 1/23/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Login to post response