C# .NET - Need help with the query

Asked By abinav shankar on 23-Jan-12 01:06 AM

Hi

Pl consider the following scenario, I am applying for leave from 01/03/2012 to 01/04/2012 (i.e from jan 1st to jan 4th 4 days) Now after applying leave i am again applying for leave on 01/02/2012 to 01/03/2012 (2nd jan to 3rd jan) now I should not allow to apply leave within this period as I have taken leave during this period
how to do it pl help

Venkat K replied to abinav shankar on 23-Jan-12 01:15 AM
How you are storing the leave details of the employee?
If you are storing in a separate table you can query the current period against the existing data whether they have already applied for the leave.

SELECT 1 FROM tblLeaves WHERE LeaveDate BETWEEN LeaveFROMDate AND LeaveTODate

If you get the result mean that the employee has already applied leave for the above period.

Thanks
Jitendra Faye replied to abinav shankar on 23-Jan-12 01:26 AM

For this use query like this-


select * from tablename
 where (fromdate> StoredFromDate and  fromdate< StoredToDate)
       and (Todate> StoredFromDate and  Todate< StoredToDate)
    or  (Todate= StoredFromDate or  Todate= StoredToDate)
       or  (fromdate= StoredFromDate or  fromdate= StoredToDate)



Try this and let me know.

Riley K replied to abinav shankar on 23-Jan-12 01:53 AM

Create a Sproc that accepta a datetime parameter and return true or false if the date falls within the range

Create procedure [dbo].[CheckLeaveAvailability]
@LeaveFROMDate  datetime
as
begin
if not exists(
SELECT  * FROM EmpDOJ WHERE @LeaveFROMDate between LeaveFrom and LeaveTo
)
select 'true'
else
select 'false'
end

Regards

kalpana aparnathi replied to abinav shankar on 23-Jan-12 02:46 PM
Try this way:

public IQueryable<OrderView> GetOrderViewsByProperty(int intPropAddrID, DateTime dtBegOrderDate, DateTime dtEndOrderDate)
  {
    return this.ObjectContext.OrderViews.Where(o => o.PropAddrID.Value.Equals(intPropAddrID) &&
    (dtBegOrderDate == null || EntityFunctions.TruncateTime(o.OrderDT) >= EntityFunctions.TruncateTime(dtBegOrderDate)) &&
    (dtEndOrderDate == null || EntityFunctions.TruncateTime(o.OrderDT) <= EntityFunctions.TruncateTime(dtEndOrderDate.Date)));
  }