By the Hearing of Faith

Found the following equation in a probability book (so q=1-p):

p(1 + q + q² + q³ + …) = p(1/(1-q))

Er … wha? ‘Splain this to me.

Scientific American says that “our brains have a map for numbers” ….

Query:

SELECT fname, lname, hire_date
FROM EMPLOYEES
WHERE DATEDIFF(yy, hire_date,GETDATE()+30) > DATEDIFF(yy, hire_date,GETDATE())

Function: Returns the name and hire date of all employees whose hire-date anniversaries fall within the next thirty days. The DATEDIFF() method returns the difference, in units given by the first parameter, between the second and third parameters. So if the difference in years between an employee’s hire date and thirty days from now exceeds the difference in years between the employee’s hire date and today, you know that employee will have a hire-date anniversary within the next thirty days. For example, if today is 6 Jan 2014 (as it is), then GETDATE()+30 = 5 Feb 2014. If an employee was hired on 10 Jan 2012, (DATEDIFF(yy, 10Jan2012,5Feb2014) = 2) > (DATEDIFF(yy, 10Jan2012,6Jan2014) = 1). If, however, an employee was hired on 10 Feb 2012, (DATEDIFF(yy, 10Feb2012,5Feb2014) = 1) !> (DATEDIFF(yy, 10Feb2012,6Jan2014) = 1), and if an employee was hired on 2 Jan 2012, (DATEDIFF(yy, 2Jan2012,5Feb2014) = 2) !> (DATEDIFF(yy, 2Jan2012,6Jan2014) = 2).

Source: Henderson, Ken. The Guru’s Guide to Transact-SQL. Reading, MA: Addison-Wesley, 2000. Print. (p. 25)

Comments: Most database engines will already have an API in place to keep you from reinventing the wheel and, in so doing, crushing your own foot. Good to know, isn’t it? Have a pleasant and productive day.

Following Mr. Smith’s bogus data led me down gloomy and tortuous paths to an illegal smoke-filled basement casino in Chinatown at 3 AM. The place was packed, but this time I at least had a photo:

Mr. Smith’s unblinking psychotic gaze mocked me from behind his unfashionable Harry-Potter eyewear, and his left hand seemed eternally poised to strike anyone who stood athwart his evil plans. I figured he wouldn’t be hard to spot ….

Query:

SELECT fname, lname, hire_date
FROM EMPLOYEES
WHERE CAST(CAST(YEAR(GETDATE()) AS varchar(4))+
SUBSTRING(CONVERT(char(8), hire_date,112),5,4) AS datetime) 
BETWEEN GETDATE() AND GETDATE()+30

Function: Attempts to determine hire date anniversaries for all employees that fall within the next thirty days (the anniversaries, not the employees). Starting with line 3, this query extracts the current year and casts it as a varchar(4). Then in the following line, the query converts each hire_date value to style 112 (which is yyyymmdd) and then trims out the mmdd as a substring (start at character 5 and use the 4 characters that follow) and casts it as datetime (the outer cast statement of the preceding line). So it constructs a date from the hire_date column which would be the annual hire anniversary for each employee and then only selects it for display in the query results if it falls between the current date and 30 days hence. The synthesized date might not be valid, however, because the employee might have been hired in a leap year.

Source: Henderson, Ken. The Guru’s Guide to Transact-SQL. Reading, MA: Addison-Wesley, 2000. Print. (p. 25)

Comments: Though we might be tempted to lose patience with Mr. Henderson for presenting us with a flawed query, we’re rewarded–should we read on–with a noble attempt to build gender equality through words: “If the employee was hired in a leap year and the current year isn’t also a leap year, you’ll have a problem if her hire date was February 29″ [emphasis mine]. I’ll bet you chauvinistically assumed this hypothetical employee was male, didn’t you !?! Admit it. You just don’t much care for the third-person singular feminine possessive pronoun … and how it watches Lifetime Network, prefers white wine to beer, and talks openly about its feelings. Well, Ken Henderson’s prose hearkens that THE TIMES THEY ARE A CHANGIN’, pal. And so on and so forth. Yeah ….

Query:

SELECT t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Hi-Keeba%'
ORDER BY SchemaName, TableName

Function: Finds any value in any column in any table in a database.

Source: *sigh* …. I honestly don’t remember.

Comments: If you’re dealing with an enormous industrial-strength data jackhammer with hundreds of tables and millions of rows, this query can take a while to run. But you might have to use it.

Query:

SELECT name FROM Deep13.sys.tables WHERE name like '%Forrester%'

Function: Find all the tables in the database Deep13 with the string Forrester in the name.

Source: Me. This is helpful if you’re looking around a database with hundreds/thousands of tables for a clue about where relevant data might be.

Comments: I need a neologism for the phenomenon where you start entering your password at a blank Windows terminal, and by the time the screen emerges from hibernate mode, you realize you forgot to enter your login and have just been typing your password in the Windows “User name” field for everyone in the office to see. And then you realize you’re back in 8th-grade geometry class with no pants on ….

Query:

CREATE TABLE EMPLOYEES (
enumber int NOT NULL,
lname char(30) NOT NULL,
fname char(30) NOT NULL,
hire_date datetime NOT NULL)
GO
INSERT INTO EMPLOYEES
VALUES(1,'Bot','Cam','1988-11-24 09:29:01.303')
GO
INSERT INTO EMPLOYEES
VALUES(2,'Sy','Gyp','1988-11-24 09:29:02.303')
GO
INSERT INTO EMPLOYEES
VALUES(3,'Servo','Tom','1988-11-24 09:29:03.303')
GO
INSERT INTO EMPLOYEES
VALUES(4,'Robot','Crow T.','1988-11-24 09:29:04.303')
GO

Function: Create sample data so we can run subsequent queries on datetime data … instead of just writing queries on tables that don’t exist, like a certain self-proclaimed T-SQL guru.

Source: Me. Although I wrote this so that I could run the datetime queries in The Guru’s Guide to Transact-SQL, by Ken Henderson … who neglected to include any DDL for the EMPLOYEE table. (Yeah, I know my table’s named differently. Sue me.)

Comments: There are four employees at this organization. All of them are robots, and all were hired within one second of each other, at the moment of roll call. If you don’t get why this is awesome, you are a lot less happy than you could be. I hope that you realize why … in the not too distant future. Maybe next Sunday, A.D.

I once took a class in Uncreative Writing. We mostly used phrases like “Contains polydihydrogenated monosulfinaluminated xanthanascorbinalates.”