Upper and Lower case in SQL SERVER

Working in SQL SERVER 2012 (meaning, I haven’t tried this in other version),  I found that a huge table of gis data had 49 rows with lower case postal codes.

As the data I would be using to find these would be in upper case,  I chose to convert these to upper case instead of doing it in the query,  because that would increase the resources to run said query (and I would forget to do that at some point down the road).

To do this,  I used the SQL Server functions UPPER and LOWER.

To find columns that are either in all upper or lower case,  you simply compare the contents of the column to what the UPPER or LOWER function returns.

To find the lower case codes,  this was the query I used


SELECT
*
FROM
geolocation
WHERE
code = LOWER(code)       //this is the bit that finds the lower case fields

As I ran through the steps to convert these to upper case,  I initially used the condition

code = LOWER(code)

in the UPDATE statement.

To my chagrin, there were still 2 rows that weren’t updated.

Both of them had an uppercase character.

To get around this,  I changed the condition to

code != UPPER(code)

which worked fine.

The lesson here is that when you are converting a column to a desired case,  use the target case as one of your conditions.

…which makes perfect sense when I type that out loud.

Here’s the code


UPDATE
geolocation
SET
code = UPPER(code)
WHERE
code= UPPER(code)
Advertisements

Dealing with Meditech Dates (including Clinical formats) in SQL (Meditech, iPeople Echo, SQL Server)

For those regular followers of this blog who don’t realize it,  I work in healthcare.

While I usually don’t post industry specific things here,  I’m going to start as one of the purposes of this blog is to keep track of things I learn,  that I’ll probably forget as I move onto the next project.

Those of you are are familiar with Meditech know that there are many instances where dates are stored in a “Clinical Date” format.   That is,  the number of days since 3/1/1980.

We recently started using iPeople Echo,  which we purchased as part of their Connect suite, to copy data from Meditech’s proprietary Dictionaries (the Meditech term for tables) into SQL server.

When querying the data,  most of the dates were in the Meditech standard for Canadian sites (YYYYMMDD).

When writing a sql query that involves dates,  its often necessary to convert the Meditech date to a SQL datetime datatype.

The CONVERT function does this,  the trick is knowing the correct format.

Here’s an example where it converts the ADMITDATE to a SQL date type.


CONVERT(date,a.ADMITDATE,112)

The parameters are

CONVERT([data-type(date)],database field,format)

The format 112 indicates  YYYYMMDD

Here is a reference on format codes. 

The Meditech clinical date format is trickier to deal with as it represents the number of days passed since 3/1/1980.

If you’re writing a query that requires you to handle clinical dates,  its best to use a Stored Procedure so that you can do the conversion once,  and use the results in your actual query.  I’ll post a live example below,  but for now,  here’s the base query.


CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int)

In order to use the result of this statement in a WHERE clause pulling from iPeople DR,  it needs to be converted to a number.

As you’ll see in this partial example row from our Radiology Exam Information table,  the field ‘EXAMDATES’ is a number. (and, for some reason, a plural)

RAD EXAMDATES EXAMQ EXAMENTEREDSECONDS EXAMACCOUNT EXAMACRCOMMENT1 EXAMACRCOMMENT2
D10027 9285 1 802279697 RS4X000115 NULL NULL

When looking for a range of dates,  user’s prefer to be able to enter a date range that makes sense to them,  its up to as, as programmers to accommodate this.   (and to make our own lives easier while testing queries)

If you use the above code fragment to convert the examdate field above your code would look like this


SELECT
CONVERT(varchar(12),DATEADD(DAY,CONVERT(int,a.EXAMDATES),'3/1/1980'),106)

The result would be  02 Aug 2005,  or using the 112 format 20050802

I mentioned stored procedures earlier and will post an example,  but it will require some explanation.

The following stored procedure was written for a project that gives users geographic information (primarily driving distance to each of our facilities) for patients who presented for certain types of radiology exams.

The information is going to be used to locate new equipment and tweak service delivery.

Seeing as I need to query the radiology exam table used as an example above,  I wanted an easy way to set date ranges without doing a lot of work in the PHP script that calls this stored procedure.

The stored procedure runs on another SQL Server instance,  with the iPeople Data server added as a linked object.  You’ll see in the query how I reference those tables.

I do this so I can write stored procedures while not affected the actual iPeople database,  and, so I can use custom tables to contain select data (so I don’t have to change the query if exam codes change),  as well as store geographic data such as lat and long for each postal code.

You’ll also see, that I use the Meditech date format YYYYMMDD  as the procedure parameters (@startdate,@enddate) and then convert them to the clinical date format (@from and @end)  prior to the select running.

The @from and @end variables are used in the WHERE select to limit the results to the desired date range.


ALTER PROCEDURE [dbo].[radpatientsforgis]
@startdate varchar(8),
@enddate varchar(8)
AS

DECLARE @from int
DECLARE @end int

SET @from = CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int)
SET @end = CAST(DATEDIFF(day,'3/1/1980',CONVERT(date,@enddate,112)) as int)

SELECT
--b.NAME as name,
a.EXAMACCOUNT as account,
a.EXAMMNEMONIC as exam,
a.EXAMNUMBER as number,
REPLACE(b.ZIP,' ','') as zip,
c.lat as lat,
c.long as lon,
CONVERT(varchar(12),DATEADD(DAY,CONVERT(int,a.EXAMDATES),'3/1/1980'),106) as dates
FROM
IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyExamInformation as a
INNER JOIN
IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyPatient as b ON a.EXAMACCOUNT = b.ID
INNER JOIN
geolocation as c ON REPLACE(b.ZIP,' ','') = REPLACE(c.code,' ','')
INNER JOIN
radexamcodes as d ON d.exmnemonic = a.EXAMMNEMONIC
WHERE
a.EXAMDATES >= @from
AND
a.EXAMDATES <= @end
AND
b.ZIP IS NOT NULL
AND
d.active = 1

A sample result is below with altered data

account exam number zip lat lon dates
XXXXXX MAMSCBIL xxxxx LNLNLN 83.3691 -64.8376 04-Mar-15

Note: I did not receive any consideration from iPeople for this post, or the link.  I have no issues with letting people know about products and applications that make my life easier.

SQL SERVER: Conditional selects

In a recent project I needed to come up with a way to return information from one of two tables, based on whether or not the value of the first column was null.

In situations like this,  the SELECT CASE statement is the best option.

This is the syntax:


(

SELECT CASE WHEN

[condition]

THEN

[return if true]

ELSE

[return if false]

END

)

Its important to know that you can use SELECT CASE in both the selects AND the WHERE CLAUSES of a sql statement.

Here is a full query,  where,  if the person’s address is null in table a,  it will provide the address from table b.

You would use the same syntax in the WHERE CLAUSE.


SELECT

a.name as Name,

(

SELECT CASE

WHEN

a.address IS NULL

THEN

b.address

ELSE

a.address

END

) as Address

FROM

users AS a

INNER JOIN

userdata AS b ON a.UID = b.UID