Me again - Really need an answer

Results 1 to 2 of 2

Thread: Me again - Really need an answer

  1. #1
    Join Date
    Dec 1969

    Default Me again - Really need an answer

    &nbsp;<BR>Sorry for the repost guys but I really need an answer to this and I think my last post is slipping to the bottom of the board where it is unlikely to get an answer...<BR><BR>sorry, but here you go again....<BR><BR>Hi guys, <BR><BR>I have a problem and I was wondering if you can help me solve it. I have a database table with something in the region of a quarter of a million records. There are three fields in the table, customerID, companyID and customerType. The primary key is customerID. The companyID field shows what company a particular customer is associated with. Any companyID can have either one or many of our customers. The customerType field assignes customers to one of fifteen different cusmtomer types (denoted by 1,2,....15). <BR><BR>My problem is this: <BR><BR>A companyID may have different customer types assciated with it. However, I want to find the most common customerType associated wtih each of the companyID&#039;s. I will then store these values in a seperate table (a table with two fields - something like companyID and TypicalCustomerType). <BR><BR>Given that there are soo many records in the table, I want to do this in the most efficent way possible. <BR><BR>Has anyone got any ideas of how I can do this efficently. I think I write a script to do it myself. However, the way I am thinking of doing it would involve opening thousands of record sets and would be very taxing on the processor. <BR> <BR><BR>Any help would be much appreciated. <BR><BR>Thanks, <BR>Simon

  2. #2
    Join Date
    Dec 1969

    Default RE: Me again - Really need an answer

    Hy,<BR>just do something like this:<BR>Select companyID , customerType , Count(customerType ) from whereever<BR><BR>this recordset contains your information. Requery it like this for the maximum numbers of customertype:<BR>Select companyID , customerType , Max(customerType ) from New_Table<BR><BR>My T-SQL isnt good enought for doing this in one pass and `you cant use 2 aggregates in one pass so filter it via ado or insert into a new Table and requery<BR><BR>Best Regards<BR>Kars-T<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts