Need help with SELECT statement. 04-19-10

Hi,

I need some help with the select statement given the business rules and 
desired result below.
Thank you so much for your help in advance.  Please see the business rules 
below.

IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL
  DROP TABLE #Office

CREATE TABLE #Office
(
  CompanyId  INT   NULL,
  OfficeId   INT   NULL,
  CorpOffice BIT   NULL,
  DBAName    VARCHAR(15)  NULL
)
go

INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1')
INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2')
INSERT INTO #Office VALUES (1323, 1306, 0, 'Test3')

INSERT INTO #Office VALUES (1324, 1304, 1, 'Test3')
INSERT INTO #Office VALUES (1324, 1305, 0, 'Test4')

INSERT INTO #Office VALUES (1325, 1307, 0, 'Test5')
INSERT INTO #Office VALUES (1328, 1309, 1, 'Test6')
GO

    SELECT *
      FROM #Office;
    go
    
CompanyId   OfficeId    CorpOffice DBAName
----------- ----------- ---------- ---------------
1323        1302        0          Test1
1323        1303        0          Test2
1323        1306        0          Test3

1324        1304        1          Test3
1324        1305        0          Test4

1325        1307        0          Test5

1328        1309        1          Test6    

/*
-- Business Rules: 1. Only one CompanyId and CorpOffice = 0 Return
                   2. Multiple CompanyId and CorpOffice = 1 in one of them 
Return
                   3. Multiple CompanyId and CorpOffice = 0 Don't return.
*/

-- desired results:
CompanyId   OfficeId    CorpOffice DBAName
----------- ----------- ---------- ---------------
1324        1304        1          Test3
1324        1305        0          Test4

1325        1307        0          Test5
0
Utf
4/19/2010 6:27:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

15 Replies
946 Views

Similar Articles

[PageSpeed] 59

Tables have to have keys; keys cannot be NULL; therefore you never
posted a proper table!  The DUNS is the industry stadnard identifier
for companies, but let's ignore that. SQL programmers do not like
assembly language BIT flags; we prefer an encoding that can be
extended or which has an industry standard.

CREATE TABLE Offices
(company_id INTEGER NOT NULL,
 office_id INTEGER NOT NULL,
 PRIMARY KEY (company_id, office_id)
 office_type CHAR(1) DEFAULT 'N' NOT NULL
    CHECK (office_type IN ('C', 'N', 'B', ..)),
 dba_name VARCHAR(15) NOT NULL
);

SQL Server can now use ANSI/ISO Standard Syntax, thre is no need for
dialect:

INSERT INTO Offices
VALUES (1323, 1302, 0, 'Test1')
 (1323, 1303, 0, 'Test2'),
 (1323, 1306, 0, 'Test3'),
 (1324, 1304, 1, 'Test3'),
 (1324, 1305, 0, 'Test4'),
 (1325, 1307, 0, 'Test5'),
 (1328, 1309, 1, 'Test6');

>> 1. Only one (company_id and office_type = 'N') Return
 2. Multiple (company_id and office_type = 'C') in one of them
Return
 3. Multiple (company_id and office_type = 'N') Don't return. <<
SELECT company_id
  FROM Offices
GROUP BY company_id
HAVING COUNT(*) = 1 AND MIN(office_type)= 'N'
   OR COUNT(*) > 1 AND MIN(office_type)= 'C';
0
CELKO
4/19/2010 8:37:52 PM
SELECT o.CompanyId, Max(o.OfficeId) As OfficeID,
  CAST(MAX(CAST(o.CorpOffice AS int)) AS bit) AS CorpOffice,
  MAX(o.DBAName) AS DBAName
FROM #Office o
GROUP BY o.CompanyId
HAVING COUNT(*) = 1 AND MAX(CAST(o.CorpOffice AS int)) = 0
UNION ALL
SELECT o1.CompanyId, o1.OfficeId, o1.CorpOffice, o1.DBAName
FROM #Office o1
WHERE EXISTS (SELECT o2.CompanyID
  FROM #Office o2
  WHERE o1.CompanyId = o2.CompanyId
  GROUP BY o2.CompanyId
  HAVING MAX(CAST(o2.CorpOffice AS int)) <> MIN(CAST(o2.CorpOffice AS int)))
ORDER BY CompanyId, OfficeId;

Tom

"LN" <LN@discussions.microsoft.com> wrote in message 
news:3B3AA72A-A47F-429C-8D4F-0C0FBAFB7007@microsoft.com...
> Hi,
>
> I need some help with the select statement given the business rules and
> desired result below.
> Thank you so much for your help in advance.  Please see the business rules
> below.
>
> IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL
>  DROP TABLE #Office
>
> CREATE TABLE #Office
> (
>  CompanyId  INT   NULL,
>  OfficeId   INT   NULL,
>  CorpOffice BIT   NULL,
>  DBAName    VARCHAR(15)  NULL
> )
> go
>
> INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1')
> INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2')
> INSERT INTO #Office VALUES (1323, 1306, 0, 'Test3')
>
> INSERT INTO #Office VALUES (1324, 1304, 1, 'Test3')
> INSERT INTO #Office VALUES (1324, 1305, 0, 'Test4')
>
> INSERT INTO #Office VALUES (1325, 1307, 0, 'Test5')
> INSERT INTO #Office VALUES (1328, 1309, 1, 'Test6')
> GO
>
>    SELECT *
>      FROM #Office;
>    go
>
> CompanyId   OfficeId    CorpOffice DBAName
> ----------- ----------- ---------- ---------------
> 1323        1302        0          Test1
> 1323        1303        0          Test2
> 1323        1306        0          Test3
>
> 1324        1304        1          Test3
> 1324        1305        0          Test4
>
> 1325        1307        0          Test5
>
> 1328        1309        1          Test6
>
> /*
> -- Business Rules: 1. Only one CompanyId and CorpOffice = 0 Return
>                   2. Multiple CompanyId and CorpOffice = 1 in one of them
> Return
>                   3. Multiple CompanyId and CorpOffice = 0 Don't return.
> */
>
> -- desired results:
> CompanyId   OfficeId    CorpOffice DBAName
> ----------- ----------- ---------- ---------------
> 1324        1304        1          Test3
> 1324        1305        0          Test4
>
> 1325        1307        0          Test5 

0
Tom
4/19/2010 11:53:54 PM
Thank you Tom.  Now, try to understand your query.



"Tom Cooper" wrote:

> SELECT o.CompanyId, Max(o.OfficeId) As OfficeID,
>   CAST(MAX(CAST(o.CorpOffice AS int)) AS bit) AS CorpOffice,
>   MAX(o.DBAName) AS DBAName
> FROM #Office o
> GROUP BY o.CompanyId
> HAVING COUNT(*) = 1 AND MAX(CAST(o.CorpOffice AS int)) = 0
> UNION ALL
> SELECT o1.CompanyId, o1.OfficeId, o1.CorpOffice, o1.DBAName
> FROM #Office o1
> WHERE EXISTS (SELECT o2.CompanyID
>   FROM #Office o2
>   WHERE o1.CompanyId = o2.CompanyId
>   GROUP BY o2.CompanyId
>   HAVING MAX(CAST(o2.CorpOffice AS int)) <> MIN(CAST(o2.CorpOffice AS int)))
> ORDER BY CompanyId, OfficeId;
> 
> Tom
> 
> "LN" <LN@discussions.microsoft.com> wrote in message 
> news:3B3AA72A-A47F-429C-8D4F-0C0FBAFB7007@microsoft.com...
> > Hi,
> >
> > I need some help with the select statement given the business rules and
> > desired result below.
> > Thank you so much for your help in advance.  Please see the business rules
> > below.
> >
> > IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL
> >  DROP TABLE #Office
> >
> > CREATE TABLE #Office
> > (
> >  CompanyId  INT   NULL,
> >  OfficeId   INT   NULL,
> >  CorpOffice BIT   NULL,
> >  DBAName    VARCHAR(15)  NULL
> > )
> > go
> >
> > INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1')
> > INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2')
> > INSERT INTO #Office VALUES (1323, 1306, 0, 'Test3')
> >
> > INSERT INTO #Office VALUES (1324, 1304, 1, 'Test3')
> > INSERT INTO #Office VALUES (1324, 1305, 0, 'Test4')
> >
> > INSERT INTO #Office VALUES (1325, 1307, 0, 'Test5')
> > INSERT INTO #Office VALUES (1328, 1309, 1, 'Test6')
> > GO
> >
> >    SELECT *
> >      FROM #Office;
> >    go
> >
> > CompanyId   OfficeId    CorpOffice DBAName
> > ----------- ----------- ---------- ---------------
> > 1323        1302        0          Test1
> > 1323        1303        0          Test2
> > 1323        1306        0          Test3
> >
> > 1324        1304        1          Test3
> > 1324        1305        0          Test4
> >
> > 1325        1307        0          Test5
> >
> > 1328        1309        1          Test6
> >
> > /*
> > -- Business Rules: 1. Only one CompanyId and CorpOffice = 0 Return
> >                   2. Multiple CompanyId and CorpOffice = 1 in one of them
> > Return
> >                   3. Multiple CompanyId and CorpOffice = 0 Don't return.
> > */
> >
> > -- desired results:
> > CompanyId   OfficeId    CorpOffice DBAName
> > ----------- ----------- ---------- ---------------
> > 1324        1304        1          Test3
> > 1324        1305        0          Test4
> >
> > 1325        1307        0          Test5 
> 
> .
> 
0
Utf
4/20/2010 3:34:01 AM
The query has two parts.  The first part

SELECT o.CompanyId, Max(o.OfficeId) As OfficeID,
  CAST(MAX(CAST(o.CorpOffice AS int)) AS bit) AS CorpOffice,
  MAX(o.DBAName) AS DBAName
FROM #Office o
GROUP BY o.CompanyId
HAVING COUNT(*) = 1 AND MAX(CAST(o.CorpOffice AS int)) = 0

get the rows for companies that have only one row (because of the group by 
and having count(*) = 1).  Since we are grouping by CompanyId, so we can't 
select, for example, OfficeId, directly, we have to do some aggregate 
function, but since there is only one row, Max(OfficeId) is the same as 
OfficeId.  Same for the other fields.  CorpOffice must be CAST to an int 
because MAX of a bit is not allowed and then cast back to a bit to make the 
datatype of the CorpOffice column be correct.  So we have the rows for 
companies that have only one row.  But we only want those rows that have 
CorpOffice = 0, so that is the other part of the HAVING clause (AND 
MAX(CAST(o.CorpOffice AS int)) = 0).  So this part gets the companies that 
have only one row and that row has a CorpOffice = 0.

To that we UNION ALL

SELECT o1.CompanyId, o1.OfficeId, o1.CorpOffice, o1.DBAName
FROM #Office o1
WHERE EXISTS (SELECT o2.CompanyID
  FROM #Office o2
  WHERE o1.CompanyId = o2.CompanyId
  GROUP BY o2.CompanyId
  HAVING MAX(CAST(o2.CorpOffice AS int)) <> MIN(CAST(o2.CorpOffice AS int)))
ORDER BY CompanyId, OfficeId;

The second part gets all rows where the company has at least two rows in the 
table and those rows have different values in CorpOffice (that is the max 
value is not equal to the min value).  Since the CorpOffice is a bit column, 
that means at least one of the rows must have CorpOffice = 0 and at least 
one must have CorpOffice = 1.

Tom
"LN" <LN@discussions.microsoft.com> wrote in message 
news:478A41FF-B983-4BBE-BC06-F6C3612C06C1@microsoft.com...
> Thank you Tom.  Now, try to understand your query.
>
>
>
> "Tom Cooper" wrote:
>
>> SELECT o.CompanyId, Max(o.OfficeId) As OfficeID,
>>   CAST(MAX(CAST(o.CorpOffice AS int)) AS bit) AS CorpOffice,
>>   MAX(o.DBAName) AS DBAName
>> FROM #Office o
>> GROUP BY o.CompanyId
>> HAVING COUNT(*) = 1 AND MAX(CAST(o.CorpOffice AS int)) = 0
>> UNION ALL
>> SELECT o1.CompanyId, o1.OfficeId, o1.CorpOffice, o1.DBAName
>> FROM #Office o1
>> WHERE EXISTS (SELECT o2.CompanyID
>>   FROM #Office o2
>>   WHERE o1.CompanyId = o2.CompanyId
>>   GROUP BY o2.CompanyId
>>   HAVING MAX(CAST(o2.CorpOffice AS int)) <> MIN(CAST(o2.CorpOffice AS 
>> int)))
>> ORDER BY CompanyId, OfficeId;
>>
>> Tom
>>
>> "LN" <LN@discussions.microsoft.com> wrote in message
>> news:3B3AA72A-A47F-429C-8D4F-0C0FBAFB7007@microsoft.com...
>> > Hi,
>> >
>> > I need some help with the select statement given the business rules and
>> > desired result below.
>> > Thank you so much for your help in advance.  Please see the business 
>> > rules
>> > below.
>> >
>> > IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL
>> >  DROP TABLE #Office
>> >
>> > CREATE TABLE #Office
>> > (
>> >  CompanyId  INT   NULL,
>> >  OfficeId   INT   NULL,
>> >  CorpOffice BIT   NULL,
>> >  DBAName    VARCHAR(15)  NULL
>> > )
>> > go
>> >
>> > INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1')
>> > INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2')
>> > INSERT INTO #Office VALUES (1323, 1306, 0, 'Test3')
>> >
>> > INSERT INTO #Office VALUES (1324, 1304, 1, 'Test3')
>> > INSERT INTO #Office VALUES (1324, 1305, 0, 'Test4')
>> >
>> > INSERT INTO #Office VALUES (1325, 1307, 0, 'Test5')
>> > INSERT INTO #Office VALUES (1328, 1309, 1, 'Test6')
>> > GO
>> >
>> >    SELECT *
>> >      FROM #Office;
>> >    go
>> >
>> > CompanyId   OfficeId    CorpOffice DBAName
>> > ----------- ----------- ---------- ---------------
>> > 1323        1302        0          Test1
>> > 1323        1303        0          Test2
>> > 1323        1306        0          Test3
>> >
>> > 1324        1304        1          Test3
>> > 1324        1305        0          Test4
>> >
>> > 1325        1307        0          Test5
>> >
>> > 1328        1309        1          Test6
>> >
>> > /*
>> > -- Business Rules: 1. Only one CompanyId and CorpOffice = 0 Return
>> >                   2. Multiple CompanyId and CorpOffice = 1 in one of 
>> > them
>> > Return
>> >                   3. Multiple CompanyId and CorpOffice = 0 Don't 
>> > return.
>> > */
>> >
>> > -- desired results:
>> > CompanyId   OfficeId    CorpOffice DBAName
>> > ----------- ----------- ---------- ---------------
>> > 1324        1304        1          Test3
>> > 1324        1305        0          Test4
>> >
>> > 1325        1307        0          Test5
>>
>> .
>> 

0
Tom
4/20/2010 4:44:15 AM
On Mon, 19 Apr 2010 13:37:52 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>Tables have to have keys; keys cannot be NULL; therefore you never
>posted a proper table!  The DUNS is the industry stadnard identifier
>for companies, but let's ignore that. 

Ignoring it is a good plan, DUNS is voluntary opt in, and is therefore
a particularly terrible key. not all companies have a DUNS number,
therefore it cannot be used as a unique key for all companies.... 



>SQL programmers do not like
>assembly language BIT flags; we prefer an encoding that can be
>extended or which has an industry standard.
>

sql PROGRAMMERS do what works best. Bit flags are sometimes
appropriate (as per recording the yes/no answers from a user
preferences form)



0
Iain
4/22/2010 11:59:37 AM
>> Ignoring it is a good plan, DUNS is voluntary opt in, and is therefore a particularly terrible key. not all companies have a DUNS number, therefore it cannot be used as a unique key for all companies....  <<

I talked to the D&B people a few weeks ago to clean up my personal
DUNS. They only have 160 MILLION companies on file in the US; the
representative did not have the total world figures. Since it is
always nine digits, I can verify it with a regular Expression. Since I
can log on to Internet and go to the D&B website, Anyone can validate
it. The cost of getting a DUNS is nothing -- you just apply for it.
Adding credit reports and other information will cost you, if you want
the whole package; as a single consultant, I only use the number.

Companies that do business with the US Federal Government (a fairly
large organization) are required to have one. I believe the same is
true for the UN but I don't know about the EU. I got one because DELL
computers requires one to pay anyone. None of my clients have found
any company or proprietorship with whom they do business not to have a
DUNS.

Yes, it is voluntary; so are ZIP codes :) Would you like to make the
argument that they are also "a particularly terrible key" for
mailings?  As I understand it, in the UK, you have to buy Postal Code
data from the Royal Post and it is illegal to publish it privately;
correct me if I am wrong. That would make a DUNS cheaper and more
universal than a British postal code!

The other candidate is usually some tax id number. Unfortunately,
there are privacy and data theft problems in exposing it. And was that
the Federal, State, local or Foreign tax id number?

Since it is "a particularly terrible key", you must have a better
one.

What is it?  Would you mind filling out one of my little forms I use
when I design a database for a client?  It is part of a data
dictionary description of a data element:

1) Validation: how do you know that a value can be in this domain?
Regular expression? fixed length? check digit? etc.

2) Verification: how do you know that a value is actually used?
External authority? Internal source? etc.

3) Scope of understanding: is the data element understood only to me
and one database? within the company? within a particular industry?
Universally?

4) Maintenance: how is the data element maintained? External
authority? Internal authority? Anyone can change it?

>> BIT flags are sometimes appropriate (as per recording the yes/no answers from a user preferences form) <<

As a working statistician for a number of years, that is not a good
example. To process a survey you need Yes/No shown to the subject. But
internally, you also have "Did not answer", "Was not asked" and
"Contradicts another answer" for the math to work.

Also, that is not a preference scale (the term in the trade is Likert
scale. http://en.wikipedia.org/wiki/Likert_scale). They are is best
done with five rankings.


0
CELKO
4/22/2010 4:29:42 PM
On Thu, 22 Apr 2010 09:29:42 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>
>Companies that do business with the US Federal Government (a fairly
>large organization) are required to have one. I believe the same is
>true for the UN but I don't know about the EU. I got one because DELL
>computers requires one to pay anyone. None of my clients have found
>any company or proprietorship with whom they do business not to have a
>DUNS.
>

But my customers do not require their customers to hae a DUNS. This
would preclude sales to private homeowners, and mak esales ot many of
their customers who either don't know, or don't have a DUNS number for
their company. 

So I should make my customer's life difficult and thus promote
migration to another software which "Doesn't insist on bloody stupid
answers", to satisfy your sense of propriety? 


>Yes, it is voluntary; so are ZIP codes :) Would you like to make the
>argument that they are also "a particularly terrible key" for
>mailings?  As I understand it, in the UK, you have to buy Postal Code
>data from the Royal Post and it is illegal to publish it privately;
>correct me if I am wrong. That would make a DUNS cheaper and more
>universal than a British postal code!

The postal code is a LOUSY key, (might be a good index) because it
doesn't even have the advantage of uniqeness. 

Most of your 'Industry standard' primary key examples seem to either
lack the evasive quality of uniqeness, or the even more evasive
quality of pervasiveness. In that every single example I can remember
you using to replace someone elses 'ID' has either not comprehensively
covered all cases of the entity required, or has not uniqely identifed
the entity required. 


>Since it is "a particularly terrible key", you must have a better
>one.



No, my single overriding statement here is that until you can find a
comprehensive, instantly available, and verifiable UNIQUE identifier
for your entity, there is no 'Natural' primary key. And the set of
comprehensive, instantly available, verifiable unique identifiers is
really very very small. 

So, in order to prevent storing dummy, incorrect, data in the database
one should avoid using them as primary keys. 

To answer your comment. For a company, within a single application,
excluding the nicely available IDENTITY data type? The account number
in the accounts ledgers. This is typically 6-8 characters long, and
validated by regexp against XXXX99 XXX999 or similar. 

By all means index by DUNS, that way, if you can get anybody to bother
to tell you what it is, you can use it. But it's not the primary key,
by a looooong chalk. 


All of your examples boil down to having to find out someone else's ID
number. 

EAN  = national, comeany etc identifiers, + company's own internal ID
+ check digit. 

ISBN - Same deal 

VIN - Same Deal

SSN - Same deal. 

They are all pointers to an ID number in someone else's database. They
cost time and effort to determine by the user, and are generally
useless in terms of memorability or ease of entry (Univerally unique
identifiers must, of necessity, be LONG streams of input data) (More
than 8 is long.... ).  So you're going to have to have another, user
assigned, shorter, easier, unique code for each element. 

They cannot (generally) be used as sort criteria, reporting
restriction ranges etc, etc. 

So their main purpose in life is to be the foreign key in a relational
database. You're not going to let the user enter that number 'raw',
it'll be from a lookup of some sort. 

>
>>> BIT flags are sometimes appropriate (as per recording the yes/no answers from a user preferences form) <<
>
>As a working statistician for a number of years, that is not a good
>example. To process a survey you need Yes/No shown to the subject. But
>internally, you also have "Did not answer", "Was not asked" and
>"Contradicts another answer" for the math to work.

"USER PREFERENCES FORM" is not "Survey". It's on/off switches. 

A practical example from my system, in the customer entity, is
"Customer Q.A.?" meaning, should this system treat the customer as
being ISO 900X Quality assured? 
Note, not "Is the customer ISO 900X Q.A.?", but "do they want to be
treated as Q.A.". 

I am guessing that your answer would be to have their ISO QA
registration number in a field and treat null as no. However, there
are customers with no ISO QA number (still attempting to get
registered, or not prepared to put the effort in to get fully
registered) who nevertheless want to be treated as such by my
customer, (provide them with traceability information and supplier
certificates for the material on delivery). 
Further, it is none of my customer's concern what their customers QA
reg number is, and they don't want the hassle of finding it out. (If
you thought steel men were intransigent, you never encountered
Yorkshire Steel Men....).

So, how do you record, in such a way as to affect the behaviour of the
application using a simple statement, this customer's desire to be
treated as a Q.A. organisation? 


Iain

0
Iain
4/23/2010 12:22:28 PM
"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:ab8eb84a-9d2a-44f5-9ebd-b1036f01cffa@c1g2000prn.googlegroups.com...

> Yes, it is voluntary; so are ZIP codes :) Would you like to make the
> argument that they are also "a particularly terrible key" for
> mailings?  As I understand it, in the UK, you have to buy Postal Code
> data from the Royal Post [Royal Mail]* and it is illegal to publish it 
> privately;
> correct me if I am wrong. That would make a DUNS cheaper and more
> universal than a British postal code!

* as you like to correct people on the wrong use of names or terminology

Postcode data can be used in systems, there are many products that allow 
address look ups.  Creation of new ones are purchased from Royal Mail and 
only them.

Postcodes contain approximately 8000 homes in the UK, varies depending on 
location of course.  So for keys its not great, look up for mailing areas 
yes.

> The other candidate is usually some tax id number. Unfortunately,
> there are privacy and data theft problems in exposing it. And was that
> the Federal, State, local or Foreign tax id number?

Again in the UK, you can have this information on screen but there is 
directives according to the Data Protection act on how or where you can 
display this information.  But for a unqiue key here assuming your looking 
at adults, you can use the national insurance number but its use is limited.

>>> BIT flags are sometimes appropriate (as per recording the yes/no answers 
>>> from a user preferences form) <<
>
> As a working statistician for a number of years, that is not a good
> example. To process a survey you need Yes/No shown to the subject. But
> internally, you also have "Did not answer", "Was not asked" and
> "Contradicts another answer" for the math to work.

I have to disagree here CELKO, we use it here in an insurance underwriting 
system to inducate the answers to yes no questions, removes the variations 
that are recorded YES, yes, y, NO, no, No, N,n etc.

As these answer make up part of the insurance paperwork. 


0
Simon
4/23/2010 1:40:39 PM
>> But my customers do not require their customers to have a DUNS. This wou=
ld preclude sales to private homeowners, and make sales to many of their cu=
stomers who either don't know, or don't have a DUNS number for their compan=
y. <<

You are changing the topic; the poster did not care about individuals,
just companies. If you sell a house to a company and you did a credit
check on them, you will find a DUNS. It is that universal.

>> Most of your 'Industry standard' primary key examples seem to either lac=
k the evasive quality of uniqueness, or the even more evasive quality of pe=
rvasiveness.<<

160 Million DUNS numbers is not pervasive enough for you? How much
merchandise have you bought without a bar code on it? Books without an
ISBN?

>> In that every single example I can remember you using to replace someone=
 else 'ID' has either not comprehensively covered all cases of the entity r=
equired, or has not uniquely identified the entity required. <<

Unh? I keep advocating standards that are already in place in their
industry, and very often are required by law.

>> No [better suggestion], my single overriding statement here is that unti=
l you can find a comprehensive, instantly available, and verifiable UNIQUE =
identifier for your entity, there is no 'Natural' primary key. And the set =
of comprehensive, instantly available, verifiable unique identifiers is rea=
lly very very small. <<

Have you ever looked at the ISO catalog? Remember FIDDLER ON THE ROOF?
When some students ask the Rabbi, =93is there a proper blessing for the
Czar?=94, he answers =93There is a proper blessing for everything!=94 to th=
e
students.

If you are willing to Google for a bit, you can find an industry
standard over 95% of the time. I even wrote a book about this (=93Data,
Measurements and Standards in SQL=94; ISBN: 978-0-12-374722-8). Opps! I
gave you the ISBN, so you will never be able to find it now :)

>> To answer your comment. For a company, within a single application, excl=
uding the nicely available IDENTITY data type? The account number in the ac=
counts ledgers. This is typically 6-8 characters long, and validated by reg=
exp against XXXX99 XXX999 or similar. <<

Have you looked at what MAJOR accounting firms use in their ledger
codes? Have you seen what the Feds require? What happens with local
Chart of Accounts is that you spend a lot of time converting to other
Chart of Accounts to talk to the outside world.

There has been talk for decades about a Uniform Chart of Accounts as
part of GAAP, but it is frustrating.

>> All of your examples boil down to having to find out someone else's ID n=
umber. <<

Standard identifiers are **everyone's** id number. That is the whole
point of them. What would your auto insurance say if you announced
that your car had IDENTITY =3D 42 or you call it =93Fred=94 and you want to
put that in the forms instead of the VIN?

>> EAN =3D national, company etc identifiers, + company's own internal ID
+ check digit.
ISBN - Same deal
VIN - Same Deal
SSN - Same deal. <<

SSN has no check digits. It is a lousy data element design, but that
is another topic.

>> They are all pointers to an ID number in someone else's database. They c=
ost time and effort to determine by the user, and are generally useless in =
terms of memorability or ease of entry (Universally unique identifiers must=
, of necessity, be LONG streams of input data) (More than 8 is long.... ). =
So you're going to have to have another, user assigned, shorter, easier, un=
ique code for each element. <<

Unh? They are nothing like a pointer! Hardware? Assembly language? C
programs? Hello?

Do you have a telephone number? In the US it is 10 digits, but somehow
we memorized a lot them (my wife used to know the home phones for
virtually every oncologist in Atlanta when she worked at a hospital).

The fact you can go to an authority to verify a standard code is a
blessing. If you are in that trade, you find them very easy to enter =96
you scan their bar code most of the time and if you do manual entry
you get a check digit to help you. But if you work in a trade, you can
read the parts of the code. When I see ISBN13: 978-0-12-369379-2, I
immediately know that this is a book (978), the book is in English
(0), the publisher is Elsevier (12) and I can verify the check digit
(Luhn code).

Now if I want to find a description of the book, I can go to the
Library of Congress website (and get the DDC and LOC codes, among
other things) or I can go to Amazon.com (and get reviews, suggestions
and sales figures), or any bookstore or library that has it on the
shelf with a search.

>> They cannot (generally) be used as sort criteria, reporting restriction =
ranges etc, etc. <<

You just outlined examples with a hierarchy in their substrings in
left to right order! Isn't that useful for sorting and reporting? For
example, the ISBNs with the prefix =93978-0-12-xxxxxx-x=94, are Elsevier
books. If I know how a publisher assigns their substring, I might be
able to get categories and sub-categories.

>> So their main purpose in life is to be the foreign key in a relational d=
atabase. You're not going to let the user enter that number 'raw', it'll be=
 from a lookup of some sort. <<

Unh? These things are primary keys which are referenced in other
tables in the schema.

>> A practical example from my system, in the customer entity, is "Customer=
 Q.A.?" meaning, should this system treat the customer as being ISO 900X Qu=
ality assured? Note, not "Is the customer ISO 900X Q.A.?", but "do they wan=
t to be treated as Q.A.". <<

You do not validate the customer's claim? That just sounds wrong to
me. No legal problems? Is the risk so low, you can ignore it? You'd
want to see my medical license if you ran a hospital.

>> I am guessing that your answer would be to have their ISO QA registratio=
n number in a field and treat NULL no. However, there are customers with no=
 ISO QA number (still attempting to get registered, or not prepared to put =
the effort in to get fully registered) who nevertheless want to be treated =
as such by my customer, (provide them with traceability information and sup=
plier certificates for the material on delivery). Further, it is none of my=
 customer's concern what their customers QA reg number is, and they don't w=
ant the hassle of finding it out. (If you thought steel men were intransige=
nt, you never encountered Yorkshire Steel Men....). <<

In the US, this would land you in prison or civil court. The legal
term is =93due diligence=94 and you have not done it. Also, claiming ISO
certification allows the frauds to over-charge you.

I don't know the ISO-900x numbering system, but the US patent system
has a =93patent pending=94 status for things that are not out of the
process yet. I would avoid just a NULL and go for a QA_status that has
{unknown, certification, application, not certification}

As an aside, the US Department of Defense has started a UUID type code
for all property worth more than $5000.00 and they will have a central
database.
0
CELKO
4/23/2010 5:36:26 PM
>> Creation of new ones are purchased from Royal Mail and only them. <<

The idea of paying for a postal code is weird to us. That is supposed
to be our tax money at work for us. Every place is different; Italian
law requires that invoice numbers be sequential, for example.

>> Again in the UK, you can have this information on screen but there is di=
rectives according to the Data Protection act on how or where you can displ=
ay this information. But for a unique key here assuming your looking at adu=
lts, you can use the national insurance number but its use is limited. <<

The original post was looking for company identifiers. In the US, we
get crazy about privacy. The Social Security Number used to be our
version of the National Insurance Number and was used everywhere, but
it is disallowed now. I just had a problem ordering one of my college
transcripts. The old student id when I was in grad school was the
SSN.  I had to do extra work, and send a physical form with my
signature on it.  ARRGH!  Medical records are a nightmare now.

>> I have to disagree here CELKO, we use it here in an insurance underwriti=
ng system to indicate the answers to yes no questions, removes the variatio=
ns that are recorded YES, yes, y, NO, no, No, N,n etc. <<

That is one reason I like computer forms =96 no room for =93creative=94
answers and control of what is asked:)

But this is not a survey, which is what we were talking about; it is
data. When you get a "Did not answer" or "Contradicts another answer",
you reject the document until you can get consistent data. The =93was
not asked=94 option is implied by the selection of input screens.

What I hope you have is not codes, but a decision table that has the
accept or reject rules. You probably also avoid non-transitive data.
Ever play 'scissors, paper, stone'? Ask people compare to pairs of
goods and you get this. My favorite is how American thought that a
liter is bigger than a quart, but two liters is less than a half
gallon. Coca Cola had to delay two-liters bottles because of this.
There are three kinds of people; those who can do math and those you
can't :)

If the domain is two-values, then the only problem I have is that you
should not exclude growth. The classic example is sex_code; lots of
places use {'M', 'F'}, but the ISO Standard is {0=3Dunknown, 1=3Dmale,
2=3Dfemale, 9=3Dlawful person}. But rH blood factor is {'-', '+'} and will
not change.

0
CELKO
4/23/2010 5:36:52 PM
On Fri, 23 Apr 2010 10:36:26 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>>> But my customers do not require their customers to have a DUNS. This would preclude sales to private homeowners, and make sales to many of their customers who either don't know, or don't have a DUNS number for their company. <<
>
>You are changing the topic; the poster did not care about individuals,
>just companies. If you sell a house to a company and you did a credit
>check on them, you will find a DUNS. It is that universal.
>

I won't, because I haven't got the time or resources to look it up
when I want to create their account. It has no meaning to me as an
individual, no use in helping me remember or find the record for
later, it has NO POINT in my database save to give a reference to talk
to other databases.  If I DO ever wind up wanting to talk to other
databases, then I might find a use for that data, but it's data, not
key...... 
Generally, on the practical interchanges of data I have done so far,
the big fish in the relationship has insisted that the little fish use
THEIR account reference in the transfer of data, and do you know what?
It's never been the DUNS number.... 

>>> Most of your 'Industry standard' primary key examples seem to either lack the evasive quality of uniqueness, or the even more evasive quality of pervasiveness.<<
>
>160 Million DUNS numbers is not pervasive enough for you? How much
>merchandise have you bought without a bar code on it? Books without an
>ISBN?
>

No it's not. It doesn't manage to cover the subset of "people my
customers' do business with". Therefore, it is not pervasive enough
for me. 

As for ISBNs, about 5% of my personal library does not have an ISBN.
(According to the database I have it stored in... which patently
doesn't use the ISBN to store its records, as otherwise I would be
unable to produce this statistic for you.) 95% data coverage 'good
enough' for you? Because my boss likes it if we can store more than
95% of the data.... 
I admit this is not necessarily a statistically valid sample, as it's
only 3500 books, and it is (of necessity) a subset of 'all books',
skewed towards Science fiction and Fantasy, with the odd delve into
50's Noir, but it's the exceptions which disprove the rule.... 

>>> In that every single example I can remember you using to replace someone else 'ID' has either not comprehensively covered all cases of the entity required, or has not uniquely identified the entity required. <<
>
>Unh? I keep advocating standards that are already in place in their
>industry, and very often are required by law.
>
And yet they fail to be either comprehensive or unique. Probably says
something about 'standards'. I like standards, there are so many to
choose from. 

You jumped down the OP's throat about not having the intelligence to
use the DUNS number to store his customer data by. Not universal, not
required by law. By the way, who's laws? I'm in the U.K., my law is
not your law.... See, not even the law is standard.... 


>>> No [better suggestion], my single overriding statement here is that until you can find a comprehensive, instantly available, and verifiable UNIQUE identifier for your entity, there is no 'Natural' primary key. And the set of comprehensive, instantly available, verifiable unique identifiers is really very very small. <<
>
>Have you ever looked at the ISO catalog? Remember FIDDLER ON THE ROOF?
>When some students ask the Rabbi, �is there a proper blessing for the
>Czar?�, he answers �There is a proper blessing for everything!� to the
>students.
>
>If you are willing to Google for a bit, you can find an industry
>standard over 95% of the time. I even wrote a book about this (�Data,
>Measurements and Standards in SQL�; ISBN: 978-0-12-374722-8). Opps! I
>gave you the ISBN, so you will never be able to find it now :)
>

So Q.E.D.? As yet you have proven unable to give me a single
universally applicable standard for a human being, a customer, or a
piece of steel section. Do some Q, in order to D.... 
And no, the standards you found for steel don't count. As I pointed
out before, they are not unique, they are not comprehensive and (and
this is the important part) they don't describe the section of the
material, just it's composition. 

(Try going to the store and saying you want fruit cake. Will you get a
1 portion, 5 portion or 10 portion cake? Will it be square(ish), or
round, or oblong(ish).)

>>> To answer your comment. For a company, within a single application, excluding the nicely available IDENTITY data type? The account number in the accounts ledgers. This is typically 6-8 characters long, and validated by regexp against XXXX99 XXX999 or similar. <<
>
>Have you looked at what MAJOR accounting firms use in their ledger
>codes? Have you seen what the Feds require? What happens with local
>Chart of Accounts is that you spend a lot of time converting to other
>Chart of Accounts to talk to the outside world.
>

No, what happens with local charts of accounts is you can take them in
the managers office as summary reports and he can understand them.
When you have one accounts clerk, who is not a qualified accountant,
and 5 salespeople creating your new accounts on the fly as the
quotations are requested, no-one cares about the DUNS number. 

We are the customer of many organisations, some really quite large,
our company name is PCI systems Ltd, we're the only one in Sheffield
in the U.K. 
I have no idea what our DUNS number is (assuming we have one). Can you
tell me? While you're off doing that, I'll hang up and go get my steel
from somewhere else which doesn't ask such patently stupid
questions.... 
I've found the U.K. website for D&B, it promises to sell me the
information about my company within 12 hours of the request. If you
went in to Wallmart and they said you could have that milk if you just
hang on 12 hours while we set up your account, and that'll be 5
dollars to cover the costs in so doing, would you go next door where
they don't need you to wait. Because I would.... 

>There has been talk for decades about a Uniform Chart of Accounts as
>part of GAAP, but it is frustrating.
>

Because no-one wants to be bothered to have to follow externally
published lists which have no purpose save to be externally published
lists????

>>> All of your examples boil down to having to find out someone else's ID number. <<
>
>Standard identifiers are **everyone's** id number. 

But they are still ID numbers, It has to start somewhere.  ID is not
toxic, it is required somewhere in any uniqifier. 


>That is the whole
>point of them. What would your auto insurance say if you announced
>that your car had IDENTITY = 42 or you call it �Fred� and you want to
>put that in the forms instead of the VIN?
>

Umm, I don't know. We don't use the VIN on our forms, we use the
vehicle registration number. I believed they use the DVLC database to
get the rest from that. Taking that as an example, they would probably
be upset if I didn't provide them with their reference number for my
car, but that's a requirement on their database inputs, not mine.

I do use the Vehicle registration number as my primary key for
vehicles, but I shouldn't, as it can actually be transferred between
vehicles, with the former either being assigned a new number, or
scrapped. So I suppose I should go back and change that entity to have
an ID. The VIN has no purpose to my users, as they don't care what the
VIN is, would have to work harder to find it and therefore the chances
of me getting the data populated in the database decrease...  

>
>>> They are all pointers to an ID number in someone else's database. They cost time and effort to determine by the user, and are generally useless in terms of memorability or ease of entry (Universally unique identifiers must, of necessity, be LONG streams of input data) (More than 8 is long.... ). So you're going to have to have another, user assigned, shorter, easier, unique code for each element. <<
>
>Unh? They are nothing like a pointer! Hardware? Assembly language? C
>programs? Hello?

No, Pointer, reference, identifier. Use a thesaurus. 

>
>Do you have a telephone number? In the US it is 10 digits, but somehow
>we memorized a lot them (my wife used to know the home phones for
>virtually every oncologist in Atlanta when she worked at a hospital).
>

My customers have some 3000 active accounts, that's a LOT of
meaningless numbers for someone to store in their heads. 


>The fact you can go to an authority to verify a standard code is a
>blessing. If you are in that trade, you find them very easy to enter �
>you scan their bar code most of the time and if you do manual entry
>you get a check digit to help you. But if you work in a trade, you can
>read the parts of the code. When I see ISBN13: 978-0-12-369379-2, I
>immediately know that this is a book (978), the book is in English
>(0), the publisher is Elsevier (12) and I can verify the check digit
>(Luhn code).
>
>Now if I want to find a description of the book, I can go to the
>Library of Congress website (and get the DDC and LOC codes, among
>other things) or I can go to Amazon.com (and get reviews, suggestions
>and sales figures), or any bookstore or library that has it on the
>shelf with a search.
>

I agree that ISBN is one of the better standards for use in this
regard, as it is very widely used, but 95% is not 100%. Primary keys
must be 100%..... 

>>> They cannot (generally) be used as sort criteria, reporting restriction ranges etc, etc. <<
>
>You just outlined examples with a hierarchy in their substrings in
>left to right order! Isn't that useful for sorting and reporting? For
>example, the ISBNs with the prefix �978-0-12-xxxxxx-x�, are Elsevier
>books. If I know how a publisher assigns their substring, I might be
>able to get categories and sub-categories.
>

The DUNS number is useless for sorting and reporting in my chart of
accounts. 

I don't want (for example) BAEN books in two subsets because they have
two ISBN prefixes. I don't want to have to know that (and I'm guessing
here) BAEN is somewhere after LOC on my report because a randomly
assigned number says it is...... (The company number in the ISBN is
not going to sort the company into a sensible order and you know
it.... )

>>> So their main purpose in life is to be the foreign key in a relational database. You're not going to let the user enter that number 'raw', it'll be from a lookup of some sort. <<
>
>Unh? These things are primary keys which are referenced in other
>tables in the schema.
>

And what do you call the column when viewed from the point of view of
the other table?..... the foreign key. When looked at in terms of
volume of data stored on the hard drive is the value of the primary
key of table A stored more times as a primary key (1 row) or as a
foreign key (MANY rows)? So this data is mostly used as  a foreign key
to join to that table. Or as a secondary index to access the data from
a more memorable set of information from within the table itself. 
(You list customers by company name, select one and the database then
uses the primary key to find the rest of the customer data).

>>> A practical example from my system, in the customer entity, is "Customer Q.A.?" meaning, should this system treat the customer as being ISO 900X Quality assured? Note, not "Is the customer ISO 900X Q.A.?", but "do they want to be treated as Q.A.". <<
>
>You do not validate the customer's claim? That just sounds wrong to
>me. No legal problems? Is the risk so low, you can ignore it? You'd
>want to see my medical license if you ran a hospital.
>

I don't care. They are a customer, if they want the ISO certification
for the material, they can have it. They don't need to be Quality
assured to have it, *I* need to be quality assured to give it to them
(or at least i have to declare that I am not, and they can't use this
cert as proof of quality).

If you came in to my hospital as a patient (read customer) I wouldn't
want to see your medical licence. If you asked to see your chart, or
have a copy of it, I would even probably have to give it to you
legally. If you were a Doctor on staff (read SUPPLIER) I would want
your qualifications. But you're not, you're a customer. 

>>> I am guessing that your answer would be to have their ISO QA registration number in a field and treat NULL no. However, there are customers with no ISO QA number (still attempting to get registered, or not prepared to put the effort in to get fully registered) who nevertheless want to be treated as such by my customer, (provide them with traceability information and supplier certificates for the material on delivery). Further, it is none of my customer's concern what their customers QA reg number is, and they don't want the hassle of finding it out. (If you thought steel men were intransigent, you never encountered Yorkshire Steel Men....). <<
>
>In the US, this would land you in prison or civil court. The legal
>term is �due diligence� and you have not done it. Also, claiming ISO
>certification allows the frauds to over-charge you.
>

Why are my customers charging me anything? Normally this works the
other way around. You may want to check your definitions before you
write another ERP or POS system..... 

>I don't know the ISO-900x numbering system, but the US patent system
>has a �patent pending� status for things that are not out of the
>process yet. I would avoid just a NULL and go for a QA_status that has
>{unknown, certification, application, not certification}
>

I don't care. It matters not to me. Their legal Q.A. status will not
affect in one jot or tittle my behaviour to them. They will get
certificates of quality issued to them alongside the despatch note if
they have asked for them, and not if they haven't. This sounds pretty
boolean/binary to me.... 
And no, I don't care when they requested the certs be turned on or
off. I care whether they want them printed out alongside the delivery
note at the time I print the delivery note. 

>As an aside, the US Department of Defense has started a UUID type code
>for all property worth more than $5000.00 and they will have a central
>database.

Not for my stuff they won't. It's in the U.K. 

Before your firm belief that there is a universally available standard
for all data sets, and that the use of ID is therefore inherently
wrong and 'not relational'. You need to find one UNIVERSALLY applied,
instantly available, functional, 'standard' identifier.... 
0
Iain
4/26/2010 2:01:30 PM
On Fri, 23 Apr 2010 10:36:52 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:


>>> I have to disagree here CELKO, we use it here in an insurance underwriting system to indicate the answers to yes no questions, removes the variations that are recorded YES, yes, y, NO, no, No, N,n etc. <<
>
>That is one reason I like computer forms � no room for �creative�
>answers and control of what is asked:)
>
>But this is not a survey, which is what we were talking about; it is
>data. When you get a "Did not answer" or "Contradicts another answer",
>you reject the document until you can get consistent data. The �was
>not asked� option is implied by the selection of input screens.
>

no WE weren't. I said User preferences form. Within my application,
there are user preferences, some of which are boolean in nature. So I
use bits to store the yes or no answer. Why not? 

Iain
0
Iain
4/26/2010 2:04:24 PM
>> Within my application, there are user preferences, some of which are Boolean in nature. So I use bits to store the yes or no answer. Why not? <<

How do you cast these bits to Boolean? Boolean values are modeled as
{+1, -1, 0, any positive number, etc} in various programming
languages. Within the Microsoft proprietary products, we have
disagreement between C# and VB.  What are called the X3J languages
(those with ANSI Standards) there is disagreement as to casting data
types to Boolean.  A lot of this traces back to the old days of high
end/low end, ones-complement/twos-complement hardware. Writing with
bits is a sign ofa mindset not yet out of assembly language
programming.

If you really have a data element drawn from a two-valued domain, then
you can usually find an industry standard (like my rH factor example)
or make up a more human readable code if there is no standard.

This is not the same as using flags for program control (flag
coupling), but bit flags get to be problematic in practice. Given,
say, three flags we have a combination of 8 patterns. Some of them are
probably illegal, so we need a CHECK() constraint to prevent them. Now
keep adding more flags. This leads to more CHECK() constraints.  You
are quickly playing the game of 20 questions.  Would you prefer to use
Dewey Decimal Classification or a set of bit flags for a library?

SQL is a predicate language; we want to find the state of the data by
using search condition and not by reading flags set by a prior
operation.


0
CELKO
4/26/2010 6:18:24 PM
On Mon, 26 Apr 2010 11:18:24 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>>> Within my application, there are user preferences, some of which are Boolean in nature. So I use bits to store the yes or no answer. Why not? <<
>
>How do you cast these bits to Boolean? Boolean values are modeled as
>{+1, -1, 0, any positive number, etc} in various programming
>languages. Within the Microsoft proprietary products, we have
>disagreement between C# and VB.  What are called the X3J languages
>(those with ANSI Standards) there is disagreement as to casting data
>types to Boolean.  A lot of this traces back to the old days of high
>end/low end, ones-complement/twos-complement hardware. Writing with
>bits is a sign ofa mindset not yet out of assembly language
>programming.
>
>If you really have a data element drawn from a two-valued domain, then
>you can usually find an industry standard (like my rH factor example)
>or make up a more human readable code if there is no standard.

Why? To what end? The application displays a check box for boolean
values, (Which is about as human readable as it gets for true/false
statements.)
I AM the industry standard for the question, "do you want my
application to include the QA documents alongside the delivery note"

My application is perfectly capable of conversing with the database
server and using the returned data a true or false from  a 'bit' data
type.

>
>This is not the same as using flags for program control (flag
>coupling), but bit flags get to be problematic in practice. Given,
>say, three flags we have a combination of 8 patterns. Some of them are
>probably illegal, so we need a CHECK() constraint to prevent them. Now
>keep adding more flags. This leads to more CHECK() constraints.  You
>are quickly playing the game of 20 questions.  Would you prefer to use
>Dewey Decimal Classification or a set of bit flags for a library?
>
>SQL is a predicate language; we want to find the state of the data by
>using search condition and not by reading flags set by a prior
>operation.
>

What? 
This is not the state of the data, it's an answer, given by a user,
with a given default value, to a preferences question. There is no
interrelation of this data with anything else, no check conditions, no
nothing. It has no effect on any of the other data in the system,
nothing is dependant on it from a relational point of view. 

The customer does or doesn't want QA certification to be provided with
their material. This affects the application behaviour, in the form of
providing a boolean answer to a previously asked question. No other
data anywhere in the database can be used to infer or calculate the
answer, the answer is not available on the web, or from an industry.
It just IS. 

I want to store a yes/no, true/false answer in the absolute minimum of
space on each row of the table in the database server. I want the
resolution of whether the question is yes or no as fast as possible. 

So, what data structure available in MS SQL server uses the minimum
number of bits on the hard drive to store the answer to a yes or no
question? 

What should one use, instead of a bit value column, to store yes or no
answers. 

I have already indicated that there is only a yes or no answer to this
question, there is no room for 'maybe' or 'unanswered', there is no
need to see when the question was asked/answered or last accessed, I
just want to know, for this particular flag, what is the given answer
for a given customer, or to get a list of all the customers with the
answer set to 'Y'. 

What data structure would you use to store this information in SQL
server? 
0
Iain
4/28/2010 4:14:03 PM
On Mon, 26 Apr 2010 11:18:24 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>>> Within my application, there are user preferences, some of which are Boolean in nature. So I use bits to store the yes or no answer. Why not? <<
>
>How do you cast these bits to Boolean? 

I don't, the database driver for the 4GL my application is written in
does, (assuming I'm updating the data) and Crystal reports does
(assuming I'm displaying the data). 

Not my problem. My problem is using the minimum amount of hard drive
to store the data. 

Iain
0
Iain
4/28/2010 4:15:52 PM
Reply:

Similar Artilces:

Can I keep an autoshape tool selected?
For example, if I want to draw multiple lines on a page using the line tool is there a way to just draw, draw, draw instead of going back to get the tool after each "draw". Thanks, Frank wrote: > For example, if I want to draw multiple lines on a page using the > line tool is there a way to just draw, draw, draw instead of going > back to get the tool after each "draw". > Thanks, In Word 2003 or earlier, you can double-click the line, arrow, rectange, or oval tool buttons on the Drawing toolbar (but not the other shapes that are only in the Aut...

Albert Kallal or Marshall Barton Help please
Multi Select Example: http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html From another post Marshall, you suggested the code above to solve my problem of an undbound check box in a continous form. This really looks like it will work for me. I have spent about 2 hours going over the code and trying to change it for my database, but I am missing something. Would either of you be willing to help? Please. Sue Not sure if this will help but here is the entire code for the form I am trying to use the code on. I am so sure I have missed something simple to those who know th...

need help with formula #2
how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

$10,000 a week, as much as $40,000 a month or even up to $500,000 a year!
1) Though multi-level Marketing has been near and dear to many for more than 50 years, 95% of those who trusted it have failed. 2) They failed because most people simply cannot close sales. Without closing they cannot recruit. 3) Without recruiting, duplication cannot happen. Even the 5% who CAN recruit cannot achieve their full potential without duplication. 4) None of the hype, recruiting systems, replicated websites, interruption marketing, sexy products, exotic compensation plans, training kits, books, tapes, seminars, conference calls, or "heavy hitters" have been able to...

Broken statement import
Hi, Rather foolishly, it now turns out, I downloaded and installed Microsoft Office Accounting Express 2008 for evaluation purposes. Now, Money 2005 (14.0.120.1105) running on 64-bit Vista Business no longer downloads and imports bank statements automatically. Previously, I would do the following: 1) Load Money (obviously) and open my bank .mny file 2) Click Favourites, Favourite Web Sites, Your Banks, Barclays Bank plc, Web Statements Logon Page 3) Enter login credentials Then I would download the latest transactions, and Money would import them into my accounts directly. Now, it n...

Formula Help 02-02-10
How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

editing frustrations in pulication view( HELP!)
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel What im trying to do is design a template where every single page has the same layout, so i can just go through and fill out the info in the appropriate places. In publication view, under master page, i got it all set up. When I go back to what i considered was regular view to start filling in what i need i face a few troubles. For one the header foot window thing shows up and wont go away unless i click the X, but then that grays EVERYTHING out and I cannot work on my document. so i click around to make it pop ...

missing toolbar in Word's Help screen
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC When I try to access the Help system from Word 2008 (or Excel, for that matter), there is no toolbar at the top of the Help screen--there's no Search box, Topics, Back/Forward, Home, or Print buttons. The PowerPoint Help screen, on the other hand, does have its toolbar. I'm on a Mac PowerPC G4 running OS 10.4.11. <br><br>How can I make the toolbar available for Word's and Excel's Help screens? Click the oblong button at the right end of the Help window's Title Bar. HTH |:>) ...

select distinct id with other data
Hello everybody, I have a table with such data: id name 1 Mario 1 Mario Krsnic 1 MKrsnic 1 MarioK The same Id refers to different names of the same person. how can I select distinct ID with both columns? If I use select distinct id,name then I get the above data. I would like to have only: 1,Mario(or some other variante of the name) In the result should every id appear only once. Thanks for suggestions! Mario Mario Krsnic wrote: >I have a table with such data: >id name >1 Mario >1 Mario Krsnic >1 MKrsnic >1 MarioK ...

Formula Help
I have first names in one column and last names in a second column and I need to combine them in one column for an import. The formula I usually use is =A1&" "&B1 but for some reason it doesn't want to work for this spreadsheet. It's Office 2007 and I've saved the file as a regular worksheet. Any ideas? Never mind, it's just something with this particular spreadsheet. I copied and pasted the columns into a new one and it worked. "Brittany" wrote: > I have first names in one column and last names in a second column and I need >...

Need help storing all my emails before i redo my pc outlook 2003
i have microsoft outlook 2003 on windows xp i need to redo my pc but i dont want to loose my emails and contacts and I have too many things stored in files it'sjust too many things to move one by one thanks iris The file you need to back up is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/config/backup.htm -- Russ Valentine [MVP-Outlook] "IrisR" <anonymous@discussions.microsoft.com> wrote in message news:a1b901c434bd$d94a3940$a...

Select subform's query criteria from form's unbound cbo
I want users to be able to select a name from a form's unbound combo box and that selection to automatically become the criteria for the subform's underlying query, without the parameter box for the query popping up. How do I do this? I'm new to VBA and not an Access 2003 whiz either. TIA for your help! WorkerBee wrote: > I want users to be able to select a name from a form's unbound combo > box and that selection to automatically become the criteria for the > subform's underlying query, without the parameter box for the query > popping up. How do I do t...

Need advice on 5.5 migration
Our current setup is all NT4 servers and I am running Exch 5.5. My boss wants a whole new domain created for new servers that we will be getting that will be running 2003 server and exchange 2003 (current servers are too outdated for an upgrade). I have set this up in a test environment and created the trusts and ran the ADMT and things ran fairly smoothly except password issues but that may be another post later. My question is which is the best way to approach this from an Exchange standpoint? Should the 2003 join the existing site or should I create a new exchange site for the 2003?...

at times i have to reboot to start Outlook 2003, any help?
it would be appreciated Sounds like you have a 3rd party application or addin that isn't letting Outlook close properly. Try bringing up the task list (start > run > tasklist.exe > ok button) to see if Outlook is still running when it won't start. (check the processes tab) "skimmi" <skimmi@discussions.microsoft.com> wrote in message news:42FAFCC7-55BC-45DB-B8D7-674FF9638B14@microsoft.com... > it would be appreciated Not sure if this helped or not? The problem did happen again and tried what you suggested but the screen only flashed up momentari...

select with subquery in select clause
Hi I need to make a select from 2 tables whit this layout always with the newest datetime exchange currency. The local cuurency is Mexican Pesos Table 1 Currencies example: CurrencyID Key Description 1 USD USA Dollar 2 EUR European Euro 3 Yen Japan Yen Tabla 2 is Day exchange Currency CurrencyID Equivalent DateTime 1 12.50 2010-08-07 08:00:00 1 12.55 2010-08-07 08:10:00 1 12.49 2010-08-07 08:20:00 1 12.60 2010-08-07 08:30:00 1 12.51 2010-08-07 08:40:00 1 12.52 2010-08-07 08:50:00 1 12.48 2010-08-07 09:00:00 1 12.61 2010-08-07 09:10:00 <-- the newest xchange for USD 2...

Manually uninstall GP 10
I am having problems with a GP 10 install on a Vista machine. I tried to do an upgrade from 9 to 10 and it stopped as it was looking for a Service Pack. I have tied to uninstall or reinstall both 9.0 and 10.0 and the setup file will not execute. I have turned off the user account control. I probably have a conflict with something as this a development machine with a myriad of programs on it. It there a way to manually uninstall GP? Jim ...

Need query to return absence of record
Hello all, hope everyone is doing well this fine morning! I need some help in constructing a query. I have three tables, designed as so: Personnel FullName(PK) Department Classes ClassName(PK) TrainingHistory *Junc table between Personnel and Classes* FullName(FK) ClassName(FK) DateTaken TrainingHistory records every instance of a class taken by all personnel. I need to be able to find people who HAVE NOT taken a given class. I need to be able to pick what class I search for each time I do the query. For example, I need to know who in the organization has NOT t...

Installation of Office 2004 on OS X 10.5 Leopard
I installed Office 2004 (and EndNote) after upgrading to Leopard 10.5 and something is amiss. Three (probably related) symptoms I observed: 1. After the install, when opening Word, I'm greeted with the prompt: "An unexpected error occurred while trying to load the Microsoft Framework library." 2. When trying to update Office 2004 via Microsoft AutoUpdate, the application recognizes any out-of-date versions, but then hangs and doesn't download anything (requires a force quit). Also hangs if running updates that were manually downloaded from Microsoft's website. (even o...

Need site for VB Tutorial
Hello Can someone suggest some site to learn VB?? Thanks Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=26676 Hi see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Frank Kabel Frankfurt, Germany "SMILE" <SMILE.1dp2ta@excelforum-nospam.com> schrieb im Newsbeitrag news:SMILE.1dp2ta@excelforum-nospam.com... > > Hello > Can someone suggest some site to learn VB...

need example code to get the contactID from SFA/conts/edit.aspx...
Hi!! My question is: How can I get the right ContactID from the current/opened Contact in Microsoft CRM? Can I read the ContactID out from the URL? Is there an example Code available? Microsoft�s SDK for CRM is not very helpful for this Problem. Has anybody already an example code? Thank you for your help! Greetings Daniel You should be able to get it from the query string, as long as you make sure that the entry in the isv.config file with the link to your app has the variable "PassParams" equal to 1 For example: <Button Title="Test" ToolTip="Info on Test&qu...

save macro help!! #2
ttt, anyone? -- gmannew ----------------------------------------------------------------------- gmannewt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512 View this thread: http://www.excelforum.com/showthread.php?threadid=26761 ...

Help.....Formula Required
If current contents off cell are a variable no from a previous equatio (eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110) and � (minus) the lower figure (107) from the higher (110) and = answer (3) to be displayed in SAME cell -- gareth6 ----------------------------------------------------------------------- gareth67's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495 View this thread: http://www.excelforum.com/showthread.php?threadid=26584 Sorry just realised you asked for nearest multiple of 10. Roundup will surprisingly, always round u...

Email Template in Campaign Activity not available 04-24-06
If I create a Campaign in CRM and decide to communicate via Email with my marketing list members by using a Campaign Activity with "Email" as channel, I dont seem to be able to personalize the activity? One would expect to be able to leverage from Email Templates from within Campaigns...comments? ...

Trigger question 03-15-10
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is NULL, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT Can I create a trigger to do the above ? Thank you On Mar 15, 10:09=A0am, "fniles"...

Really need some help with a tricky formula
Good afternoon everyone I'll try and put this as simple as possible but have tried doing this myself and cannot work out how to do this. I have a new employee - summary as follows He starts 01/01/2010 (c65) He is a level 2 (b65) He has a monthly sales target of say €10,000 (d65) However his target does not start in month 1, instaead for the first 4 months (Jan - April his target will be €0.00, From months 5 - 8 (May - Aug) his taget will be 75% of the €10,000 (€7,500). From month 9 (Sept) it'll be 100% of the target = €10,000 Along the top of my spreadsheet I ...