A Fine Slice Of SQL Server
Erik Darling opposes expansive numeral policies:
A while back I blogged about how ISNUMERIC can give you some wacky results.
Working with a client… Well, it’s hard to know based on my publishing schedule. But they were using ISNUMERIC all over the place, and getting the same wacky results, and even errors.
Click through to see what types of things ISNUMERIC() considers to be numbers and for a couple of alternatives.
Aaron Bertrand continues a series on the utility of calendar tables:
In two previous articles (part 1 and part 2), I showed some practical ways to use a SQL Server calendar table to solve business day and date range problems. Next, I’ll demonstrate how you can use this table to solve scheduling problems, like environment-wide server patch management.
When you have a handful of servers, managing patching is straightforward. When you have hundreds, it gets more complicated to balance keeping everything in sync and not spending all of your time organizing and performing patching.
This is a concrete example of where calendar tables can make life a lot easier.
Comments closedRandolph West shows us how the DATETIMEOFFSET type is stored in SQL Server:
Cast your mind back to our discussion on
DATETIME2. As you know,DATETIME2is basically the same as squishingDATE(3 bytes) andTIME(between 3 and 5 bytes depending on the scale) into the same column. You end up with a persisted value that is between 6 and 8 bytes wide.
DATETIMEOFFSETis kinda sorta the same thing, but with more bytes on the end. If you take a look at the Microsoft Docs page, the similar idea of a varied column size is retained. For a scale of 0 fractions of a second you only need 8 bytes to store your value, while the default scale of 7 decimal places for storing seconds requires the full 10 bytes.
Click through to understand how the sordid details.
Comments closedEitan Blumin opens Pandora’s Box:
In one of my previous posts, Fun with DATETIME Arithmetics, I introduced a way to use “math” to manipulate
datetimevalues for effectively generating, calculating, and displaying intervals (i.e. difference between twodatetimevalues). These mostly work with the addition and subtraction operators (+, -).In one of the paragraphs, I mentioned multiplication and division, and posed the question about why anyone would ever need to do this.
Read on for one not-quite-ordinary reason why you might need this.
Comments closedHaroon Ashraf recommends using a template with date dimension details:
A Power BI Template
A Power BI template is a structure or model that typically contains commonly used tables, relationships, and hierarchies belonging to an organization or an individual. This model is reused in any Power BI report. More information is provided in the previous article:
Centralized Data Modelling using Power BI Templates
What is a Date Template in Power BI?
A Date template is a precise structure of the Date table that is a background for building reports in the organization. In other words, it is like a built-in Date table that any reports developer or a skilled business user can apply to build Power BI reports.
Read on for more Q&A as well as how to create a simple version of a date table for this template. The idea of using a template makes even more sense as you have more complicated date table requirements, such as adding in fiscal year details, holiday information (especially holidays which don’t always fall on the same solar calendar day, such as Passover or Easter), and dates important to the company.
Comments closedAaron 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 closedEitan 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.100This 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 closedAaron Bertrand has a post up on using a calendar table:
A while back, I wrote an article called Creating a date dimension or calendar table in SQL Server. I have used this pattern repeatedly and, based on the questions I get from the community, many of you are using it, too. Some of the questions I get are along the lines of “how do I actually use this table in my queries?” and “what are the performance characteristics compared to other approaches?” So, I thought I would put together a collection of use cases and analysis, starting with business day problems.
I’m a big fan of calendar tables as well. They’re quite useful for a variety of business problems and make date math problems really easy, especially when dealing with non-standard calendars (e.g., work weeks, fiscal years, figuring out what day Easter is).
Comments closedMichael Grogan has a few examples of working with time series data in PostgreSQL:
Tools such as Python or R are most often used to conduct deep time series analysis.
However, knowledge of how to work with time series data using SQL is essential, particularly when working with very large datasets or data that is constantly being updated.
Here are some useful commands that can be invoked in SQL to better work with time series data within the data table itself.
Click through for examples like using a window function to calculate moving averages and using time zones. H/T Mark Hutchinson.
Comments closedEd Hansberry decrees that henceforth, Thursday shall be the first day of the week:
By default, Power BI starts its week on Sunday, but that doesn’t work for every situation. Some companies prefer their week start on Monday, and I recently helped someone that needed their week to start on Thursday. Fortunately, by tweaking your date table in Power Query, you can make this change with no calculations at all.
Click through to see how.
Comments closed