We help IT Professionals succeed at work.

Generating a banding

665 Views
Last Modified: 2012-05-09
I have a field which is a number. Between 0 and 100000.
I need to create a further field from this. This field is a banding. So if the number is 10095 it needs to be labelled in the new field as 10001 - 20000. The banding are 10000 apart an run from 0.

So 0 - 10000
10001 - 20000
20001 - 30000

Cheers
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
The most flexible approach would be to create a new table, tblBands.  Assuming you are only ever dealing with ints..tblBands--------------------------------------------------StartAt (int)EndAt (int)BandName (varchar(100))Now you can use a query like this:SELECT a.SomeColumn, b.BandNameFROM SomeTable a INNER JOIN    tblBands b ON a.SomeColumn BETWEEN b.StartAt AND b.EndAt

Author

Commented:
Great.  Works a dream.  
CERTIFIED EXPERT
Top Expert 2010

Commented:
Glad to help :)
CERTIFIED EXPERT
Top Expert 2012

Commented:
A better approach in my view would be to create a computed column (no additional disk space or maintenance required) that creates that label as follows:

ALTER TABLE YourTable
ADD Banding AS CAST((Number / 10000) * 10000 + 1 AS varchar(10)) + '-' + CAST((Number / 10000) * 10000 + 10000 AS varchar(10))
CERTIFIED EXPERT
Top Expert 2011

Commented:
i can see the benefits of both suggested approaches; however can you confirm

1) what you mainly intend doing with the new column (ie display or searching)
2) how big the table is (row size , number of rows)
3) is the database in which the table resides used for a production system, or
    is it for MI purposes , or hybrid?
4) the banding is very clean at present , have you considered what would happen
    if you needed to add additional rows to a Band (e.g. you  run out of ids in the
     range 0 - 10000 but need to allocate more to it (if thats possible) ... )
 

Author

Commented:
I created a table with the bandings in and use the following SQL.  It works fine, apart from when the fields I am trrying to band is null it contains the banding 0 - 10,000.  How do I correct this?

SELECT     dbo.vacancies.date_found, dbo.vacancies.job_id, dbo.vacancies.title, dbo.vacancies.salary_min, dbo.vacancies.salary_max, dbo.vacancies.jobboard_id,
                      dbo.vacancies.advertiser_id, dbo.vacancies.duplicate_id, dbo.vacancies.vac_hash, dbo.vacancies.original, dbo.vacancies.repost7,
                      dbo.job_boards.name AS jb_source, dbo.advertisers.adv_id, dbo.advertisers.duns_number, dbo.job_categories.job_category, dbo.advertisers.adv_name,
                      dbo.advertisers.adv_type, dbo.advertisers.duns_HQ_number, dbo.advertisers.duns_public_sector, dbo.locations.city, dbo.locations.county, dbo.locations.region,
                      dbo.locations.country, dbo.vacancies.country AS raw_country, dbo.job_types.final_job_type, dbo.job_titles.final_job_title
FROM         dbo.vacancies INNER JOIN
                      dbo.advertisers ON dbo.vacancies.advertiser_id = dbo.advertisers.adv_id INNER JOIN
                      dbo.job_titles ON dbo.vacancies.title = dbo.job_titles.raw_job_title INNER JOIN
                      dbo.job_boards ON dbo.vacancies.jobboard_id = dbo.job_boards.jobboard_id INNER JOIN
                      dbo.locations ON dbo.vacancies.raw_locations = dbo.locations.raw_location INNER JOIN
                      dbo.job_types ON dbo.vacancies.raw_type = dbo.job_types.raw_job_type INNER JOIN
                      dbo.salary_bandings_definition ON dbo.vacancies.salary_min BETWEEN dbo.salary_bandings_definition.min_band AND
                      dbo.salary_bandings_definition.max_band LEFT OUTER JOIN
                      dbo.job_categories ON dbo.job_titles.final_job_title = dbo.job_categories.final_job_title
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>It works fine, apart from when the fields I am trrying to band is null it contains the banding 0 - 10,000.  How do I correct this?<<
One approach is to use the ISNULL() function, as in:
ISNULL(YourPotentiallyNullColumnGoesHere, '0 - 10,000')

Incidentally, if you use aliases in your query it will make it more readable as in:

SELECT  v.date_found,
        v.job_id,
        v.title,
        v.salary_min,
        v.salary_max,
        v.jobboard_id,
        v.advertiser_id,
        v.duplicate_id,
        v.vac_hash,
        v.original,
        v.repost7,
        b.name jb_source,
        a.adv_id,
        a.duns_number,
        c.job_category,
        a.adv_name,
        a.adv_type,
        a.duns_HQ_number,
        a.duns_public_sector,
        l.city,
        l.county,
        l.region,
        l.country,
        v.country AS raw_country,
        jt.final_job_type,
        t.final_job_title
FROM    dbo.vacancies v
        INNER JOIN dbo.advertisers a ON v.advertiser_id = a.adv_id
        INNER JOIN dbo.job_titles t ON v.title = t.raw_job_title
        INNER JOIN dbo.job_boards b ON v.jobboard_id = b.jobboard_id
        INNER JOIN dbo.locations l ON v.raw_locations = l.raw_location
        INNER JOIN dbo.job_types jt ON v.raw_type = jt.raw_job_type
        INNER JOIN dbo.salary_bandings_definition s ON v.salary_min BETWEEN s.min_band AND s.max_band
        LEFT OUTER JOIN dbo.job_categories c ON t.final_job_title = c.final_job_title

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
try
SELECT     v.date_found, v.job_id, v.title, v.salary_min, v.salary_max, v.jobboard_id, 
                      v.advertiser_id, v.duplicate_id, v.vac_hash, v.original, v.repost7, 
                      dbo.job_boards.name AS jb_source, dbo.advertisers.adv_id, dbo.advertisers.duns_number, dbo.job_categories.job_category, dbo.advertisers.adv_name, 
                      dbo.advertisers.adv_type, dbo.advertisers.duns_HQ_number, dbo.advertisers.duns_public_sector, dbo.locations.city, dbo.locations.county, dbo.locations.region, 
                      dbo.locations.country, v.country AS raw_country, dbo.job_types.final_job_type, dbo.job_titles.final_job_title
FROM    (select v.*
              ,coalesce(salary_min,0) as salBander
           from  dbo.vacancies) as v INNER JOIN
                      dbo.advertisers ON v.advertiser_id = dbo.advertisers.adv_id INNER JOIN
                      dbo.job_titles ON v.title = dbo.job_titles.raw_job_title INNER JOIN
                      dbo.job_boards ON v.jobboard_id = dbo.job_boards.jobboard_id INNER JOIN
                      dbo.locations ON v.raw_locations = dbo.locations.raw_location INNER JOIN
                      dbo.job_types ON v.raw_type = dbo.job_types.raw_job_type INNER JOIN
                      dbo.salary_bandings_definition ON v.salbander BETWEEN dbo.salary_bandings_definition.min_band AND 
                      dbo.salary_bandings_definition.max_band LEFT OUTER JOIN
                      dbo.job_categories ON dbo.job_titles.final_job_title = dbo.job_categories.final_job_title

Open in new window

Author

Commented:
This is great.  Thank you.  I'm new to SQL, would be so kind to write updatedthe SQL syntax I pasted in and include the isnull function?  i do not know where to write it.
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
Please request that a Moderator re-open this question so that you can award points to the real solution.

Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.