Matching Dates

Ok guys fairly complicated one here...
I have a single table with 20,000 transactions within it. Each transaction
has a date attached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and
a WeekNumber. i.e.:

startdate finishdate weeknumber
01/01/2007 08/01/2007 1

What I need to be able to do is take each transactions from the transactions
table and link it to this calendar table to get the weeknumber of when each
transaction occured.
How on earth can I produce this?

Many thanks.
Ash.

hi,

ashg657 wrote:
Ok guys fairly complicated one here...
Not really

I have a single table with 20,000 transactions within it. Each transaction
has a date attached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and
a WeekNumber. i.e.:
startdate finishdate weeknumber
01/01/2007 08/01/2007 1
You need a Join:

SELECT t.*, w.WeekNumber
FROM Transactions t
INNER JOIN Weeks w
ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

http://office.microsoft.com/en-us/ac...984841033.aspx
http://www.mvps.org/access/datetime/date0012.htm

mfG
-- stefan --

On 21 Aug, 10:30, Stefan Hoffmann wrote:
I have a single table with 20,000 transactions within it. Each transaction
has adateattached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and
a WeekNumber. i.e.:
startdate finishdate weeknumber
01/01/2007 08/01/2007 1

You need a Join:

SELECT t.*, w.WeekNumber
FROM Transactions t
INNER JOIN Weeks w
ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

If OP is to do that -- and the suggestion is a good one -- then they
should make the data in their calendar table more like

startdate=#2007-01-01 00:00:00#
finishdate=#2007-01-08 23:59:59#
weeknumber=1

With their existing data (i.e. one day granularity), the value
#2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
weeknumber at all!

Alternatively, they could ensure that every DATETIME value encountered
is of one day graularity (e.g. only use SQL stored procs with strongly
type DATETIME parameters always 'rounded') but it's must easier to
accept the fact that all DATETIME values are of one second graularity
and code accordingly.

Jamie.

--

All Date values in Access are not granular to 1 second. It is up to the
developer to determine and control whether a time value is included. That is
why there are three different functions.
Date() returns only the date portion.
Now() returns date and time.
Time returns only the time.

What is not clear here is which date to use for the week. There are two
dates, but the OP did not say which should be used to return the week. In
the example, the start date is week 1, but the end date is certainly not.

Additionally, is there any reason the DatePart function can't be used to
determine the week rather than having to maintain a table? It would be
faster.

=DatePart("ww",SomeDate)
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On 21 Aug, 10:30, Stefan Hoffmann wrote:
I have a single table with 20,000 transactions within it. Each transaction
has adateattached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and
a WeekNumber. i.e.:
startdate finishdate weeknumber
01/01/2007 08/01/2007 1

You need a Join:

SELECT t.*, w.WeekNumber
FROM Transactions t
INNER JOIN Weeks w
ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

If OP is to do that -- and the suggestion is a good one -- then they
should make the data in their calendar table more like

startdate=#2007-01-01 00:00:00#
finishdate=#2007-01-08 23:59:59#
weeknumber=1

With their existing data (i.e. one day granularity), the value
#2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
weeknumber at all!

Alternatively, they could ensure that every DATETIME value encountered
is of one day graularity (e.g. only use SQL stored procs with strongly
type DATETIME parameters always 'rounded') but it's must easier to
accept the fact that all DATETIME values are of one second graularity
and code accordingly.

Jamie.

--

Thanks for the input.
DatePart could not be used as our company calendar runs from October -
September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have
decided that this seperate table is necessary - correct me if I am wrong.
The only other suggestion I have had is to create a table with every single
date of the year in it and assign week numbers to each indivudally, possible
a long winded way of doing things but never the less I could see it working.
Many thanks again.

"Klatuu" wrote:

All Date values in Access are not granular to 1 second. It is up to the
developer to determine and control whether a time value is included. That is
why there are three different functions.
Date() returns only the date portion.
Now() returns date and time.
Time returns only the time.

What is not clear here is which date to use for the week. There are two
dates, but the OP did not say which should be used to return the week. In
the example, the start date is week 1, but the end date is certainly not.

Additionally, is there any reason the DatePart function can't be used to
determine the week rather than having to maintain a table? It would be
faster.

=DatePart("ww",SomeDate)
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On 21 Aug, 10:30, Stefan Hoffmann wrote:
I have a single table with 20,000 transactions within it. Each transaction
has adateattached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and
a WeekNumber. i.e.:
startdate finishdate weeknumber
01/01/2007 08/01/2007 1

You need a Join:

SELECT t.*, w.WeekNumber
FROM Transactions t
INNER JOIN Weeks w
ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

If OP is to do that -- and the suggestion is a good one -- then they
should make the data in their calendar table more like

startdate=#2007-01-01 00:00:00#
finishdate=#2007-01-08 23:59:59#
weeknumber=1

With their existing data (i.e. one day granularity), the value
#2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
weeknumber at all!

Alternatively, they could ensure that every DATETIME value encountered
is of one day graularity (e.g. only use SQL stored procs with strongly
type DATETIME parameters always 'rounded') but it's must easier to
accept the fact that all DATETIME values are of one second graularity
and code accordingly.

Jamie.

--

On 21 Aug, 18:02, Klatuu wrote:
It is up to the
developer to determine and control whether a time value is included. That is
why there are three different functions.

Date() returns only the date portion.
Now() returns date and time.
Time returns only the time.

No!

Standard SQL has DATE and TIME date types, plus some others, but
Access/Jet has but one temporal data type, named DATETIME (close to
Standard SQL's TIMESTAMP data type but nothing like SQL Server's). The
clue's in the name...

In Access/Jet, Date() always returns a time portion, being midnight;
Time() always returns a date portion, being of 30th of December
eighteen hundred and ninety-nine. You can hide certain date/time
elements using formatting but that doesn't make the underlying values
disappear. This is newbie stuff and you know it

All Date values in Access are not granular to 1 second.

I guess I was summarizing my conclusions based on experience rather
than stating a solid fact; sorry for not being clear. Time is in
continuum, periods can be infinitely divided and in this regard the
floating point nature of Access/Jet's DATETIME functionality is a good
fit; pity it's not practical. Unless prevented, someone can put sub-
second values in a DATETIME column but to operate on it would have to
roll their own temporal functionality (non-trivial to say the least)
because the smallest useable time granule in Access/Jet's own is one
second. If you take the aforementioned prevention approach it gets to
be a pain too, not so bad for table columns where engine-level
validation rules can be used but really onerous for input parameter
values (I shudder to contemplate the consequence if you're a 'dynamic
SQL' person).

Similarly, someone could roll their own fixed point temporal data
type; this is not so fanciful: we see many people here trying to use
text data types to achieve the same and the result usually a horrible
mess.

Of course, most designers use DATETIME, take no action at all but
assume all dates will be of one day granularity, and we see many of
them in the groups wondering out loud why their rows disappear when
JOINed on DATETIME columns and the answer is they allowed time
elements other than midnight and consequently got them.

My conclusions are to use DATETIME for instants, a pair of DATETIMEs
for periods,use engine-level validation rules to ensure DATETIME
columns' values are of known granularity no smaller than one second
(but often larger granules) and either 'round' parameter values to the
same granularity of the column they are being compared while allowing
sub-second values to be implicitly rounded (fine by me: it's not my
fault that DATETIME is not based on fixed point so I shouldn't be
expected to fix it!) I can then used closed-closed representation for
periods e.g. period for the current month would be represented as

[#2007-08-01 00:00:00#, #2007-08-31 23:59:59#]

The OP is also using closed-closed but would represent the same period
as

[#2007-08-01 00:00:00#, #2007-08-31 00:00:00#]

which begs the question, where does the value #2007-08-31 00:00:00#
fall? No where probably.

Now you may ask where does the value 39325.999994213 fall in my
version and I tell you that 39325.999994213 is not a valid DATETIME
value (!!) and if the front end designer or user allowed it to be used
as if it were on then that's their problem (of course I could ensure
the values were always rounded but it would result in a complex and
less flexible system -- and I repeat I shouldn't be expected to fix
the DATETIME data type's floating point 'problem'). I don't think the
OP could use the same defence because valid DATETIME values other than
midnight are far more commonly encountered and there no real excuse
for not anticipating them e.g. as a I suggested they could make their
end DATETIMEs have time element one second before midnight OR use
closed-open representation e.g.

[#2007-08-01 00:00:00#, #2007-09-01 00:00:00#)

and how hard is that g?!

What is not clear here is which date to use for the week. There are two
dates, but the OP did not say which should be used to return the week. In
the example, the start date is week 1, but the end date is certainly not.

Additionally, is there any reason the DatePart function can't be used to
determine the week rather than having to maintain a table? It would be
faster.

=DatePart("ww",SomeDate)

Is there any reason the DatePart function can't be used? You seem to
have answered your own question: using DatePart is fair enough when
your definition of week start date and week end date happens to
coincide with Microsoft's; if not, you've got to roll your own.

SQL (the language) was designed for data storage and data retrieval
and not surprisingly it excels in this area. It was not designed as a
calculation engine, hence doesn't do so well in this area. It stands
to reason to prefer a data-driven solution, using data stored in
permanent tables joining to other tables etc, over a calculation.
Putting the logic into a UDF, for example, obscures the logic of the
application and can only run inside the Access user interface, whereas
a calendar table style solution is clear and available to all. It also
has a many uses e.g. finding the number of enterprise days between two
dates; identifying overlapping periods (JOIN to the calendar table and
GROUP BY calendar day), etc.

Maintenance isn't usually a problem because the calendar tends to be
quite stable g! Also consider it's easier to change data in a table
than it is to make a code change to a UDF. Further, the table-driven
approach ports well to other SQL DBMSs.

Jamie.

--

hi,

ashg657 wrote:
DatePart could not be used as our company calendar runs from October -
September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have
decided that this seperate table is necessary - correct me if I am wrong.
I would encapsulate this in a function. Cause it is easier to maintain.
Just take a look at the common week number algorithms and use it for
your definition of a year.



mfG
-- stefan --

On 22 Aug, 09:34, Stefan Hoffmann wrote:
DatePart could not be used as our company calendar runs from October -
September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have
decided that this seperate table is necessary - correct me if I am wrong.

I would encapsulate this in a function. Cause it is easier to maintain.
Just take a look at the common week number algorithms and use it for
your definition of a year.

Funny, I find it easier to change data in a table (or even alter a
database object) in the database than to roll out a code change
(certainly costs us less money!) Consider a FE/BE split: is it easier
to change data in the BE or change *all* the FEs?

I use the Calendar table approach and it works well.

Do you think it could be the case that you find it easier to write
procedural code than to devise a set-based, table driven approach?

Jamie.

--

hi Jamie,

Jamie Collins wrote:
I would encapsulate this in a function. Cause it is easier to maintain.
Just take a look at the common week number algorithms and use it for
your definition of a year.
Funny, I find it easier to change data in a table (or even alter a
database object) in the database than to roll out a code change
(certainly costs us less money!) Consider a FE/BE split: is it easier
to change data in the BE or change *all* the FEs?
The distribution is not a real argument, as we are in the phase of
creating (designing and implementing) an application.

I use the Calendar table approach and it works well.
When this kind of week number calculation is needed only once, you may
be right.

But i suppose this is not true. Just consider report printing with week
numbers. This would mean you have to use a bunch of these joins just for
a simple week number in the page footer/header. Okay, you may use a
DLookup() replacement, but I think this quite equivalent to a join.

Do you think it could be the case that you find it easier to write
procedural code than to devise a set-based, table driven approach?
This kind of functionality needs a larger degree of abstraction. This is
covered by my functional approach.

Just my 2 cents.


mfG
-- stefan --

On 22 Aug, 10:56, Stefan Hoffmann wrote:
I would encapsulate this in a function. Cause it is easier to maintain.

Funny, I find it easier to change data in a table (or even alter a
database object) in the database than to roll out a code change
(certainly costs us less money!) Consider a FE/BE split: is it easier
to change data in the BE or change *all* the FEs?

The distribution is not a real argument, as we are in the phase of
creating (designing and implementing) an application.

I was responding to your point about ease of maintenance.

I use the Calendar table approach and it works well.

When this kind of week number calculation is needed only once, you may
be right.

But i suppose this is not true. Just consider report printing with week
numbers. This would mean you have to use a bunch of these joins just for
a simple week number in the page footer/header.

My way involves retieving data, not a calculation. If I want to use a
retirieved value multiple times I will cache it (e.g. disconnection
recordset) rather than make multiple database round trips.

I use a proferssional 'industrial strength' report writer myself and
I'm pretty sure it uses a locally cached resultset and hits the
database just the once.

Despite the non-applicable example you picked, I really don't see the
problem. If a table JOIN was an issue then we'd all have very
denormalized databases g. And if looking up a value in a table was
an issue we'd all be stuffed vbg!

Jamie.

--