datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find the highest yield CD at your local bank?

Execute the following T-SQL script in Microsoft SQL Server Management Studio Query Editor to search for the highest yield CD.

-- Microsft SQL Server T-SQL interest rate calculation
-- MSSQL find CD (Certificate of Deposit) with highest yield
-- T-SQL table create - T-SQL insert into table – populate table
USE AdventureWorks;
 
CREATE TABLE CDRate (
  Bank         CHAR(40),
  Headquarters CHAR(20),
  CD           CHAR(20),
  Years        INT,
  Rate         SMALLMONEY)
 
GO
/***** FOLLOWING IS DUMMY CD RATE DATA *****/
insert CDRate select 'Citigroup', 'New York, NY', '4YR CD', 4, 17.3
insert CDRate select 'Bank of America Corp.', 'Charlotte, NC', '4YR CD', 4, 17.3
insert CDRate select 'J. P. Morgan Chase & Company', 'Columbus, OH', '4YR CD', 4, 17.3
insert CDRate select 'Wachovia Corp.', 'Charlotte, NC', '4YR CD', 4, 17.3
insert CDRate select 'Taunus Corp.', 'New York, N.Y.', '5YR CD', 5, 23.0
insert CDRate select 'Wells Fargo & Company', 'San Fransisco, CA', '3YR CD', 3, 11.0
insert CDRate select 'HSBC North America Inc.', 'Prospect Heights,IL', '4YR CD', 4, 17.3
insert CDRate select 'U.S. Bancorp', 'Minneapolis, MN', '4YR CD', 4, 17.3
insert CDRate select 'Bank of the New York Mellon Corp.', 'New York, NY', '4YR CD', 4, 17.3
insert CDRate select 'Suntrust, Inc.', 'Atlanta, Ga.', '4YR CD', 4, 17.3
insert CDRate select 'Citizens Financial Group, Inc.', 'Providence, RI', '3YR CD', 3, 11.0
insert CDRate select 'National City Bank', 'Cleveland, OH', '4YR CD', 4, 17.3
insert CDRate select 'State Street Corp.', 'Boston, MA', '4YR CD', 4, 17.3
insert CDRate select 'Capital One Financial Corp.', 'McLean, VA', '4YR CD', 4, 17.3
insert CDRate select 'Regions Financial Corp.', 'Birmingham, AL', '4YR CD', 4, 17.3
insert CDRate select 'PNC Financial Services Group, Inc.', 'Pittsburg, PA', '5YR CD', 5, 23.0
insert CDRate select 'BB&T Corp.', 'Winston-Salem, NC', '4YR CD', 4, 17.3
insert CDRate select 'TD Bank North, INC.', 'Portland, ME', '4YR CD', 4, 17.3
insert CDRate select 'Fifth Third Bankcorp', 'Cincinatti, OH', '3YR CD', 3, 11.0
insert CDRate select 'Keycorp Cleveland,', 'OH', '4YR CD', 4, 17.3
insert CDRate select 'Northern Trust Corp.', 'Chicago, IL', '4YR CD', 4, 17.3
insert CDRate select 'Bancwest Corp.', 'Honolulu, HA', '5YR CD', 5, 23.0
insert CDRate select 'Harris Financial Corp.', 'Wilmington, DE', '4YR CD', 4, 17.3
insert CDRate select 'Comerica Incorporated', 'Dallas, TX', '4YR CD', 4, 17.3
insert CDRate select 'M&T Bank Corp.', 'Buffalo, NY', '4YR CD', 4, 17.3
insert CDRate select 'Marshall & Ilsley Corp.', 'Milwaukee, WI', '4YR CD', 4, 17.3
insert CDRate select 'BBVA USA Bancshares, Inc.', 'The Woodlands, TX', '3YR CD', 3, 11.0
insert CDRate select 'Unionbancal Corporation', 'San Fransisco, CA', '4YR CD', 4, 17.3
insert CDRate select 'Huntington Bancshares, Inc.', 'Columbus, OH', '4YR CD', 4, 17.3
insert CDRate select 'Zions Bancorporation', 'Salt Lake City, UT', '5YR CD', 5, 23.0
go
 
SELECT Legend = 'BEST CD RATES (dummy data)',
       Bank,
       HeadQuarters,
       CD
FROM   CDRate
WHERE  Rate / Years IN (SELECT max(Rate / Years)
                        FROM   CDRate)
 
GO
/* Results
 

Legend Bank HeadQuarters CD
BEST CD RATES (dummy data) Taunus Corp.                             New York, NY         5YR CD              
BEST CD RATES (dummy data) PNC Financial Services Group     Pittsburg, PA        5YR CD              
BEST CD RATES (dummy data) Bancwest Corp.                           Honolulu, HI         5YR CD              
BEST CD RATES (dummy data) Zions Bancorporation                     Salt Lake City, UT   5YR CD              

            
*/
 
 
------------
-- Lookup table for State Codes
------------
-- State/Possession/Provinces/Miliary Code Abbreviations
-- SQL Server table create
CREATE TABLE dbo.StateCode (
  StateCodeID INT    IDENTITY ( 1 , 1 )    UNIQUE,
  State       VARCHAR(40)    NOT NULL,
  Code        CHAR(2)    PRIMARY KEY);
 
GO
 
-- SQL populate lookup table
-- U.S. States & Posessions
INSERT INTO dbo.StateCode VALUES('ALABAMA','AL');
INSERT INTO dbo.StateCode VALUES('ALASKA','AK');
INSERT INTO dbo.StateCode VALUES('AMERICAN SAMOA','AS');
INSERT INTO dbo.StateCode VALUES('ARIZONA ','AZ');
INSERT INTO dbo.StateCode VALUES('ARKANSAS','AR');
INSERT INTO dbo.StateCode VALUES('CALIFORNIA ','CA');
INSERT INTO dbo.StateCode VALUES('COLORADO ','CO');
INSERT INTO dbo.StateCode VALUES('CONNECTICUT','CT');
INSERT INTO dbo.StateCode VALUES('DELAWARE','DE');
INSERT INTO dbo.StateCode VALUES('DISTRICT OF COLUMBIA','DC');
INSERT INTO dbo.StateCode VALUES('FEDERATED STATES OF MICRONESIA','FM');
INSERT INTO dbo.StateCode VALUES('FLORIDA','FL');
INSERT INTO dbo.StateCode VALUES('GEORGIA','GA');
INSERT INTO dbo.StateCode VALUES('GUAM ','GU');
INSERT INTO dbo.StateCode VALUES('HAWAII','HI');
INSERT INTO dbo.StateCode VALUES('IDAHO','ID');
INSERT INTO dbo.StateCode VALUES('ILLINOIS','IL');
INSERT INTO dbo.StateCode VALUES('INDIANA','IN');
INSERT INTO dbo.StateCode VALUES('IOWA','IA');
INSERT INTO dbo.StateCode VALUES('KANSAS','KS');
INSERT INTO dbo.StateCode VALUES('KENTUCKY','KY');
INSERT INTO dbo.StateCode VALUES('LOUISIANA','LA');
INSERT INTO dbo.StateCode VALUES('MAINE','ME');
INSERT INTO dbo.StateCode VALUES('MARSHALL ISLANDS','MH');
INSERT INTO dbo.StateCode VALUES('MARYLAND','MD');
INSERT INTO dbo.StateCode VALUES('MASSACHUSETTS','MA');
INSERT INTO dbo.StateCode VALUES('MICHIGAN','MI');
INSERT INTO dbo.StateCode VALUES('MINNESOTA','MN');
INSERT INTO dbo.StateCode VALUES('MISSISSIPPI','MS');
INSERT INTO dbo.StateCode VALUES('MISSOURI','MO');
INSERT INTO dbo.StateCode VALUES('MONTANA','MT');
INSERT INTO dbo.StateCode VALUES('NEBRASKA','NE');
INSERT INTO dbo.StateCode VALUES('NEVADA','NV');
INSERT INTO dbo.StateCode VALUES('NEW HAMPSHIRE','NH');
INSERT INTO dbo.StateCode VALUES('NEW JERSEY','NJ');
INSERT INTO dbo.StateCode VALUES('NEW MEXICO','NM');
INSERT INTO dbo.StateCode VALUES('NEW YORK','NY');
INSERT INTO dbo.StateCode VALUES('NORTH CAROLINA','NC');
INSERT INTO dbo.StateCode VALUES('NORTH DAKOTA','ND');
INSERT INTO dbo.StateCode VALUES('NORTHERN MARIANA ISLANDS','MP');
INSERT INTO dbo.StateCode VALUES('OHIO','OH');
INSERT INTO dbo.StateCode VALUES('OKLAHOMA','OK');
INSERT INTO dbo.StateCode VALUES('OREGON','OR');
INSERT INTO dbo.StateCode VALUES('PENNSYLVANIA','PA');
INSERT INTO dbo.StateCode VALUES('PUERTO RICO','PR');
INSERT INTO dbo.StateCode VALUES('RHODE ISLAND','RI');
INSERT INTO dbo.StateCode VALUES('SOUTH CAROLINA','SC');
INSERT INTO dbo.StateCode VALUES('SOUTH DAKOTA','SD');
INSERT INTO dbo.StateCode VALUES('TENNESSEE','TN');
INSERT INTO dbo.StateCode VALUES('TEXAS','TX');
INSERT INTO dbo.StateCode VALUES('UTAH','UT');
INSERT INTO dbo.StateCode VALUES('VERMONT','VT');
INSERT INTO dbo.StateCode VALUES('VIRGIN ISLANDS','VI');
INSERT INTO dbo.StateCode VALUES('VIRGINIA ','VA');
INSERT INTO dbo.StateCode VALUES('WASHINGTON','WA');
INSERT INTO dbo.StateCode VALUES('WEST VIRGINIA','WV');
INSERT INTO dbo.StateCode VALUES('WISCONSIN','WI');
INSERT INTO dbo.StateCode VALUES('WYOMING','WY');
-- US Armed Forces Geography Codes
INSERT INTO dbo.StateCode VALUES('Armed Forces AFRICA/CAN/EU/MidEast', 'AE');
INSERT INTO dbo.StateCode VALUES('Armed Forces Americas', 'AA');
INSERT INTO dbo.StateCode VALUES('Armed Forces Pacific', 'AP');
-- Canadian Postal Province Codes
INSERT INTO dbo.StateCode VALUES('Alberta', 'AB')
INSERT INTO dbo.StateCode VALUES('British Columbia', 'BC')
INSERT INTO dbo.StateCode VALUES('Manitoba', 'MB')
INSERT INTO dbo.StateCode VALUES('New Brunswick', 'NB')
INSERT INTO dbo.StateCode VALUES('Newfoundland and Labrador', 'NL')
INSERT INTO dbo.StateCode VALUES('Northwest Territories', 'NT')
INSERT INTO dbo.StateCode VALUES('Nova Scotia', 'NS')
INSERT INTO dbo.StateCode VALUES('Nunavut', 'NU')
INSERT INTO dbo.StateCode VALUES('Ontario', 'ON')
INSERT INTO dbo.StateCode VALUES('Prince Edward Island', 'PE')
INSERT INTO dbo.StateCode VALUES('Quebec', 'QC')
INSERT INTO dbo.StateCode VALUES('Saskatchewan', 'SK')
INSERT INTO dbo.StateCode VALUES('Yukon', 'YT')
GO
 
-- Query with state spelled out from StateCode lookup table
-- Select from select, correlated subquery, subselect  
SELECT Legend = 'BEST CD (dummy)',
       Bank,
       HeadQuarters,
       HQState = (SELECT State FROM StateCode WHERE HeadQuarters LIKE '%'+Code),
       CD,
       AvgRate= Rate / Years
FROM   CDRate
WHERE  Rate / Years IN (SELECT max(Rate / Years) FROM   CDRate)
GO
/* Results

Legend Bank HeadQuarters HQState CD AvgRate
BEST CD (dummy) Taunus Corp.                             New York, NY         NEW YORK 5YR CD               4.6
BEST CD (dummy) PNC Financial Services Group    Pittsburg, PA        PENNSYLVANIA 5YR CD               4.6
BEST CD (dummy) Bancwest Corp.                           Honolulu, HI         HAWAII 5YR CD               4.6
BEST CD (dummy) Zions Bancorporation                     Salt Lake City, UT   UTAH 5YR CD               4.6

 
*/
 
-- USPS State Codes - Canadian Province Codes
SELECT * FROM StateCode ORDER BY StateCodeID
GO

/* Results

StateCodeID State Code
1 ALABAMA AL
2 ALASKA AK
3 AMERICAN SAMOA AS
4 ARIZONA  AZ
5 ARKANSAS AR
6 CALIFORNIA  CA
7 COLORADO  CO
8 CONNECTICUT CT
9 DELAWARE DE
10 DISTRICT OF COLUMBIA DC
11 FEDERATED STATES OF MICRONESIA FM
12 FLORIDA FL
13 GEORGIA GA
14 GUAM  GU
15 HAWAII HI
16 IDAHO ID
17 ILLINOIS IL
18 INDIANA IN
19 IOWA IA
20 KANSAS KS
21 KENTUCKY KY
22 LOUISIANA LA
23 MAINE ME
24 MARSHALL ISLANDS MH
25 MARYLAND MD
26 MASSACHUSETTS MA
27 MICHIGAN MI
28 MINNESOTA MN
29 MISSISSIPPI MS
30 MISSOURI MO
31 MONTANA MT
32 NEBRASKA NE
33 NEVADA NV
34 NEW HAMPSHIRE NH
35 NEW JERSEY NJ
36 NEW MEXICO NM
37 NEW YORK NY
38 NORTH CAROLINA NC
39 NORTH DAKOTA ND
40 NORTHERN MARIANA ISLANDS MP
41 OHIO OH
42 OKLAHOMA OK
43 OREGON OR
44 PENNSYLVANIA PA
45 PUERTO RICO PR
46 RHODE ISLAND RI
47 SOUTH CAROLINA SC
48 SOUTH DAKOTA SD
49 TENNESSEE TN
50 TEXAS TX
51 UTAH UT
52 VERMONT VT
53 VIRGIN ISLANDS VI
54 VIRGINIA  VA
55 WASHINGTON WA
56 WEST VIRGINIA WV
57 WISCONSIN WI
58 WYOMING WY
59 Armed Forces AFRICA/CAN/EU/MidEast AE
60 Armed Forces Americas AA
61 Armed Forces Pacific AP
62 Alberta AB
63 British Columbia BC
64 Manitoba MB
65 New Brunswick NB
66 Newfoundland and Labrador NL
67 Northwest Territories NT
68 Nova Scotia NS
69 Nunavut NU
70 Ontario ON
71 Prince Edward Island PE
72 Quebec QC
73 Saskatchewan SK
74 Yukon YT

 

*/
-- Cleanup
DROP TABLE dbo.StateCode
DROP TABLE CDRate
GO
------------

Exam Prep 70-461
Exam 70-461