The Bit Bucket

T-SQL 101: 86 Summarizing data with SUM, AVG, MIN, MAX

I mentioned in a previous post that COUNT was an aggregate. The other common aggregates are shown in this table, and no surprise what they do.

SUM adds up or totals the values.

AVG calculates the average of the values.

MIN works out the minimum value.

MAX works out the maximum value.

But if you’ve started to think about how SQL Server works, you might be wondering about what happens with NULLs.

2021-02-22

SDU Tools: Weekday in same week using SQL Server T-SQL

A while back, I got a request to add a new function to our free SDU Tools for developers and DBAs. The user wanted to be able to find a particular day of the week, in a given week. So for example, if I give you a date of 30th April 2021, what is the Thursday in that week? So we’ve added a new function WeekdayOfSameWeek.

It takes two parameters:

2021-02-19

Power BI: Join me at the Power BI Summit !

If you haven’t already heard, there’s a global Power BI summit coming up. It’s 19th to 23rd April and details are here:

https://globalpowerbisummit.com/

I’m presenting a session “Enterprise Power BI: Do I still need a data warehouse?

It’s being replayed across different time zones and each time though, I’ll be there for a live Q&A. Would love to see you there.

2021-02-18

Book Review: Reprogramming the American Dream

I’ve mentioned before that I have a deep interest in artificial intelligence and how it will change the world. In particular, I’m interested in the effects on jobs. So I was pleased to get to listen to another book covering this recently. It was Kevin Scott (from Microsoft)’s Reprogramming the American Dream: From Rural America to Silicon Valley - Making AI Serve Us All.

Kevin is Microsoft’s Chief Technology Officer, so I’ve come across him before. Prior to Microsoft, he was a senior vice president at LinkedIn and came across to Microsoft as part of the LinkedIn purchase.

2021-02-17

SQL Server Express and Reporting Services

I’m a fan of SQL Server Express edition. It’s a fabulously functional free database that lets you work with up to 10GB of data per database. One aspect that’s commonly misunderstood though, is that it also supports a limited version of SQL Server Reporting Services.

There are many scenarios where Express edition is the perfect fit. The Microsoft sales and marketing teams have had an ongoing strange relationship with Express edition because it’s free. Much of the documentation tried to suggest that it was for hobbyists, and para-professionals. But Express edition has a great role to play in professional systems too. For example, if you needed a small database to run on a series of point of sale devices, it could well be the right answer, particularly if it’s then connected to one of the higher editions as a central SQL Server system.

2021-02-16

T-SQL 101: 85 Counting rows and column values with COUNT

In previous posts, I looked at how to read data from a table. Now, we need to look at how we do calculations on the data in the table.

The most basic calculation we might do is to count the number of rows in the table. The first example above does that.

What about the asterisk?

But also notice that is has an asterisk in the query. Some people worry about the asterisk being in their queries as usually having an asterisk isn’t a good idea. In fact, some customers have automated systems for checking code, and the automated system might complain about the asterisk.

2021-02-15

SDU Tools: Date of Orthodox Easter in SQL Server T-SQL

Some time back, we added DateOfEasterSunday to our free SDU Tools for developers and DBAs. Given it was the Christian Easter Sunday, almost immediately, I got a request for the Greek Orthodox Easter. That date isn’t of course just the Greek one, so we’ve added a new function DateOfOrthodoxEaster.

It’s based on a concept from Antonios Chatzipavlis. Thanks !

It takes a single parameter:

@Year int - the year to find the date for

2021-02-12

Making a cloud transformation, not just a migration - free course

In the dying days of PASS, one of the attempts at keeping the organization afloat was the introduction of the PASS Pro membership. One benefit of that was access to a series of Learning Experiences. Since the demise of PASS, our friends at Red-Gate have purchased the assets, and that includes those courses.

I was pleased to hear from the Red-Gate team that the courses have now been made available for free, and without any type of paywall/loginwall. That’s just awesome.

2021-02-11

T-SQL 101: 84 Avoiding data type conversion errors with TRY_CAST, TRY_CONVERT and TRY_PARSE

In a previous post, I showed how to use CAST and CONVERT. What I didn’t mention before though, is what happens when the conversion will fail. If I try to convert the string ‘hello’ to an int, that just isn’t going to work. Of course, what does happen, is the statement returns an error.  Same thing happens if I try to convert the 30th February 2016 to a date. There aren’t 30 days in February. Again, an error will be returned.

2021-02-10

Opinion: iPhone12 Pro is an interesting device but a lousy phone

I’d been using an iPhone6+ for quite a while, and it still worked fine. Lately though, I’d been running into things that needed a later version of iOS than what was supported on that phone. So I changed to an iPhone12 Pro.

So far, that’s been a big mistake.

One thing I’ve seen over the years while working with technology, is that development teams often get enamoured with the new features. And they forget to make sure that core functionality works as expected. The core functions seem to get lost in the weeds.

2021-02-09