Hi There,

HyperBI is the blog from me (Terry McCann) a data professional based Exeter, UK. I aim to focus on content I am interested in from a wide variety of topics focusing on SQL Server, Business Intelligence, SSIS, SSAS, SSRS, Excel, PowerPivot, PowerBI and open source tools such as R. I will also pay attention to the role of the BI DBA, looking at SQL syntax, administration, optimisation, set theory, relational theory and career development.

About me – I have worked in a few different capacities over the last 6 years ranging from Developer to Database Administrator and now working as a Data Warehouse Developer for a large multinational company. I am a Microsoft Certified Professional holding certifications in SQL Server and other Microsoft products.

I am proud to be one of the organisers of possibly the best SQL Server user group in the UK (if not the world! :P), SQLSouthWest based out of Exeter, UK. As well as co-organising SQLSouthWest I am also co-organiser of the PASS SQL Saturday Exeter, a two day community driven conference for SQL Server professionals. I am passionate about the SQL community and can often be found answering questions on forums and attending conferences.

You can also find me on Twitter @SQLShark.


View Terry McCann's profile on LinkedIn



Web Site Terms and Conditions of Use

1. Terms

By accessing this web site, you are agreeing to be bound by these
web site Terms and Conditions of Use, all applicable laws and regulations,
and agree that you are responsible for compliance with any applicable local
laws. If you do not agree with any of these terms, you are prohibited from
using or accessing this site. The materials contained in this web site are
protected by applicable copyright and trade mark law.

2. Use License

  1. Permission is granted to temporarily download one copy of the materials
    (information or software) on HyperBI’s web site for personal,
    non-commercial transitory viewing only. This is the grant of a license,
    not a transfer of title, and under this license you may not:

    1. modify or copy the materials;
    2. use the materials for any commercial purpose, or for any public display (commercial or non-commercial);
    3. attempt to decompile or reverse engineer any software contained on HyperBI’s web site;
    4. remove any copyright or other proprietary notations from the materials; or
    5. transfer the materials to another person or “mirror” the materials on any other server.
  2. This license shall automatically terminate if you violate any of these restrictions and may be terminated by HyperBI at any time. Upon terminating your viewing of these materials or upon the termination of this license, you must destroy any downloaded materials in your possession whether in electronic or printed format.

3. Disclaimer

  1. The materials on HyperBI’s web site are provided “as is”. HyperBI makes no warranties, expressed or implied, and hereby disclaims and negates all other warranties, including without limitation, implied warranties or conditions of merchantability, fitness for a particular purpose, or non-infringement of intellectual property or other violation of rights. Further, HyperBI does not warrant or make any representations concerning the accuracy, likely results, or reliability of the use of the materials on its Internet web site or otherwise relating to such materials or on any sites linked to this site.

4. Limitations

In no event shall HyperBI or its suppliers be liable for any damages (including, without limitation, damages for loss of data or profit, or due to business interruption,) arising out of the use or inability to use the materials on HyperBI’s Internet site, even if HyperBI or a HyperBI authorized representative has been notified orally or in writing of the possibility of such damage. Because some jurisdictions do not allow limitations on implied warranties, or limitations of liability for consequential or incidental damages, these limitations may not apply to you.

5. Revisions and Errata

The materials appearing on HyperBI’s web site could include technical, typographical, or photographic errors. HyperBI does not warrant that any of the materials on its web site are accurate, complete, or current. HyperBI may make changes to the materials contained on its web site at any time without notice. HyperBI does not, however, make any commitment to update the materials.

6. Links

HyperBI has not reviewed all of the sites linked to its Internet web site and is not responsible for the contents of any such linked site. The inclusion of any link does not imply endorsement by HyperBI of the site. Use of any such linked web site is at the user’s own risk.

7. Site Terms of Use Modifications

HyperBI may revise these terms of use for its web site at any time without notice. By using this web site you are agreeing to be bound by the then current version of these Terms and Conditions of Use.

8. Governing Law

Any claim relating to HyperBI’s web site shall be governed by the laws of the State of Exeter without regard to its conflict of law provisions.

General Terms and Conditions applicable to Use of a Web Site.

Privacy Policy

Your privacy is very important to us. Accordingly, we have developed this Policy in order for you to understand how we collect, use, communicate and disclose and make use of personal information. The following outlines our privacy policy.

  • Before or at the time of collecting personal information, we will identify the purposes for which information is being collected.
  • We will collect and use of personal information solely with the objective of fulfilling those purposes specified by us and for other compatible purposes, unless we obtain the consent of the individual concerned or as required by law.
  • We will only retain personal information as long as necessary for the fulfillment of those purposes.
  • We will collect personal information by lawful and fair means and, where appropriate, with the knowledge or consent of the individual concerned.
  • Personal data should be relevant to the purposes for which it is to be used, and, to the extent necessary for those purposes, should be accurate, complete, and up-to-date.
  • We will protect personal information by reasonable security safeguards against loss or theft, as well as unauthorized access, disclosure, copying, use or modification.
  • We will make readily available to customers information about our policies and practices relating to the management of personal information.

We are committed to conducting our business in accordance with these principles in order to ensure that the confidentiality of personal information is protected and maintained.

1 comment

  1. Hello Terry,
    Seeking your help.
    Please help me to design a sql query if possible.


    Two Tables are there

    Table 1 called as Driver- Purpose of Driver Table is to look for only those data in mytable which is available in driver
    Table 2 called as mytable: Output Rows will be fetch from here.

    if there is a value in the columns of driver table it should look for the value to be contained in that column field in mytable

    null/blank in driver means any value

    declare @driver table

    FromYear varchar (100),
    FromMake varchar (100),
    FromModel varchar (100),
    FromRemarks varchar (300),
    FromRemarks2 varchar (300),
    ToYear varchar (300),
    ToMake varchar (300),
    ToModel varchar (300),
    ToRemarks varchar (300),
    ToRemarks2 varchar (300)

    INSERT @driver

    SELECT ‘2013’ ,’Nissan’ ,’Rogue’ ,NULL,NULL , ‘2014’ ,’Nissan’ ,’sedan’ ,NULL,NULL UNION ALL
    SELECT ‘2006’ , ‘Chevrolet ‘,’Rover ‘ ,NULL,NULL , ‘2007’ ,’BMW’,’TESLA’,’ AutomaticCVT%’ ,NULL

    SELECT * FROM @driver

    DECLARE @mytable TABLE
    MAKE VARCHAR (250),
    MODEL VARCHAR (250),
    YEAR VARCHAR (250),
    PART VARCHAR (250)

    Insert @mytable

    SELECT ‘Nissan’,’Rogue’,’2013′,NULL,NULL,’AEM21-209ED-HK’ UNION ALL
    SELECT ‘Nissan’,’Rogue’,’2013′,NULL,NULL,’AEM21-2057DK’ UNION ALL
    SELECT ‘Nissan’,’Rogue’,’2013′,NULL,NULL,’AEM21-2279DK’ UNION ALL
    SELECT ‘Nissan’,’Rogue’,’2013′,NULL,NULL,’AEM21-2028DK’ UNION ALL

    SELECT ‘Nissan’,’SEDAN’,’2014′,NULL,NULL,’AEM21-209ED-HK’ UNION ALL
    SELECT ‘Nissan’,’SEDAN’,’2014′,NULL,NULL,’AEM21-2057DK’ UNION ALL
    SELECT ‘Nissan’,’SEDAN’,’2014′,NULL,NULL,’AEM21-2028DK’ UNION ALL
    — Part numbers (AEM21-2279DK) contain 2013, Nissan, Rogue
    –same part should also contain 2014, Nissan, SEDAN if not then it should be in output
    SELECT ‘Chevrolet’,’Rover’,’2006′,NULL,NULL,’BRM1G1.9007A2’UNION ALL
    SELECT ‘Chevrolet’,’Rover’,’2006′,NULL,NULL,’BRM1F4.6001A1’UNION ALL
    SELECT ‘Chevrolet’,’Rover’,’2006′,NULL,NULL,’BRM1G1.9018A2’UNION ALL
    SELECT ‘Chevrolet’,’Rover’,’2006′,NULL,NULL,’BRM1G1.9022A1’UNION ALL
    SELECT ‘Chevrolet’,’Rover’,’2006′,NULL,NULL,’BRM1G1.9018A1’UNION ALL

    SELECT ‘BMW’,’Tesla’,’2007′,’ AutomaticCVT’,NULL,’BRM1G1.9007A2’UNION ALL
    SELECT ‘BMW’,’Tesla’,’2007′,’ AutomaticCVT’,NULL,’BRM1F4.6001A1’UNION ALL
    SELECT ‘BMW’,’Tesla’,’2007′,’ AutomaticCVT’,NULL,’BRM1G1.9018A2’UNION ALL
    SELECT ‘BMW’,’Tesla’,’2007′,’ AutomaticCVT’,NULL,’BRM1G1.9022A1′

    — Part number (BRM1G1.9018A1) contain ‘Chevrolet’,’Rover’,’2006′ same part should also
    — contain ‘BMW’,’Tesla’,’2007′ with ‘ AutomaticCVT%’ in Remarks1, if does not then it should be in output.

    — OUTPUT Look like this

    — ‘Nissan’,’Rogue’,’2013′,NULL,NULL,’AEM21-2279DK’

    Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>