Date Range

[dg3249]

I need help with a date range problem.
I have an orders table which has a starting date and an ending date. For example;
order 1111, starting date Nov 1, 2002
endig date Dec 31, 2002

I need to pull all individual dates in this date range for all orders.

HELP!!

 

[JohnDTampaBay]

I need to pull all individual dates in this date range for all orders.

Try rephrasing the question in SQL terms and the answer may make itself obvious.

Do you mean I need to select distinct startdates and enddates where date is between 11/1/2002 and 12/31/2002?

If so, give this a shot:

SELECT DISTINCT startdate FROM Orders
WHERE startdate BETWEEN '11/1/2002' AND '12/31/2002'
UNION
SELECT DISTINCT enddate FROM Orders
WHERE enddate BETWEEN '11/1/2002' AND '12/31/2002'

I hope this helps. Good luck. --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[dg3249]

Not quite,

The values are not distinct.
There are multiple orders and each order has different start and end dates, that may overlap each other.
What I need is for my query to show all days that an order will be working as individual days of the week.
I tried what you suggested, but it didn't pull the values between the start and end dates..

 

[dg3249]

After playing with your query some more, its pulling up the starting and ending dates but not the ones in between.
Do you know how to do this.

Thanks for your help Angel

 

[JohnDTampaBay]

Hmmm. Following your original sample, you want a result set like this?
Order WorkingDate
1111 11-1-2002
1111 11-2-2002
1111 11-3-2002
...
1111 12-30-2002
1111 12-31-2002

If you can post the expected result you want to get from the sample data, perhaps we can slay this monster a little quicker. --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[dg3249]

This is exactly what I want.
All individual dates between the starting and ending dates.
I am assuming we will have to use a local variable, but am not to familiar with how to set this up.

 

[JohnDTampaBay]

Whew! (Angel wipes brow.) OK. How does this work for you?

SET NOCOUNT ON

CREATE TABLE #WorkDays
  (OrderID int NOT NULL, WorkDate datetime NOT NULL)

--Put starting and ending dates in
INSERT #WorkDays (OrderID, WorkDate)
SELECT OrderID, Startdate FROM #Orders
UNION
SELECT OrderID, Enddate FROM #Orders

--Insert missing dates
--as long as dates were just inserted
WHILE @@ROWCOUNT > 0   
  INSERT #WorkDays (OrderID, WorkDate)
  SELECT w2.OrderID, DateAdd(day,1,w2.WorkDate)
  FROM #WorkDays w2
    JOIN Orders o ON o.OrderID=w2.OrderID
  WHERE DateAdd(day,1,w2.WorkDate) NOT IN 
      (SELECT WorkDate FROM #WorkDays w1
       WHERE w1.OrderID = w2.OrderID)
  AND DateAdd(day,1,w2.WorkDate) < o.EndDate

--Output the data and clean up
SELECT * FROM #WorkDays ORDER BY OrderID, WorkDate
DROP TABLE #WorkDays

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[r937]

would you believe it, i wrote an article about this, and i use an integers table for it (integers table mentioned just the other day in thread220-396957)

Finding all the dates between two dates

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html

rudy

 

[JohnDTampaBay]

Nice work, Rudy. I solved a similar problem to reveal gaps in RecordIDs prior to import using the above methodology. Goes to show that there's more than one way to skin a cat. --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[tlbroadbent]

I created a Tek-Tips FAQ several months ago that addressed this topic.

faq183-840: How Can I Find Gaps in Date and Number Sequences?
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[dg3249]

I was actually able to get this working in Crystal without changing my SQL Query.
Thank you for all of your help.