February 3, 2009 at 12:03 pm
I call this the Status/State problem but that's because it fits the industry we (my company) deals in but it may not be appropriate for other industries so I'm trying to locate what the most commonly (or at least more commonly used) term is for describing this so I can refer to it correctly when posting help requests about it and without going into a complex description each time.
NOte there is no DDL because I can't give that kind of info do to the NDA we have and so I've tried to give an example scenario of this kind of programming problem.
There are several scenarios for this and so this is just one example:
A real estate company has a custom DB they use to store their listings. Among many other tables they have a table called HOUSE that stores basic info about a house like it's MLS#, address, the oustide color, bathroom count, Status (i.e. Avaiable, Pending COntract, Sold, ect) and so on.
The next table is called HOUSE_HISTORY which stores an entry for each time the house is shown, someone puts money down to hold it or when it sales and so on.
If I want to create a query that will show the status of the House as of right now I can just query the field in the HOUSE table that stores its status. If however I need to see the status (or state) of the house at a point in time then I have to use the HOUSE_HISTORY table which includes a field that stores the date that the event occurred.
The HOUSE_EVENT table has an entry for every time something happens and not necessarily for every day of the year of every year so I can't simply query the table for an entry on a specific date. ANd because its possible more then one event could occur the same day for the same house I can't assume the first entry returned from HOUSE_HISTORY reflects the houses status as of that day.
I could use Aggregate functions to return the MAX date that is less then or equal to the date I'm searching on but if I need to see the range of dates that a status spans (i.e. how long has the status been 'Pedning Contract') then I need more then just the Max item.
The Order-Details term is often used to refer to a 1-to-many relationship and the Employee-Supervisor term is often used to refer to a table with self-joins, wher an entry in the table can be both an Employee and a supervisor, but what term or description, if any, is used to refer to this kidn of Date-State problem?
Thanks
Kindest Regards,
Just say No to Facebook!February 3, 2009 at 12:09 pm
Here is a quick question before i try answering your question. Is the most recent record in the HOUSE_HISTORY table considered current where as the other records are historical for each HOUSE?
February 3, 2009 at 12:17 pm
i think your HOUSE_EVENT table is going to have just certain statuses that are relevent to the current status of a property...many can be ignored i assume?
correct me if i'm wrong, but the default status of a house would be "OPEN" or "SELLABLE" or "FOR SALE" or whatever the default condition is...
would the HOUSE_EVENT table have events for DEPOSIT TAKEN,DEPOSIT RETURNED,PENDING SALE ,CONTRACT ACCEPTED,SOLD, REMOVED FROM MLS, and that kind of stuff?
the other items in the table like whether it was show or not isn't really relevant, so you just need a sub select of certains statuses to determine whether the default status is overridden, right?
from that sub set of data, i think you can pull out what you need..max status where status code in(certain allowed statusus) might need some details on how
HOUSE_EVENT is structured to help further.
Lowell
February 3, 2009 at 12:20 pm
I think the term you are looking for is EffectiveDate. In the case of your example this should also include the time as well, so perhaps EffectiveDateTime would be a more inclusive term for this example.
February 3, 2009 at 12:24 pm
I used to work at an insurance company and we had a similar requirement. We used two columns, an effective date and an end date. So when querying, we had to evaluate if the date passed in was between those dates. Probably not the most elegant design, but it worked for us.
Greg
February 3, 2009 at 12:27 pm
G² (2/3/2009)
I used to work at an insurance company and we had a similar requirement. We used two columns, an effective date and an end date. So when querying, we had to evaluate if the date passed in was between those dates. Probably not the most elegant design, but it worked for us.Greg
I use a similiar scheme in my ODS database, but I use the term AsOf reporting when pulling data based on a specific date.
February 3, 2009 at 12:29 pm
[font="Verdana"]Here's how I'd do it:
Use a Common Table Expression (CTE) or in-live view to calculate the "house id" and maximum date for each "house id" (i.e. the last status entry). You can include any necessary filtering you need to here (i.e. to exclude certain statuses.)
You can then join between the CTE and the two base tables. I'd do left outer join so deal with the issue of a newly listed house that hasn't yet had any state records.
So psuedo-SQL would look like:
with
LatestHouseState as (
select HouseID, max(StateAt) as StateAt
from HouseHistory
group by HouseID
)
select h.*, hh.*
from House h
left join (
HouseHistory hh
inner join LatestHouseState lhs
on hh.HouseID = lhs.HouseID and hh.StateAt = lhs.StateAt
)
on h.HouseID = hh.HouseID
[/font]
February 3, 2009 at 12:35 pm
WOW!
I just went to get a drink after posting and by the time I get back there are already a half dozen replies.
From the replies so far I'd say Lynn's 'EffectiveDate' is probably the best way to summarize this kind of predicament into a small term.
Greg's (G2) answer about using a Start & Stop/End date-time is a great way to do this but the data we are dealing with does not include a start & stop date-time but only an As of date-time for an event that can change the status of the object in question.
I'm gonna see if we can take Bruce's suggestion on using a CTE and incorporate that into our specific data environment.
Thanks to all for replying so quickly. I was certain it would be probably tomorrow before I had something to move forward with!
Kindest Regards,
Just say No to Facebook!Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply