Press "Enter" to skip to content

Category: Dates and Numbers

The Power of Rounding

Published 2024-12-06 by Kevin Feasel

Denny Cherry makes a change:

So, I ran across a problem with QuickBooks that involves some of the most basic math that we were all taught in elementary school: how to round numbers properly. You’d think that a company that makes accounting and invoicing software for a living would understand how rounding of numbers works. But based on the last hour of having to edit the data that gets send to QuickBooks from our internal system you’d be wrong.

Denny’s example is $3.18497736 and rounding happens after four decimal spots, so it’s $3.1849 or $3.1850. Denny expects $3.1850 and QuickBooks gives $3.1849.

In this case, Denny’s right. The part that confuses people is banker’s rounding, which has you round to the even number if your last digit is a 5. For example, if it were $3.18495 and you round to four spots after the decimal, that would be $3.1850. With $3.18485, it would round to $3.1848.

.NET uses banker’s rounding by default, which can confuse people unfamiliar with the concept. SQL Server, meanwhile, rounds the way that Denny expects: 5 or higher rounds up, 0-4 rounds down.

Leave a Comment

Blank Dates and DAX

Published 2024-12-03 by Kevin Feasel

Marco Russo and Alberto Ferrari are blanking on us:

Handling missing dates in a semantic model can be challenging, especially when working with DAX time intelligence functions. Dates might be missing for various reasons: incomplete data entry, system errors, special placeholder values like 0000, or dates set far in the future. We will see that using a blank is the best way to manage missing dates, even though you should pay attention to DAX conditional expressions operating on those dates. We will also consider how to hide these blanks in a Power BI report if their presence is not desired in charts and slicers.

Read on to learn more.

Leave a Comment

GiST Indexes and Range Queries in PostgreSQL

Published 2024-12-03 by Kevin Feasel

Lee Asher can’t be limited to a single point:

Our Part I query used the following WHERE clause:

WHERE tsrange(o.start_time, o.end_time) && tsrange(p.enter, p.leave)

The “tsrange()” functions return timestamp ranges. But overlap queries aren’t limited to timestamps; they can be constructed from integers and floating-point values too. Imagine an arbitrage database that tracks the minimum and maximum price paid for a commodity.

Read on for examples of other types of ranges, preventing range intersection, and more.

Leave a Comment

Converting SQL Audit FileTime to DateTime Format

Published 2024-11-06 by Kevin Feasel

Patrick Keisler helps a customer:

One of my customers recently wanted to rename each of the SQL audit files will the datetime stamp of when it was created. I explained to them the filename already contains a datetime stamp. While it does not look like a typical timestamp, it is based on the Windows Filetime data structure that is a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC). Nonetheless, they still wanted a traditional datetime stamp in the file name.

Read on to see how. I can understand the displeasure in adding redundancy to a filename, though I also understand the reasoning from the customer’s point of view: FileTime isn’t human-readable in any meaningful way.

Comments closed

Using Week-Based Calendars in Power BI

Published 2024-10-22 by Kevin Feasel

Marco Russo and Alberto Ferrari work in weeks:

Weekly calendars are common in manufacturing, retail, and any business that is sensitive to weekends or to the number of working days. For example, the scenario described in this article uses the number of pageviews on a website from 2019 to 2024, with data available until September 3, 2024. The website analyzed has a clear weekly trend, with slower traffic over the weekend, as shown in the following line chart with a daily granularity. It seems like a business website. A sports website would probably display the opposite trend.

Read on to see some of the challenges around week-based calendars. There’s a reason I have a “Dates and Numbers” category on Curated SQL and it’s exactly for things like this: some of the most common things we as humans work with are extremely complex and fraught with exceptions, including calendars.

Comments closed

Power BI Data Type Optimization

Published 2024-08-20 by Kevin Feasel

Nikola Ilic shows how important it can be to choose the right data types:

For demo purposes, I’ll be using a fact table that contains the data about chats performed by a customer support department of the fictitious company Customer First. This table includes approximately 9 million rows, which is not considered a large table in the context of Power BI and analytical workloads. For the sake of simplicity, let’s pretend that our model consists of only this single table. Finally, a semantic model is configured as an Import mode model. If you want to learn how your data is stored in Power BI, I suggest you start by reading this article first.

Data was loaded into Power BI from the underlying data source (SQL Server database) as-is, without any additional optimizations applied.

Nikola walks through the process of finding the most expensive columns in terms of data size and using the least precise acceptable value. One other thing that I commonly see is identity columns or other keys on fact tables. Those are very rarely necessary, because the point of a fact table is typically to aggregate it in some fashion. And these keys are unique (by design), meaning they won’t compress very well and will take up a lot of space. Looking at Nikola’s example, my next question would be, knowing that the name of the table is factChat, does chatID tie to some chat dimension? If not, is it actually necessary for reporting? Again, if not, that could shave off another 60 MB or so from the data model.

Comments closed

Avoid Mixing DATETIME with other Date Types

Published 2024-08-12 by Kevin Feasel

Paul White shares some advice:

Microsoft encourages us not to use the datetime data type: 

Avoid using datetime for new work. Instead, use the time, date, datetime2, and datetimeoffset data types. These types align with the SQL Standard, and are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Well, ok. Sensible and well-informed people might still choose to use datetime for performance reasons. Common date and time functions have optimised implementations in the SQL Server expression service for the datetime and smalldatetime data types.

Paul has posted the full article on X.

Comments closed

Rounding Options in T-SQL

Published 2024-07-29 by Kevin Feasel

Rick Dobson talks rounding:

Please compare the SQL Server round function to banker’s rounding in T-SQL for converting decimal values to integer values. I seek a framework for assessing how closely banker’s rounding results versus SQL Server Round function results match the underlying decimal values. Please provide a couple of empirical comparisons with the framework to indicate which set of rounded values are closer to the underlying decimals and by how much.

Rick talks about what banker’s rounding is and shows how its results adhere more closely to the underlying distribution. Rick does show a user-defined function that generates a rounded number, but if you’re doing this with large enough amounts of data, using CLR and the System.Math.Round() function will likely give you better performance. Incidentally, this is also why if you write T-SQL code to round decimal numbers and .NET code to round numbers, your results may be a little different: T-SQL rounds to the nearest integer, whereas .NET uses banker’s rounding by default.

Comments closed

Translating Numbers to Words in T-SQL

Published 2024-07-16 by Kevin Feasel

Sebastiao Pereira needs no number:

Sometimes, it is necessary to have numbers spelled out in words, like when writing a sentence (“Two hundred sixty-one victims were hospitalized.”). How do you express numbers in words with T-SQL code?

I could see a bit more recursion in the solution making it tidier, but in fairness, a bit more recursion would make it a lot slower in T-SQL.

For moderate difficulty mode, try it in German. For hard mode, switch to French.

Comments closed

Removing Leading Zeroes from a String in T-SQL

Published 2024-07-12 by Kevin Feasel

Steve Stedman gets rid of leading zeroes:

When working with data in SQL Server, there may be times when you need to remove leading zeros from a string. This task can be particularly common when dealing with numerical data stored as strings, such as ZIP codes, product codes, or other formatted numbers. In this blog post, we’ll explore several methods to remove leading zeros in SQL Server.

I’m not sure I see the reason to use anything other than CAST() (or, better yet, TRY_CAST()), but Steve does show two other methods.

2 Comments