Press "Enter" to skip to content

Curated SQL Posts

DATEDIFF() and Month Boundaries

Published 2023-08-22 by Kevin Feasel

Deb Melkin fed the mogwai after midnight:

I was working on a query this week that reminded me of a fun quirk when working with dates and the DATEDIFF function in particular.

I have a process that takes a while to run. Because of all of the moving parts to keep track of, I have an audit table to track what I’m doing to collect basic info like when did it start, when did it end, etc. I created a simple report for myself to break things down so I can report back to the team. I threw together a simple SQL statement, using DATEDIFF to figure out the how long things took. Looking at the results, I saw some interesting results.

Read on for two queries, one which has a bit of a problem and one which strives to correct that problem.

Comments closed

Tenant Configuration in Microsoft Fabric

Published 2023-08-22 by Kevin Feasel

Marc Lelijveld collects some data:

It has been quiet for a few weeks due to summer break. But now, it’s time to ramp up again and continue posting about Fabric. This time, I’ll start with a question that many Fabric (and Power BI) users ask themselves: “What does my tenant configuration look like?”. Often, users find themselves eager to explore new features they’ve come across online. But somehow, they cannot get it to work, or the feature does not even show for them.

In this blog, I’ll elaborate on the challenges and scenarios in which questions like these come up, and what you can do as a Fabric / Power BI administrator to ease answering this question.

Read on for one of the most common scenarios.

Comments closed

Omitted Variables and Logistic Regression

Published 2023-08-21 by Kevin Feasel

John Mount misses a variable:

I would like to illustrate a way which omitted variables interfere in logistic regression inference (or coefficient estimation). These effects are different than what is seen in linear regression, and possibly different than some expectations or intuitions.

This is an interesting article and there’s a really good comment helping to explain this effect in epidemiology.

Comments closed

Creating a Box Plot in R

Published 2023-08-21 by Kevin Feasel

Steven Sanderson builds up a box plot:

Are you ready to dive into the world of data visualization in R? One powerful tool at your disposal is the box plot, also known as a box-and-whisker plot. This versatile chart can help you understand the distribution of your data and identify potential outliers. In this blog post, we’ll walk you through the process of creating box plots using R’s ggplot2 package, using the airquality dataset as an example. Whether you’re a beginner or an experienced R programmer, you’ll find something valuable here.

Click through to learn what kind of information a box plot can provide, as well as how to create one using a variety of R libraries.

Comments closed

Lists and DataFrames in R

Published 2023-08-21 by Kevin Feasel

Adrian Tam continues a series on core data types in R:

Vectors in R are supposed to be of homogeneous data type. You can use a list as the container if there are mixed data types, such as numbers and strings. The list and data frame are closely related in R. The data frame is probably more useful because it reflects how we usually collect statistics. In this post, you will learn about them. Specifically, you will know:

  • What are lists and data frames in R
  • How to manipulate lists and data frames

Read on to learn more about these two sorts of collections.

Comments closed

Projecting Gas Bills with Excel

Published 2023-08-21 by Kevin Feasel

Jiri D. performs an estimate:

With a rising prices of utilities like gas and power and winter still being in progress it might be good to check your usage from time to time. With electricity and TOU meters, it is easy, retailer does that for you and you can check your consumption and projected bill at any time with a granularity of 30 minute interval (sometimes even less).
With gas (and water) it is trickier, those are being read manually every 2 – 3 months so you may be up for a surprise when the bill arrives.

I had a co-worker who tracked this stuff in meticulous detail over a period of several years, to the point where he knew exactly how much propane to buy in August (when prices are lowest) to get through winter with minimum waste.

Comments closed

SQL Server Events: Attention Signal and User Error Message

Published 2023-08-21 by Kevin Feasel

Jose Manuel Jurado Diaz explains the difference:

Several days ago, I got a service request where we had a conversation about the differences about SQL Server Events: Attention Signal and User Error Message. In the realm of SQL Server, a well-rounded understanding of various events is crucial for optimizing performance, troubleshooting issues, and maintaining a robust database environment. In this article, we’ll delve into three important events: “Attention Signal,” “User Error Message,” and “Command Timeout.” We’ll explore their significance, scenarios where they occur, and provide a practical script to simulate and capture these events. Let’s dive in!

Read on to understand the intent of each of these event types.

Comments closed

Restoring SSAS Cubes to SQL Server 2022 CU5

Published 2023-08-21 by Kevin Feasel

Meagan Longoria runs into a problem:

I have a client who was upgrading some servers from pre-2022 versions of SQL Server to SQL Server 2022 CU7. They had some multidimensional SSAS cubes that were to go on the new server. But they ran into an issue after the upgrade. After restoring a backup of an SSAS database to the new server they found that they could no longer modify the data source using SSMS.

Read on to see what the problem was, as well as how to fix it.

Comments closed

Idle Timeouts and Power BI

Published 2023-08-21 by Kevin Feasel

Chris Webb logs us all out:

A common requirement from Power BI customers in highly-regulated industries is the need to log users out of Power BI if they have been inactive for a certain amount of time. If your Power BI reports contain extremely sensitive data you don’t want someone to open a report, leave their desk for lunch, forget to lock their PC and let everyone in the office see what’s on their screen, for obvious reasons. This has actually been possible for some time now with Power BI and is now supported for Fabic, so I thought I’d write a blog post to raise awareness.

Read on to see how it works.

Comments closed

A Primer on Vectors in R

Published 2023-08-18 by Kevin Feasel

Adrian Tam shows off one of the building blocks for R:

R is a language for programming with data. Unlike many other languages, the primitive data types in R are not scalars but vectors. Therefore, understanding how to deal with vectors is crucial to programming or reading the R code. In this post, you will learn about various vector operations in R. Specifically, you will know:

  • What are the fundamental data objects in R
  • How to work with vectors in R

This is often a little tricky for newcomers to the language to pick up, though if you’re already familiar with set-based operations in SQL, vector-based operations are fairly straightforward.

Comments closed