SQL Notes

I'm starting a table of conversions.  Since I'm now working in 3 SQL dialects I need to keep them straight!

DateDiff

datepart	Analytics	MS-SQL	J-SQL
year	yy, yyyy	yy, yyyy	yyyy
quarter	qq, q	qq, q	q
month	mm, m	mm, m	m
dayofyear	dy, y	dy, y	y
day	dd, d	dd, d	d
Weekday	dw, w		w
week	wk, ww	wk, ww	ww
hour	hh	hh	h
minute	mi	mi, n	n
second	ss, s	ss, s	s
millisecond	ms	ms
microsecond	mcs	mcs
nanosecond		ns

Wildcards:

Description	MS-SQL	Jet SQL
Any String of zero or more characters	%	*
Match one character	_(underscore)	?

http://www.techrepublic.com/article/10-tips-for-using-wildcard-characters-in-microsoft-access-criteria-expressions/

ShareInformatics - UNICARE Pro-Filer - Business Analysis - Data Management: Crystal Reports® Tips and Tricks: Creating a Green Bar Effect

ShareInformatics - UNICARE Pro-Filer - Business Analysis - Data Management: Crystal Reports® Tips and Tricks: Creating a Green Bar Effect

Excel - Making a Chart behave - Trendlines, copy format, stacked chart.

These are some personal notes, but if you find them useful then go for it!

In Excel, you cannot take a stacked chart and add a trendline to it.  But I wanted to.  I had resorted to using Paint in the past, but this particular chart was going to be rather complex, and I had several of them to build, so I wanted to figure a "better way (TM)".  Here's what I did in Excel 2010.

Select your data and create the stacked chart.  That is the easy part.  For a stacked chart, you would normally have groups with quantities such as this:

Month	Widget 1	Widget 2	Widget 3	Total
March	26	6	13	45
April	21	9	11	41
May	25	7	12	44

Create the chart without using the totals column.

Right click on the chart and "Select Data".  In the "Legend Entries (Series)" pane click on "Add".  Select "Total" for the Name, and add the "Total" data to the Series Values.  Now, the "Totals" are stacked on top of the other data and you should have "Total" in your Series pane.  Click "OK".

Right click on the "Totals" data and select "Format Data Series".  On the "Series Options" tab click the "Secondary Axis" radio button under "Plot Series On".  The "Totals" data will now be hiding the stacked data.

Right click again on the "Totals" data and "Change Series Type" to a regular column.  Click the "Layouts" tab on the Excel Ribbon, click the "Totals" column, and now "Trendline" should be an option.  Select the trendline you want to use, in my case a linear trendline.

Now you have the trendline, but your stacked data is hidden.  Right click again on the "Totals" series and click on "Format Data Series".  Change the "Fill" to "No Fill", change the "Border Color" to "No line", click "Close.

There you go!

Now to copy this to multiple charts without having to walk thru all of the steps.  Begin by creating your stacked chart, but this time include the totals column.  Right click on the first chart and "copy".  Click on the new chart, and, on the Ribbon, under the "Home" Tab, click on the little arrow under "Paste" and select "Paste Special.  On the dialog box, select the radio button for "Formats".  All done!!


Credit to mrexcel.com and techrepublic.com for the tips that I put together to create this.

What do I do?

I often get asked what I do at St. Joe's and find it difficult to explain.  I work as a System Support Admin, which basically means that I boss a computer around :).  I'm also a sort of Systems Analyst, which means that I look at numbers a lot.

I'm part of a team working for the CHI Franciscan Heart Center.  The team follows patients that have heart procedures at any of our Franciscan facilities, and we collect data.  What sort of data?  Well, just about anything that might have some sort of impact on their disease, on their care, on their health, on their outcomes.  How much anesthesia, how much they weigh.  And it is all used for research, to help us provide better care for them and for future patients.  If something doesn't work, we tell the doctors and surgeons about it so that they don't do that again.  If something works better, we help make certain that the technique gets used more often.  We help keep an eye on things to make sure that patients are getting appropriate medications and treatments at appropriate times and places.  We try to encourage the doctors to give patients all the tests they need for proper diagnoses, and to not order tests that are not helpful.

My role is to aggregate and report on the data that the team enters into our database.  At any time, an administrator or physician may want to know about a specific data point; I get them the answer.  How many patients had multiple bypass last month?  I can answer that question.  How many cardiac patients were prescribed statin drugs last month?  I can answer that question.  How many patients last week did NOT get all of their meds on time?  I can answer that question.   How many patients two days ago did not get appropriate ventilator weaning?  I can answer that question.  What are the chances for successful outcome of a specific surgery on a specific patient?  With the right data I can give you an answer.

Besides using our data internally for our own research, I also submit data to the National Registries such as the Society of Thoracic Surgeons and the American College of Cardiology.  These are databases that collect all of the data on all heart procedures done across the U.S. for use by researchers. There is also a Washington State database that I report to.

The data from these Registries are all held to a high standard.  Every few years we are audited for accuracy and completeness by outside firms.  We also participate in user groups that audit each other, in an attempt to make sure that we are all collecting the same data in the same way, so that we are comparing apples to apples across the nation.

For 20+ years this data has been mostly looked at by students and physicians, but the last couple of years some of this data has been coming to light in public places.  Because of the reliability of the data, Insurers are using this data to determine the quality of programs, and then recommending good programs to their clients.  And now, Consumer Reports is also using this data to report directly to the public (YOU!) so that you can choose the best programs for your needs.  Here are a couple of recent news items that demonstrate the work that our team puts out.

Blue Distinction Certification

Consumer Reports

Download Ratings published by CR

Be informed!!

Dog Pack attacks Gator in Florida

Be sure to read to the end - it's surprising!

At times nature can be cruel, but there is also a raw beauty,
and even a certain justice manifested within that cruelty.

The alligator, one of the oldest and ultimate predators, normally considered the "apex predator", can still fall victim to implemented 'team work' strategy, made possible due to the tight knit social structure and "survival of the pack mentality" bred into the canines.

See the remarkable photograph below courtesy of Nature Magazine.

Note that the Alpha dog has a muzzle hold on the gator preventing it from breathing, while another dog has a hold on the tail to keep it from thrashing.

The third dog attacks the soft underbelly of the gator.

Not for the squeamish...

 

GOTCHA!!!

Laughter is good for the soul...

 

Go get your Gators!!

The Nine Billion Names of God

The Nine Billion Names of God is a short story written by Arthur C. Clarke back in 1953.  It tells a story of a group of Tibetan Monks who hire IBM to install a mainframe to print out the Nine Billion Names of God; they believe that the world was created to discover the Nine Billion Names and that when the purpose of the world was completed the world would end.  I’m not going to tell you the rest of the story, you will need to find it out yourself.

But I don’t want to talk about the god of Tibetan Monks.  I want to talk about the God of the Judeo-Christian Traditions.  “God” is a generic term for a deity, or a power mightier than man who interacts with man. 

There is a ton of speculation about the origin of the word “god”. http://en.wikipedia.org/wiki/God_(word) has quite a bit on the subject. 

Personally, while I agree that the idea that our English “god” comes from the Germanic “gott”, I think that there is a deeper undercurrent to the meaning.  In the article on “Gad” in the Jewish Encyclopedia  it is mentioned that Gad is the name of the Canaanite god of Fortune, pronounced “gawd”.  And today, we wish each other “Good Luck”, or we say “how fortunate” or “how lucky” a person is, which is similar to saying “Gad be with you”.  Of course, “luck” is Loki, the Norse god of fortune, “fortune” is the Roman goddess “Fortuna”. 

Where this all started for me was a couple of years ago in discussion with an acquaintance about “God”.  This acquaintance was arguing that since I worship “God”, and Muslims worship “God”, and members of the Church of Latter Day Saints worship “God”, and many others claim to worship a “God” or “gods” that we should all just get together and worship “god” together, or better yet, just live and let live and let each one worship his or her own “god”: the way they want to.  I mean, like, you know, its all the same anyways, right man?

Well, no, it isn’t all the same, at least not to me.  See, my “God” requires me to worship him a certain way, and not only that, my “God” claims to be the only true “god” and claims that all others are not really “gods” at all.  If only one “god” said that then it would not be a problem, but practically every “god” around makes similar claims.  So now what do I do?

I have settled to my own satisfaction that the “God” I worship is, for me, the true “God”.  I’m not going to go into the details here and now, but for a good place to start you can try “Mere Christianity” by C.S. Lewis.

In the Hebrew Old Testament, or Tanakh, what is translated usually as “God” is usually the Hebrew for “Elohiym”; which may be loosely translated as “Mighty One”.  In Genesis 1:1 it says that Elohiym created the heaven and the earth.  Some scholars think that Elohiym is the plural for Eloahh and that Elohiym should be translated as "Mighty Ones"; there is great debate on this matter.  As near as I can tell it is all speculation and no-one really knows for sure; much data has been lost about the Hebrew language.  Although, considering how little we know about other languages from 4-5,000 years ago, we know quite a lot about the Hebrew language.  Interesting, is is not?

In Genesis 2:4 is the first appearance of what is sometimes called the Tetragrammaton, or The Name, or YHWH, or Jehovah, or as I read it, Yahuah (ya HOO ah).  For arguments on the pronunciation of the Tetragrammaton see the article in Wikipedia or in the Jewish Encyclopedia.

So, as someone else might say that their God is Allah, I will say that my Elohiym is Yahuah, because I believe, worship, and trust the Hebrew deity.  Depending on my audience, I may say “God Bless you” or I may say “Elohiym bless you”.  I may say “Praise the Lord” or I may say “HalleluYAH”.  However, in my own personal worship and prayer, I normally use the Hebrew names.  And I will rarely tell a person "Good Luck" or say how lucky someone is, because to me, that is a referent to a different god.

Word Study #1 - 27 March 2014

I’m going to start a series of posts, we’ll see how long I can keep them up.  Probably post once or perhaps twice a week.

I don’t claim to be a teacher, although I do enjoy teaching.  I prefer to call myself a student, because I like to study.  And, one of my favorite things to study is the Bible. 

What I am going to attempt to do is share some of the things I am learning.  I don’t claim these words to be the doctrine of any specific church or body of believers, although I think that some believers have held at least some of these beliefs at various times in history.  I don’t claim that any of these are correct, just that this is what I personally have come to believe.  Your mileage may vary.  Objects in the rearview mirror are closer than they appear.

The point of my study for the last couple of years was sparked by a person that saw me reading the Bible and asked me to tell him about what I was reading (he was functionally illiterate).  I began reading to him, and I don’t think I made it thru half a verse before he interrupted me to ask what “that” word meant.  I shortly realized that I wasn't certain myself what that particular word meant, which lead to the realization that most ‘religious’ people use buzzwords and that they have no idea of the real meaning behind those words.  This is true of many groups; as a computer geek I can use words that sound big and impress many, but usually I’m careful to know what II'm talking about.

One of the first things that I had to learn was that the Bible wasn't written by an American, or an English speaking person, or even a member of modern western civilization.  For the most part the Bible was written by members of a backwater tribe of middle eastern nomads over a period ending no later than 2,000 years ago and beginning perhaps 3,000 years before that.  The closest that I can imagine comparing them to is the indigenous North American peoples such as the Sioux or the Apache or the Dakota people.  They owned no property.  The family was the nucleus of the society.  Survival of the family came before anything else.  They didn't go to the supermarket or McDonald’s when they got hungry, in fact, for much of their history farming was unusual.  They hunted and gathered.  As nomadic shepherds, they lived from one good grazing spot to another.

The ancient Hebrew language reflected that lifestyle.  It is full of words that describe the concrete day to day struggle for life   One example:  The past is known, the future is unknown.  The Hebrew lived his life with his past always before him, he could see it.  The future was behind him, it was unknown.  This is a very fundamental change in outlook from what the modern day member of western civilization has.