Press "Enter" to skip to content

Curated SQL Posts

Random Sequences and Probabilities

Published 2021-04-29 by Kevin Feasel

Holger von Jouanne-Diedrich explains the results of a poll:

Some time ago I conducted a poll on LinkedIn that quickly went viral. I asked which of three different coin tossing sequences were more likely and I received exactly 1,592 votes! Nearly 48,000 people viewed it and more than 80 comments are under the post (you need a LinkedIn account to fully see it here: LinkedIn Coin Tossing Poll).

In this post I will give the solution with some background explanation, so read on!

Read on to understand why it’s just as likely that you’ll see a sequence, when flipping a coin, of H,H,H,H,H,H just as often as you’ll see H,T,H,T,H,T.

Comments closed

Troubleshooting Code Performance in R

Published 2021-04-29 by Kevin Feasel

Mira Celine Klein shows how to benchmark R code performance:

Let’s assume you have written some code, it’s working, it computes the results you need, but it is really slow. If you don’t want to get slowed down in your work, you have no other choice than improving the code’s performance. But how to start? The best approach is to find out where to start optimizing.

It is not always obvious which part of the code makes it so slow, or which of multiple alternatives is fastest. There is the risk to spending a lot of time optimizing the wrong part of the code. Fortunately, there are ways to systematically test how long a computation takes. An easy way is the function system.time. Just wrap your code into this function, and you will (in addition to the actual results of that code) get the time your code took to run.

But that’s not the only route—read on to learn about other techniques as well and see them in action.

Comments closed

Gaps and Islands in Dates

Published 2021-04-29 by Kevin Feasel

Aaron Bertrand shows off a great use for calendar tables in gap and island style queries:

In my previous article I revisited the concept of a calendar table, and explained some ways to use this data for business date calculations. This time, I wanted to explore how you can use the calendar table to simplify generating date ranges, and some query challenges this can help you simplify.

Click through for examples of the sorts of gap and island problems you can solve fairly easily with a calendar table. For an even simpler example, many BI reports want to see days even where there is no data, and a calendar table gives you that capability.

Comments closed

Data-Driven Subscriptions in Power BI

Published 2021-04-29 by Kevin Feasel

Patrick LeBlanc shows us how to build data-driven subscriptions using Power BI and Power Automate:

Automate data driven subscriptions with the Power Platform using Power BI and Power Automate! Patrick shows you how to quickly setup a report bursting option for your reports.

Click through for the video. There are a few more steps compared to what you’d do in Power BI Reporting Services, but it’s still pretty straightforward.

2 Comments

Arithmetic Operations on DATETIME Data Types

Published 2021-04-29 by Kevin Feasel

Eitan Bluman shows off some math skills:

Mathematical addition and subtraction can be performed between two datetime data types:

SET @d2 = '1900-03-30 18:00'SELECT@d1 + @d2 -- result: 1900-04-01 10:15:15.900, @d1 - @d2 -- result: 1899-10-05 22:15:15.900, @d2 - @d1 -- result: 1900-03-29 01:44:44.100

This means that we can have basic datetime arithmetics in SQL server. We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

This is one of those things you can do, but I’m not very fond of. First of all, as Eitan points out, you can’t do these in the (in all ways superior) DATETIME2 data type. Secondly, it adds some confusion to the code, as you don’t always get what you expect.

Comments closed

Sequences and Filters in XPath

Published 2021-04-29 by Kevin Feasel

Barney Lawrence continues a series on XML processing in SQL Server:

This post looks at a problem that can come up in XML messages that have perhaps not been thought out as well as we’d like and where we can’t uniquely identify one instance of a repeating element. The example we’ve used so far avoids this problem so we’ll switch to a sample that illustrates this problem.

Read on for the crux of the problem, as well as solutions.

Comments closed

Using Logic Apps to Send Multiple Attachments

Published 2021-04-28 by Kevin Feasel

Rayis Imayev has a project:

In my real project, I need to build a Logic App to send email messages with a set of files attached from my Azure Storage Account. I was able to find similar examples from other power platform developers, however, they lacked a critical part that I needed: my set of files had to be dynamic: 2 files, or 102 files –  the Logic App should be able to support this.

So, here, I would like to share my brief journey in creating such Azure Logic App:

Read on to see how Rayis solved this.

Comments closed

Managing Azure DevOps via Azure Logic Apps

Published 2021-04-28 by Kevin Feasel

Stuart Ainsworth has a process:

A big part of my job these days is looking for opportunities to improve workflow. Automation of software is great, but identifying areas to speed up human processes can be incredibly beneficial to value delivery to customers. Here’s the situation I recently figured out how to do:

1. My SRE team uses a different Azure DevOps project than our development team. This protects the “separation of duties” concept that auditors love, while still letting us transfer items back and forth.
2. The two projects are in the same organization.
3. The two projects use different templates, with different required fields.
4. Our workflow process requires two phases of triage for bugs in the wild: a technical phase (provided by my team), and a business prioritization (provided by our Business Analyst).
5. Moving a card between projects is simple, but there were several manual changes that had to be made:
– Assigning to a Business Analyst (BA)
– Changing the status to Proposed from Active
– Changing the Iteration and Area
– Moving the card.

To automate this, I decided to use Azure Logic Apps. 

Read on to see how Stuart did this.

Comments closed