help please!!

[yumyam09]

iam making a simple computation but iam having a problem with this

If btnAdd.Text = "&Add" Then
            cbleave.Enabled = True
            dtFrom.Enabled = True
            dtTo.Enabled = True
            dtApplied.Enabled = True
            clear()
            btnAdd.Text = "&Save"

            btnSearch.Enabled = True



            sql = "SELECT IFNULL(MAX(cnt),0) FROM tblleaveapp"
            NewDataSet(sql)
        Else
            If cbleave.Text = String.Empty Then
                MsgBox("Choose Leave Type.", MsgBoxStyle.Information, Me.Text)
                Exit Sub
            End If

            sql = "SELECT leaveLeft FROM tblleaveapp"
            NewDataSet(sql)
            If ds.Tables("a").Rows.Count > 0 Then



            Else
                Dim available As Integer
                Dim days As Integer
                Dim use As Integer


                days = Integer.Parse(txtnumber.Text)
                use = Integer.Parse(txtUse.Text)


                available = days - use

                txtavailable.Text = available.ToString


            End If


            Dim available As Integer
            Dim days As Integer
            Dim use As Integer


            days = Integer.Parse(txtnumber.Text)
            use = Integer.Parse(txtUse.Text)


            available = days - use

            txtavailable.Text = available.ToString


            sql = "INSERT INTO tblleaveapp (employeeID, leaveType, leaveNumber, `from`, `to`, leaveUse, applied, leaveLeft) VALUES ('" & replaceQuote(txtID.Text) & "','" & replaceQuote(cbleave.Text) & "','" & replaceQuote(txtnumber.Text) & "','" & replaceQuote(dtFrom.Text) & "','" & replaceQuote(dtTo.Text) & "','" & replaceQuote(txtUse.Text) & "','" & replaceQuote(dtApplied.Text) & "','" & replaceQuote(txtavailable.Text) & "' )"
            SaveRecord(sql)

            clear()
            cbleave.Enabled = False
            dtFrom.Enabled = False
            dtTo.Enabled = False
            dtApplied.Enabled = False
            btnAdd.Text = "&Add"
            btnSearch.Enabled = False
            MsgBox("Are you sure you want to save this data? This data cannot be edited.", MsgBoxStyle.Information + MsgBoxStyle.YesNo, Me.Text)
        End If

the logic is i need to subtract the available days to to desire days and then the total of the two will become my available days to another transaction
example:
10 - 2 = 8
8 - 6 = 2
2 - 2 = 0
something like that please help me

thanks in advance

[M.Waqas Aslam]

you want to perform this action in your query or in your application?

[yumyam09]

in query sir did you know how??? please help me

[M.Waqas Aslam]

you can use

DATEDIFF(dd, FirstDate,2ndDate  )

this will return difference of days .firstdate and 2nd date are two dates .hope this will solve your prob.:)
If yes then please mark your thread solved and vote me up :)

Regards

[M.Waqas Aslam]

this is my query i used it in my project , this will give you an idea .

select ProductName ,Packing , BatchNbr ,ExpiryDate ,ExpireInDays  
from (
select ph.productname,ph.Packing ,b.BatchNbr ,b.ExpiryDate ,d.distributorid,
case when b.ExpiryDate > GETDATE() then DATEDIFF(dd, GETDATE(),b.ExpiryDate  )
else DATEDIFF(dd, GETDATE(),b.ExpiryDate  ) end as ExpireInDays
from HOProducts ph
inner join DistProducts  dp on ph.HOProductId = dp.HOProductId 
inner join InvoiceTransactionBody itb on itb.ProductId  = dp.ProductId 
inner join Distributors d on dp.DistributorId = d.DistributorId 
inner join Batches b on itb.BatchNo = b.BatchNbr  
) as n Where 1=1

Regards

[yumyam09]

example iam the employee and i want to have a vacation leave and i want to have a vacation in 4 days and the vacation leave have 10 days maximum so if i get 4 days my vacation leave will be 6 days automatically

then on my another vacation i want to used again my vacation leave and i want to acquired 3 days so my vacation leave will be 3 days left automatically

here`s my problem sir...i cant get the total value of my first transaction to my available days
example 10 is the available and i used 3 so my left days is 7
on my another transaction the available days was 10 again which is wrong
10 - 3 = 7 first transaction
7 - 2 = 5 second transaction

please help me T_T

[M.Waqas Aslam]

please provide the fields of your tables , which are used in this query , so i can able to provide you solution.

Regards

[yumyam09]

employeeID, leaveType(example vacation leave/sick leave), leaveNumber(maximum number of leave depends on leaveType), from, to, leaveUse(days used), leaveLeft(days left or days available after the transaction), applied(date of transaction)

[M.Waqas Aslam]

where is the limit of days ? i mean in which field you are defining the limit as you mentioned above 10 is the limit?

[yumyam09]

where is the limit of days ? i mean in which field you are defining the limit as you mentioned above 10 is the limit?

at leaveNumber

[M.Waqas Aslam]

well i suggest you to use head and detail tables for this to maintain your records, for example headtable has fields , RecHeadID,LeaveType,MaxDays and detail table fields RecDetailID,EmpID,RecHeadID,from,to with these two tables you can manage all your leave details of your employees , but here is a query for your current table ,

select employeeid,leavetype,leavenumber,leaveuse , (leavenumber-leaveuse) as leaveleft
from
(
select employeeid,leaveType,LeaveNumber,
(
select sum(leaveuse)
from table1
)  as LeaveUse
from table1
) m

i not tested it. if you caught any error , please do inform me,

Regards

[poojavb]

example iam the employee and i want to have a vacation leave and i want to have a vacation in 4 days and the vacation leave have 10 days maximum so if i get 4 days my vacation leave will be 6 days automatically

then on my another vacation i want to used again my vacation leave and i want to acquired 3 days so my vacation leave will be 3 days left automatically

here`s my problem sir...i cant get the total value of my first transaction to my available days
example 10 is the available and i used 3 so my left days is 7
on my another transaction the available days was 10 again which is wrong
10 - 3 = 7 first transaction
7 - 2 = 5 second transaction

please help me T_T

Try this

select  DateDiff("d",(Format([FromDate], 'MM/dd/yyyy')),(Format([ToDate], 'MM/dd/yyyy'))) from Tablename

[M.Waqas Aslam]

why some one down vote my post :-X with out any reason :-| , this is not fair :(