Forem: Steve Sohcot

SQL Server Loop Example

Steve Sohcot — Fri, 18 Nov 2022 17:33:35 +0000

I needed to run a LOOP statement in SQL. Normally I would do this with website server-side code (ex. PHP or Node.js), however this was needed for data analysis that I otherwise didn’t have a need for writing web-based code.

I needed a solution compatible with MS SQL Server Management Studio ("SSMS").

Why even do this?

I had a database table with millions of rows and I needed to create a table with a subset of this data.

To create the table, I needed to run several INSERT queries; each having only slightly different WHERE criteria.

By using a LOOP, I could dynamically create the table- and have a repeatable process as it would have to be done often.

How to write a loop using SQL

Here’s the code, explanation is below.

Let’s start with some fake data.

Rows 1–5 create a temporary table
Rows 7–13 populate this table with data
The value passed into my WHERE criteria (in my eventual INSERT statement) would be dynamically changing as it loops. The value will be stored in a variable called theOrg, declared on line 17.
I’ll need to loop through a finite number of rows: starting at “1” (Row 18), and up through the number of rows in my temporary table (Row 19)
Rows 21–29 is the actual loop:
Row 24 gets the specific value that I’ll ultimately be querying on, based on a SELECT query using the variable cursorId
Row 25 is where I could run an INSERT statement to generate the table, although here I’m using a SELECT statement for demonstration purposes
Row 27 increments the variable cursorId so that the loop will continue
Running it in SSMS, you’ll see this:

Of course, now I can swap out the SELECT statement (Row 25) and use my real query (with an INSERT statement) to generate my dataset.

MS Access Slow Linking Between ODBC and Local Tables? Then Don’t Do It.

Steve Sohcot — Fri, 04 Nov 2022 04:00:00 +0000

Often when analyzing data from a SQL Server table via MS Access, I’ll create a local table to specify the values to query. I’ll then link the local table to the SQL Server table (via INNER JOIN).

I’ve noticed, at least in recent versions of MS Access, that when you link a local table with a remote table (ex. ODBC) it’s incredibly slow. Often my MS Access will freeze up, making me think the application will crash.

I’ve been using MS Access for years, I’ve only noticed this as an issue “recently” — I’m currently using Microsoft 365 Apps for enterprise.

How To Avoid Local Tables in MS Access

The obvious way is to put your data into another remote (SQL Server) table.

My suggested approach is a little faster: rather than creating (or modifying) a new table every time, add WHERE criteria.

As a web developer, I use an IDE (specifically Sublime Text, but this also works with VS Code). You can select multiple lines by clicking the mouse wheel, and then move the cursor over (via “Home” and “End” keys) to surround the text in a single quote and append the word “or”:

You could also do this with an Excel formula:

Then query the (one) SQL server table and specify additional criteria:

This approach may not be reasonable for “a lot” of data, but if you’re querying for a handful of items it may be a viable alternative.

This technique greatly speeds up the time it takes me to query a SQL Server table via MS Access when performing data analysis.

Two Ways To Create Totals In SQL

Steve Sohcot — Mon, 20 Jun 2022 00:25:16 +0000

When presenting tabular data (i.e. data in a table), you may want to have a “total” row at the bottom.

I remember (~25!) years ago I took the approach of creating a multi-dimensional array to sum a value, row-by-row, and column-by-column. Both server-side and in JavaScript.

It’s more efficient to create a “total” row in SQL (server side). It’ll also make it easier for the front-end developer.

To start with, let’s assume we have a sample query like this:

Results:

Method 1: Creating a SQL Total with UNION ALL

The first approach I’ll mention is to basically create a copy of your dataset:

In the copy, exclude the first column that displays the different values
Hard-code the word “Total” to ensure that the same number of columns appear in each dataset
Join the two datasets together via UNION ALL
Wrap both together in a surrounding SELECT statement

Remember to remove the ORDER BY within the sub-queries.

We now have a “Total” row, but it doesn’t appear at the bottom of the dataset:

We can fix this by updating our ORDER BY clause to include a CASE statement:

Explanation: Where ever the column says “Total” give it a value of 1, and all other entries are 0. Therefore, Total will always be at the end. As a secondary sort, use the other field (here, “agent”).

Now we have the Total at the end:

Method 2: Creating a SQL total with ROLLUP

This may be specific to the version of SQL you’re using. I know it works with MS SQL Server

You can simply add the function ROLLUP immediately after the GROUP BY

Be sure to include the parenthesis

This works, but you’ll see that it shows a value of NULL:

Again, to make it easier for the front-end developer, we can resolve this by replacing NULL with “Total”.

Just doing that won’t be enough, you’ll need to use the same ‘sorting’ trick previously mentioned:

Note that I’m replacing NULL with “Total” in multiple instances

One last trick for SQL Subtotals

I often want to have my “Total” be the last row. Sometimes I’ll have a value of “Other” that I want to be second-to-last (rather than display alphabetically). Using the CASE statement in the ORDER BY clause again, we can achieve this:

Originally posted on Medium

What's The Ideal Pagination From A UI/UX Perspective?

Steve Sohcot — Sat, 30 Apr 2022 03:04:59 +0000

When you Google something, there are probably millions of results. But they don't display all of them at once.

Pagination is the term web developers use to show only some of the results within a dataset

You're showing only some results, then there will be an option for "go to the next page," or some equivalent.

There are several variations of pagination. I recently sought out to answer, from a UI/UX view, what is the ideal pagination format? What's the most practical (dare I say, "best")?

Pagination Benefits

Why use pagination? Two major benefits come to mind:

It's easier for users to digest small chunks of information
It helps with the page load. Loading 10 results is much faster than 1,000 results (think seconds it takes a website to display)

Pagination Examples

This is so common amongst web developers that UI web libraries have pre-built design templates. For example, here's the default Bootstrap design:

Google has a unique one:

I remember several years ago, I did something like this:

Prior to going on this quest, this was my previous standard:

As you can see it has:

First/Last icons
Previous/Next icons
Page you’re on
Total number of pages

That was pretty simple (and simple is good!) but of course I thought:

Was there a better way?

Assumption: Search vs Browse

You're not remaking Google. You won't have a million results.

Your app should have a "search" capability that will allow a user to narrow down the results. Maybe it’ll be 100 or even 1,000 results. After a search, the user may sort the results. Overall it’ll be a rather small dataset.

If the user isn’t sure what they're looking for, then they are browsing the entire dataset.

For my use case, I'm using pagination for when a user is browsing an entire dataset.

Initial logic in designing the ideal pagination

Using Google as an inspiration and a lot of thought, this was my first pass:

Considerations:

First/Last buttons (if applicable)
Previous/Next buttons (if applicable)
Numeric indicators

No Need For First/Last Buttons

When you perform a search, you expect to see the most relevant results first. If the results aren't on the page, you'll go to the next page. And maybe even the one after that.

Once you're in the "middle" of the search results, you most likely won't have a desire to go back to the first page. Similarly, you probably won't have a need to skip to the last page; if you were that interested, you’d sort the results descending. As such, no need to show "First/Last" options.

Previous/Next

Assuming the results aren't on the first page, and you're actually utilizing pagination, you'll need Previous/Next buttons.

Only show if applicable. For example, when viewing the first page of results, don’t show a "Previous" button. Or at minimum, have it displayed but disabled (unclickable).

Two comments of interest when I was talking to York Myers, a friend who's a UI/UX expert:

Consider using the term "Back" instead of "Previous". Fewer letters will be more consistent with the term "Next".
Use icons (ex. arrow) if possible. Be mindful of your users though: depending on the demographic, it may be more clear to explicitly spell-out "Next" rather than have an arrow.

Page Numbers

A couple of obvious things:

Show the current ("active") page that the user is on
In addition to Previous (Back) / Next buttons, have a clickable button that has the page number on it. If you’re on Page 1, then have a button for "Page 2" and a button for "Next".

Not as obvious:

How many numeric buttons do you need? After some thought and discussion, I think it should be no more than two numbers, before and/or after the current page the user is on. This is demonstrated in the image above.

(2) Final Recommendations

I think the logic in the preceding recommendation is ideal, however there was another suggestion offered. Take a look at the component below:

It has:

Option to select rows per page (here, a dropdown with 10 pre-selected)
Displaying the current set of results (here, 1 to 10)
Total number of results (here, 14)
Previous / Next as icons

That simplifies things, but personally I don't want to give the user an option to select the number of results to show.

Showing less options >> User has to think less

I realized that this is what GMail does. Another ideal pagination design pattern then becomes:

I'm a web developer with 20+ years experience. While I’m not a UI/UX expert, I’ve picked up a thing or two over the years and I did consult one for this article. Your comments/opinions are welcome!

This article was originally posted on Medium