How can I retrive data between two dates and between two different column i.e Date From and DateTo.

Question

I want to select data between two dates. first date is in one column i.e. "DateFrom" and second date is in second column i.e. "DateTo".

So how can I do query for this?

Answer 1

you have two date fields, both will have multiple values. So how are you going to get data, there must be some date value which needs to be checked. Say there should be a value for checkDate below which you need to check between the date ranges:

select * from table1 where checkDate between FromDate and ToDate
//or
select * from table1 where checkDate > Convert(Date, FromDate) and checkDate < Convert(Date, ToDate)

regards

joon

Answer 2

Can you please elaborate on your question? Do you want to pass 2 dates as a date range also?

Please check this WiKi page

http://wiki.lessthandot.com/index.php/Date_Range_WHERE_Clause_Simplification

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

I have four tables i.e.

1: Product

2: Customer

3:BookingDetails

4:BookedProduct

In first table i.e. Product there are three fields i.e. 1. ProductID[PK],2.ProductName,3.Quantity

In second table i.e. Customer there are four fields i.e. 1.CustomerID[PK],2.FirstName,3.LastName,4.PhoneNumber

In third table i.e. BookingDetails there are six fields i.e  1. CustomerID[FK],2.DateFrom, 3.DateTo, 4.TotalDays, 5.TimeFrom, 6.TimeTo

In fourth table i.e. BookedProduct there are five fields i.e. 1.CustomerID[FK],2.SupplierID[FK],3.ProductID[FK],4.ProductName,5.Quantity

my question is when any customer want to book any product in particular date for ex. datefrom = 2012-08-07 and dateto=2012-08-09

at that time I want to check how much Quantity remaining for a particular product  in our stock in between these dates.

So I want to count total booked product quantity in between these two dates.

How I can do query for this?

Thank you,

ajay

Answer 4

How do you connect BookingDetails with BookedProduct table?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Hello, Naomi N

BookingDetails and BookedProduct have CustomerID as a Foriegn key.

Thank you,

ajay

Answer 6

They have it as a foreign key, but I don't see a common column  between two tables? Or are you saying that each customer can have only a single BookingDetails and BookedProduct per customer? E.g. customer will identify the common rows between these two tables?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

In BookingDetails , BookedProduct table I use CustomerID.
I can get details through this ID.

One customer can book more then one product at a time.

I have tried this query

SELECT        sum(BookedProduct.Quantity)
FROM            BookedProduct INNER JOIN
                         Customer ON BookedProduct.CustomerID = Customer.CustomerID INNER JOIN
                         BookingDetails ON Customer.CustomerID = BookingDetails.CustomerID INNER JOIN
                         Product ON BookedProduct.ProductID = Product.ProductID
						 where BookedProduct.ProductID = 121 and  DateFrom='2012-08-01' and  DateTo='2012-08-09' 

But it require CustomerID.

If I update above query like this

SELECT        sum(BookedProduct.Quantity)
FROM            BookedProduct INNER JOIN
                         Customer ON BookedProduct.CustomerID = Customer.CustomerID INNER JOIN
                         BookingDetails ON Customer.CustomerID = BookingDetails.CustomerID INNER JOIN
                         Product ON BookedProduct.ProductID = Product.ProductID
						 where BookedProduct.ProductID = 121 and bookedproduct.CustomerID=111121

It gives sum of product quantity of productID =121 where customerid =111121

I don't want like this.

I want to define following terms in query

DateFrom And DateTo is required.

Answer 8

Hello all,

I have solved my problem with this query

declare @fromDate date
set @fromDate = '2012-08-15'

declare @toDate date
set @toDate = '2012-08-16'

declare @fromTime time
set @fromTime ='12:33:46'

declare @toTime time
set @toTime='03:33:46'

select sum(bookedproduct.Quantity) Quantity
from BookingDetails INNER JOIN BookedProduct on BookedProduct.CustomerID = BookingDetails.CustomerID

where ProductID=121
and (DateFrom >= @fromDate or DateTo <= @toDate) and (DateFrom between @fromDate and @toDate)
and (TimeFrom >=@fromTime or TimeTo <=@fromTime)
and(TimeFrom between @fromTime and @fromTime)            
                   

again thank you for your help.