Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.