Computed Columns MS SQL

A table with a Totals column is a perfect use case for computed columns in MSSQL. Making the Totals column a computed column allows MSSQL to do all the preassigned math for me, eliminating the need to write SQL or ColdFusion code to sum up the monthly totals. Every time I update the table I would have to run other code or SQL to again obtain the correct total.

Here's my table, MonthlyPay.

view plain print about
1SELECT [ID]
2 ,[Year]
3 ,[January]
4 ,[February]
5 ,[March]
6 ,[April]
7 ,[May]
8 ,[June]
9 ,[July]
10 ,[August]
11 ,[September]
12 ,[October]
13 ,[November]
14 ,[December]
15 ,[Totals]
16 FROM [MonthlyPay]

By turning Totals into a computed column, my life is easier. Here's how:

view plain print about
1ALTER TABLE [MonthlyPay] DROP COLUMN [Totals];
2GO
3ALTER TABLE [MonthlyPay] ADD [Totals] AS (
4[January] + [February] + [March] + [April] + [May] + [June] + [July] + [August] + [September] + [October] + [November] + [December]) PERSISTED;

I could add even more calculations including the math to get tax percentages and including those in the total. So much easier!

Combining MSSQL Functions and Wildcards to Filter Data

We can combine SQL functions and use "LIKE" to filter data searching a string in a column. We are not using RegEx, just simple pattern matching. Here's our practical example.

We are given a table called "CityYear" with a column, "Title" that has this pattern "[city name] ([year])", e.g.;

view plain print about
1Title
2Los Angeles (2015)
3Los Angeles (2016)
4Los Angeles (2017)
5Los Angeles (2018)
6Denver (2015)
7Denver (2016)
8Denver (2017)
9Denver (2018)
10Dallas (2015)
11Dallas (2016)
12Dallas (2017)
13Dallas (2018)

Let's say we just want to pull the rows containing data for the current year only (2018 at the time of this post).

The first attempt could look like this:

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '% + YEAR(GETDATE() )+ %'

However this returns no results. This is because we are matching to a string that literally contains "+ YEAR(GETDATE() ) +". Trying the following:

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'

Gives us an error: Conversion failed when converting the varchar value '%' to data type int.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'

The above query returns exactly what we want:

view plain print about
1Title
2Los Angeles (2018)
3Denver (2018)
4Dallas (2018)

The following query narrows down possible variations even more by conforming to the pattern and adding parenthesis to the comparison parameter.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) as varchar(4)) + ')%'

If we want to find the previous year (in this case, 2017), we subtract from the YEAR(GETDATE()) function.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'

view plain print about
1Title
2Los Angeles (2017)
3Denver (2017)
4Dallas (2017)

Select Distinct and Return Multiple Columns

How does one select a distinct column value and still bring back all other associated columns for the distinct value?

In this example we have repeated [Requisition_Number]s and wish to find distinct values, but we still want the other columns returned also.

This ignores the DISTINCT and returns all the rows:

view plain print about
1SELECT [ID]
2        ,DISTINCT [Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM [Requisitions]
and is NOT what we want.

This returns JUST the distinct values for [Requisition_Number] and columns associated with those values.

view plain print about
1SELECT     [ID]
2        ,[Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM (
13SELECT     [ID]
14        ,[Requisition_Number]
15        ,[Status]
16        ,Title]
17        ,[Code]
18        ,[UID]
19        ,[Full_Name]
20        ,[City]
21        ,[State]
22        ,[Requisition_Justification]
23        ,[Date_Requisition_Opened]
24        ,ROW_NUMBER() OVER(PARTITION BY [Requisition_Number] ORDER BY [ID]) RN
25    FROM [Requisitions]) R
26    WHERE RN = 1

not equal to null not working

In MSSQL, looking for rows that are not equal to a certain value does not return rows with null values. To retrieve all values that are not equal and are null, you have to add both clauses "not equal" and "is null" to return all values not equal.

In SQL checking for NOT EQUAL ("!=" or "<>") will not return NULL values. NULL has no value and cannot be compared using scalar value operators.

I had to change my WHERE clause from:

view plain print about
1[whatever] != 1
To
view plain print about
1([whatever] != 1 OR [whatever] is null)

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner