| Downloads |
|---|
| 24009.zip |
My company provides financial data about more than 10,000 US companies that are listed on different US stock exchanges. One of our clients asked me to develop a screening tool that would let their users analyze companies based on market cap, stock price, phase of product development, years in cash, number of corporate partners, and other factors.
I encountered a problem when I tried to display the count of corporate partners for each company. The data in the CorporatePartner column is a comma-delimited string that isn't in a relational database format. To display the count, I tried using dynamic SQL, which worked until I put in a WHERE condition to check the user-selected criteria—No of Partners<3 (or) No of Partners>3.
To find the number of corporate partners for each company, I wrote the T-SQL query that Listing 7 shows. This query logic works wonderfully. I can use a SELECT statement to display the count of corporate partners, and I can use the same logic in the WHERE clause to check the count based on user-selected criteria. This query subtracts the derived length (by replacing all commas with an empty string) from the total length of each corporate partner. The code in Listing 8 includes the sample data that I used to populate the tblCompanyInfo table, which consists of company ID, symbol, and a list of each company's corporate partners.
