January 2008 - Posts
Sometimes we have objects which have timespans in our database, such as valid dates for coupons, or say active dates of a sale, or trigger, or some other event. in searching through these, we may want to give a user the ability to see all of these objects which were 'active', or whose timespan intersects a user's search criteria. So the user gives a set of dates, and we want to see if any date inside the user's date at all also exists inside the date that the object in our DB was active. The following pseudo-code should hash out how I solved this problem.
(userselected_start_date <= dbobject_end_date AND userselected_end_date >= dbobject_start_date )
(userselected_start_date <= dbobject_start_date AND userselected_end_date >= dbobject_end_date)
((userselected_start_date <= dbobject_end_date AND userselected_start_date >= dbobject_start_date) OR
(userselected_end_date >= dbobject_start_date AND userselected_end_date <= dbobject_end_date))
These three OR statements essentially fill the three criteria about the user's timespan and how it relates to the timespan in the database. There are four possibilities for the set theory relationship between the date ranges.
- The date ranges share no common dates. This means the sets are disjoint, and in the above where clause, the rows would not be returned
- The date range the user selects is a superset of the database range
- The date range the user selects is a subset of the database range
- The date range the user selects has some members, but not all members, which are also found in the date range in our database
This is a bit obscure, but in writing web reports this has come up a few times. Hope it helps someone.
Ruud Campsteijn points out that it is much easier to check if dates don't intersect, and invert the results.
AND NOT (daterange1_startdate > daterange2_end_date OR daterange1_endate < daterange2_startdate)
So I'm working on a small SQL Server 2005 Analysis Services Project in Visual Studio 2005. Anyone who's ever used this tool knows that its less than forgiving if you have foreign key issues. Namely, if you have a table with a foreign key , and the associated key ID doesn't exist in the other table, you'll get an error. So I have not only a data pull into a warehouse, but a second set of scripts which normalize any rouge data / orphaned IDs.
However, after I thought I fixed all the 'gotchas', I was still getting errors that said that the DB was unable to find the associated key. After much soul searching, I saw the key it was referring to was in the same table? how can a Primary key be looked at as a foreign key? Later I found that the answer was, re-process the entire solution, not just the specific cube. Turns out, if you make dimension changes and just try to process the cube, the cube won't process some of the time, depending on the change. So now, even though its more time consuming, no matter what, I reprocess my entire solution to make sure I don't run into these little inconsistencies.
I am writing a Business Intelligence Project and I found I needed to be able to warehouse data from a MySQL Server. Rather than spend tons of money for a DB conversion software kit, I decided to use a linked server to communicate between the two. Luckily, a generous blogger at Windows Live Spaces provided me with all the steps, pretty much. Thanks Dinesh!
Also, please note, he is wrong on a few items. You don't need to use OPENQUERY, at least not for SQL Server 2005. You just need to use the triple-dot notation, AKA
SELECT * FROM LINKEDSERVER...tblname
And I wasn't able to get the generic DSN method working. I created a local SYSTEM DSN first, then just put the system DSN name in the appropriate linked server slot.
So I recently updated my XNA project to 2.0 to take advantage of the cool networking libraries and such. However, now I've noticed that game components I add to the component list no longer automatically begin calling their draw methods once they're added. Perhaps previously I was implementing DrawableGameComponents wrong, but I was under the assumption that once you add them to the components list, they automagically draw themselves. They did in 1.0, and now in 2.0 I have to manually call their .Draw method. Maybe the XNA community can help out with this?
UPDATE: This article by a member of the XNA team may shed light on my problem. there's a property of a DrawableGameComponent called 'Visible'... it may have been set default true in 1.0 and 2.0 its no longer default enabled.