It’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.