How can I retrive data between two dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can I retrive data between two dates

Author

Topic

ajaypunekar1
Starting Member

1 Post

Posted - 2012-08-12 : 03:16:08

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".

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?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-12 : 08:52:43

SELECT YourColumnNameHere
WHERE (datefrom >= '2012-08-07' AND datefrom <='2012-08-09') AND (dateto <= '2012-08-09' AND dateto >='2012-08-07')

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 11:50:43

this will give you quantity remaining for a prdt between two dates


SELECT p.ProductName,COALESCE(bp.Quantity,0) AS BookedQty, p.Quantity - COALESCE(bp.Quantity,0) AS RemainingStock
FROM Product p
LEFT JOIN ( SELECT ProductID,SUM(Quantity) AS TotalQty
            FROM BookedProduct bp1
            INNER JOIN BookingDetails bd
            ON bd.CustomerID = bp1.CustomerID
            WHERE @Date >= bd.DateFrom AND @Date < bd.DateTo +1           
            GROUP BY ProductID
         )bp
ON bp.ProductID = p.ProductID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 11:53:16

Also current design is not the recommended approach. I dont think you need ProductName in bookedproduct table as its already present in Product table and you could very easily get it by linking through ProductID added as a FK. There's no need to duplicate the description data here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources