Contact Me
Resources
-
Category Archives: SQL
Getting Averages by removing outliers
I had an interesting challenge this week. I needed to get average sales by product but the boss wants to eliminate the top 10% and bottom 10% so she can get a better picture of the actual sales values. We … Continue reading
SQL – PATINDEX and CASE
I had the odd challenge of providing an account number by removing any values that were the same except for numbers at the end. Examples custname01, custname02, custname03, etc. Since these accounts are all really the same customer we need … Continue reading
Posted in Problems & Fixes, SQL
Comments Off on SQL – PATINDEX and CASE
CFSelect bind for Search or Edit on same form
Creating a data based derived dropdown list bound to a record for Search or Edit form is sometimes needed. For search you may want to return All as your first item in the dropdown, but for edit mode you likely … Continue reading
Posted in Code, Coldfusion, Snippets, SQL
Tagged Binding data, cfselect
Comments Off on CFSelect bind for Search or Edit on same form
Date Subtraction or Difference
Sometimes you need to know the differences between two dates in days months years whatever. SQL SERVER select datediff(day,date1, date2) as days_diff, datediff(month,date1, date2) as months_diff… Can also do year, quarter, week, hour, minute, and second dateparts. Dates can be … Continue reading
Posted in Code, Coldfusion, Javascript, JQuery, Snippets, SQL
Tagged dates
Comments Off on Date Subtraction or Difference
SQL using case to Identify blanks vs NULLs
I had a strange issue driving me nuts today. I had data with null values and could use isNull to capture most of them and assign a new value to the field. However about 10 records would not accept the … Continue reading
Posted in Code, Snippets, SQL
Tagged Blanks in Data, Null Data, Null vs Blanks, SQL Case
Comments Off on SQL using case to Identify blanks vs NULLs
