How was it done back in the day? T-SQL Tuesday #114

2

May 14, 2019 by Kenneth Fisher

T-SQL TuesdayIt’s TSQL Tuesday and Matthew McGiffen (b/t) is our host with a subject near and dear to my heart. Puzzles! Thanks for coming up with this great subject Matt!

As Matt mentioned puzzles are something I do anyway. In fact, I have a whole page of puzzles and other fun things to do. Since I had done so many crosswords and other types of things, I decided this time I would do a more traditional SQL puzzle. As I thought about what to write, I remembered a couple of puzzles I had been given during some interviews. Now, SQL has improved quite a bit from when I was given these so we are going to have to have a few rules.

Pagination

The year is 2004. You’re taking a tech test as an interview for a SQL development job. They have a page in their application that displays up to 20 rows of information. They need a piece of code that will return the rows from a given page. Oh, and it may not always be 20 rows per page. You need to write a piece of code where they can pass in a page number and page size and get back results. So for example, if the page size is 20 and the page is 3 then you need to return back rows 41 to 60.

Requirements

  • Return a page of information from spt_values.
  • Order is by the column name.
  • There must be a variable or parameter for page number and one for page size.
  • No windowing functions. No OFFSET/FETCH, no ROWNUMBER(). This is 2004 they don’t exist yet.
  • The last page is of couse, just however many rows are left. So in the example above if there are only 55 rows total, the 3rd page returns 15 rows.

 


Dates and times to numbers and back

It’s a few years later and you are looking for a job again. This time you are given an actual request from a client. It’s one they’ve already solved but they want to see how you would do it. A piece of it (and one of the harder bits at that) is the fact that they are storing their datetime data as two integers. So the datetime ‘5/14/2019 8:00AM’ would be two integers. 20190514 and 80000. And ’12/4/2019 00:00:02AM’ would be 20191204 and 2.

Requirements

Given the table below, combine the two integers back into a datetime value.

CREATE TABLE DateTime_Ints (int_date int, int_time int);
INSERT INTO DateTime_Ints
SELECT CONVERT(int, CONVERT(varchar,MyDate,112)),
		CONVERT(int, REPLACE(CONVERT(varchar,MyDate,8),':',''))
FROM (VALUES (CAST('1/1/1960 12:00:00' AS DATETIME)), ('12/5/1981 00:00:04'),
			('5/30/1963 22:14:30'), ('8/17/2001 00:20:10'),
			('10/31/2008 9:00:00'), ('11/2/2019 04:00:13'),
			('2/25/1983 00:09:04'), ('1/21/2000 19:30:00'),
			('4/12/2010 10:10:10'), ('4/4/2004 04:04:04')
	) AS DateList(MyDate);

Remember that this is pre 2008 so no DATE or TIME datatypes.


And if these are too easy, or you are just having fun, pick one of the newer commands (STRING_AGG is a really popular one for this) and figure out how the same task was done before that command became available.

2 thoughts on “How was it done back in the day? T-SQL Tuesday #114

  1. […] Kenneth Fisher shares a couple of interview puzzles: […]

  2. […] Kenneth Fisher asks us to solve some query problems using older versions of SQL Server – so we can appreciate just how much easier our lives are these days… https://sqlstudies.com/2019/05/14/how-was-it-done-back-in-the-day-t-sql-tuesday-114/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,513 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: