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

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:
[vbcol=seagreen]
> 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 t
o
> 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 wit
h
> an OrderDate recorded on the 5 October then the query would return 5 October.[/vbc
ol]

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.

Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.

CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

Retrieving row- and field-level modification dates

Hello all,

(This was previously posted @. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1935938&SiteID=1&mode=1, I transferred it here because it's more relevant)

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
I use C++ non-.NET ADO in order to access that database (msado15.dll).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)

Hi Tim,

In SQL Server 2005 there has been row versioining feature implemented which can help you but this has some reatining capacity to certain time period depeding on the settings for your Engine.

Read about it more in BOL.

If you have lesser than SQL server 2005 version then you may have to create columns which track these changes based on date.

Can you tell me if this database is OLTP or OLAP database ?

Cheers

Sat

|||Hi Sat

You seem to know more than me about different kinds of databases and database technologies.
I don't know what's BOL, OLTP or OLAP.
I use Microsoft Access, and accessing it through OLEDB (.NET).