Date: 12/30/05 (SQL Server)    Keywords: sql

1. In query nalyzer, type either of the following and run it:
PRINT 365/183
PRINT CAST(365/183 AS float)

If your answer is 1 (or 1.0), congratulations, you win. You get to tell me what switch we both need to flip to get the correct (mathematically, anyway) answer.

* * *

2. I'm porting a horribly-written Access application to SQL Server. It contains a table with three columns. Each of these columns corresponds to a dropdown box on a form. The way it's supposed to be written is that a value selected in column A will populate the list in column B, etc., since only certain values in column B should be present for any given value in column A. The monkeywrench in the works is that the values in B are not necessarily unique to the values in A, and the values in C are definitely not unique to the values in B-- but not all values in B are applicable to all values of A, and all values in C are not etc. As an example:

A: Inpatient, Outpatient, Other
B: Transplant, Maternity, Addiction Treatment
C: Bone Marrow Transplant Allogenic, Caesarian Section, Emergency Room

It's relatively obvious that Outpatient in A will never correspond to Transplant in B; Transplant will always, short of a medical miracle, be an inpatient procedure. Addiction Treatment, though, can be on either an inpatient or outpatient basis. (I'm lobbying for getting rid of "other" altogether.) Similarly, Bone Marrow Transplant Allergenic will, good lord willin' and cricks don't rise, never apply to maternity-- but Emergency Room could concievably apply to just about anything in either column A or column B, because you can show up at the ER needing, well, anything.

What I've been mulling over is having three different lookup tables, each with two columns-- ID and Description-- and a fourth column that would list the valid IDs that can go together for each. That, however, ultimately saves me not much room, will require manual additions to the core table whenever new categories are added, and would probably end up being slower. (Actually, anything will end up being slower, seeing as the Access app takes the Chinese-menu method-- display anything and let the user pick whatever-- but I'd like to cut the speed decrease as much as possible.) We're still in the brainstorming phase with this, so there aren't any ideas which will undercut work that's already been done. If you have any to throw into the ring, I'd appreciate it.

Thanks!

Source: http://community.livejournal.com/sqlserver/38791.html