A Fine Slice Of SQL Server
A common dilemma is when you have two date columns, and you need to judge the gap between them for something.
For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.
You’re not left with many good ways to write the query to take advantage of indexes.
Read on to see what Erik means, and one way you can speed it up a bit.
Comments closedErik Darling has started a new series on how hard it can be to get a date:
I’ll often see people need to “flatten” dates to certain intervals.
By flatten, I mean the start of a day, week, month, or year, and likewise for the end values to all those intervals.
I’ve seen some really bad implementations of this. Most notable was probably a scalar valued function that converted a datetime to a 10 character string to remove the time portion.
In every where clause.
Click through for additional introductory notes and some links to good resources.
Comments closedNate Johnson hates time zones:
To start with, the transactional data is in PST/PDT — i.e. Pacific Time with DST fluctuation. Yes, it’s horrible. No, I don’t know what happens to events or jobs at 2am on the “Fall Back” date, or between 2am and 3am on the “Spring Forward” date. No, I can’t change it right now. Stop whining.
Now, I have offices in Paris France, Hong Kong, and Beijing China. These are 3 different “time zones”, but only 2 different
offsets— China and Hong Kong are in the same bucket, namely, UTC +08:00. More on that later.
Dates and currencies are seemingly two of the simplest things we deal with, but two of the most complex sets of data types.
Comments closedAndy Mallon needs to create groups of date ranges in T-SQL:
Here’s where my math nerd-iness comes out… There’s a little trick for “bucketizing” numbers (in this case, turning “Months” into “Month Buckets”):
– Take a number
– Divide it by your bucket size
– Round that number down to a whole number–We’ll call this the “divided number”
– Multiply the “divided number” by the bucket size–This is your bucket floor
– Add one to the “divided number” and then multiply that by your bucket size–This is your bucket ceiling
I was promised that there would be no math.
Comments closedDaniel Hutmacher shows how you can aggregate date and time data types in SQL Server:
Because the “Duration” column is a “time(3)” datatype, it doesn’t really aggregate into an average that easily.
Msg 8117, Level 16, State 1, Line 20 Operand data type time is invalid for avg operator.This may seem a little odd, as time is really a continuum just like any integer or floating-point value, right?
Read on to see how to do this and a warning from Daniel about overflowing. This sort of aggregation is a lot easier to do in R, but you can still do it in T-SQL.
Comments closedCecilia Brusatori shows how you can sort properly by month using Power BI:
Are your visualizations sorting the month field alphabetically? Don’t worry, I’ve been there when I started with Power BI.
If your model has a Dimension Date Table, first congrats, you’re on the right path in what respects to data model, second, you want to use the Month Name from that table, but you noticed that the visualizations don’t sort this column in the way we all wanted, from Jan to Dec.
Click through to see how.
Comments closedBert Wagner has more fun with math in SQL Server:
Years ago I was writing a query for a stacked bar chart in SSRS. The chart intended to show the percentage breakdown of distinct values in a table. For example, the chart would show that value A made up 30% of the rows, B made up 3%, C made up 12% and so on. Since every row had a value, I was expecting the stacked bar chart percentages to add up to 100%
However, in many instances the charts would come up short; instead of a full 100%, the percentages would only add up to 98% or 99%. What was going on?
Bert dives deeply into the topic and then gives us some practical suggestions on how to deal with it.
Comments closedChris Webb continues a series on Power BI custom formats:
In my last post I showed lots of examples of how Power BI’s new custom format string feature can be used to format numbers. This post, looking at dates and times, will be a bit different for two reasons: there are a lot more useful examples of custom date and time formats built into Power BI Desktop, and some of the format placeholders listed in the VBA documentation aren’t supported in Power BI. As a result I’m going to concentrate on some useful formats that aren’t covered well by the examples and highlight a few things that aren’t possible right now.
Read on for a slew of demos.
Comments closedMax Vernon gives us three ways to change data types:
PARSEprovides a mechanism to convert a wide variety of character based dates into a datetime data type. From the Docs:Returns the result of an expression, translated to the requested data type in SQL Server.
Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.
PARSE has its value, but comes at a cost, as Max shows.
Brett Powell gives us a version of a time dimension in Power Query:
Like most dimensions in a data model, users will generally begin their analysis of time at a high level (e.g. hour) and then filter and drill their way into greater levels of detail, often leveraging hierarchies built into the model and/or drilling functionality in tools like Power BI. To support analysis by multiple grains and hierarchies, the query produces columns which group the seconds into hourly quartiles, hours, and minutes.
Gilbert Quevauvilliers has a script to generate a time dimension as well:
Below is the syntax to create a Time Dimension Table in Power Query
I had a requirement where I needed to create a Time Dimension for a customer. Most of the time I only need the date. Upon searching I could not find a resource where they had created the time dimension only using Power Query.
Gilbert’s gives you a key insight into the value of time dimensions: breaking everything out into periods. 5-minute intervals, 15-minute intervals, or however the company looks at data.
Comments closed