The Bit Bucket - Page 2 of 124 - Thoughts from Data Platform MVP and Microsoft RD

Opinion: Don't block PO Boxes unnecessarily

In some countries, post office boxes are quite anonymous. And for that reason, some vendors aren't keen to send goods to PO Boxes. But that's not all countries. In Australia, for example, you have to provide all sorts of ID to the post office to be able to get one.

Why PO Boxes?

The fundamental reason that many people use PO Boxes is to have a relatively safe location for their mail to be collected. At so many houses, letter boxes are quite unsafe. And for people living in apartments, the situation is often far, far worse.

Like everyone else, we've been doing a lot more online shopping lately. What has really frustrated me though, are vendors who don't handle address details properly.

Losing sales

I've had many sites who have a rule built into the UI to prevent entering a PO Box for a shipping address. Even though I'd prefer it wasn't that way, I'm OK with that. But then they use the same address validation logic for a billing address.

Please, please, please don't block PO Boxes in billing addresses. That makes no sense.

I've had sites where I want to buy products, and I can't because they won't let me enter my actual billing address (i.e. a PO Box) for the credit card.

At that point, I can't proceed with the purchase.

And identity theft issues

Stealing mail from street letter boxes, etc. is a common cause of identity theft. Yet, I often find that exactly the sorts of suppliers who should be concerned about identity (utility companies, banks, local councils, etc.) often insist on sending mail directly to street addresses. That's not sensible.

Worse, I've seen people move to a new address, and the bank sends details of the change, to their old address ! i.e. the place where they are no longer living. I understand the decision process that led them to do that (in case the move wasn't real) but think what they've just done: 99% of the time, they've sent private bank-related details to an address where someone no longer lives.

Another common situation is where people travel a lot. While that's not an issue for us right now, it is at times. And having mail hanging out of a street letterbox isn't helpful security-wise. We fortunately have good neighbours who will deal with that but the issue is that we shouldn't need them to do that.

T-SQL 101: #70 Adding and subtracting intervals in SQL Server T-SQL using DATEADD

When you're writing T-SQL, you might also need to add intervals to dates, perhaps add days or subtract days and so on. The DATEADD() function is the one that we can add or subtract intervals to the date and time.

Now in the example shown here, what I've said is in 20190228 or 28th of February 2019. I want to add on 12 days. To subtract 12 days, I would have just put -12 instead of 12. You can see the output here:

Now there are a wide variety of intervals. It's not just days. There are values right down to nanosecond.

Notice also though, that there are abbreviations for each of these. So instead of writing day, you could just write d. Or you could write dd, but I'd suggest you don't do that.

You might imagine that minute is m. But m is actually month and so on, so it can get very confusing. For someone who's reading your code, it's much, much better if you actually use the full word. So even words like nanosecond, second or millisecond, just put the whole word.

You'll see lots of old programming where people have used the abbreviation instead. Don't follow that.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Is or Very

Before I started learning other languages, I'd always heard people saying that translating was hard, but I didn't really understand what they meant. When I was about 13, I was introduced to French, Latin, and Japanese. Now that's an odd mixture. I started to see the problems. Directly translating a sentence word by word leads to either nonsense in the other language or clumsy language.

For Chinese, I wanted to cover a simple example today.

In English, we might say:

He is smart.

The direct translation in Chinese would be:

他是聪明。(Tā shì cōngmíng.)

And yes, Google Translate does return "He is smart" for that Chinese sentence. But the problem is that the sentence is wrong. If you just reverse the direction of the translation, Google returns:

他很聪明。(Tā hěn cōngmíng.)

Literally, that's "He very smart". And that's how it's said in Chinese.

And if you've talked to, or listened to, Chinese migrants in the past, you may well have heard them use exactly that type of malformed English.

In Chinese, you don't just structure a sentence like

SubjectNoun is Adjective

That's fine in English but not in Chinese. Similarly:

SubjectNoun Adverb Adjective

isn't OK in English either.

很 (Hěn) meaning "very" is a common adverb but others could be used, to vary the intensity.

Incidentally, an adverb is called a 副词 (Fùcí) which is literally a secondary or auxiliary word.

Learning MandarinI'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: What is the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

I keep hearing questions from developers about the difference between SQL Server Express Edition and LocalDB. One asked me the other day:

What's the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

SQL Express (it's really SQL Server Express Edition) is a service-based version of SQL Server i.e. it runs as a service all the time, independently of other applications. When you say .\SQLEXPRESS you are looking for a named instance of SQL Server called "SQLEXPRESS" that is on your local machine and connected to via a shared memory interface (that's what the dot is).

Local DB is a deployment option for SQL Express that runs as an attached process to another application, instead of as a service. It makes it easier to deploy applications like Visual Studio that need a copy of SQL Server locally but don't want to be heavy-handed by installing a service as well as the IDE. You can read about LocalDB here.

Visual Studio also just needs to use a SQL Server for a while during development, but doesn't need it running all the time. When you connect to "(localdb)\v11.0" you are connecting to one of these attached instances, and in this case using version 11. When you start it by accessing it from within Visual Studio, it shuts down again when you close Visual Studio.

It doesn't have to be attached to Visual Studio though. You could attach it to your own application. You can also have multiple versions of localdb on the same machine.

You can even start and stop Local DB separately from applications like Visual Studio. The SqlLocalDB Utility can be used to manage the lifetime (starting and stopping) of Local DB. You can read about it and the functions that it offers, here.

SQL: Adding time to Change Data Capture (CDC) Events

Several times now on forums, I've seen questions about Change Data Capture (CDC). People like the way they can retrieve details about changes that have occurred (often to trickle-feed into a data warehouse), but they are puzzled why CDC doesn't tell them when the event occurred. There's an easy fix for that.

Let's start by doing a quick CDC setup:

DROP DATABASE IF EXISTS CDC;
GO

CREATE DATABASE CDC;
GO

USE CDC;
GO

CREATE TABLE dbo.NewEmployees
( 
    NewEmployeeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    FullName nvarchar(100)
);
GO

-- note new last few columns in:

SELECT * FROM sys.databases WHERE name = N'CDC';

-- enable cdc at the database level

EXEC sys.sp_cdc_enable_db;

-- enable cdc for the table - requires SQL Agent running

EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'NewEmployees',
  @supports_net_changes = 1,
  @role_name = NULL;

-- review the state of CDC

SELECT name, is_tracked_by_cdc FROM sys.tables;
GO

DROP DATABASE IF EXISTS CDC;

CREATE DATABASE CDC;

USE CDC;

CREATE TABLE dbo.NewEmployees

(

NewEmployeeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

FullName nvarchar(100)

);

-- note new last few columns in:

SELECT * FROM sys.databases WHERE name = N'CDC';

-- enable cdc at the database level

EXEC sys.sp_cdc_enable_db;

-- enable cdc for the table - requires SQL Agent running

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo',

@source_name = 'NewEmployees',

@supports_net_changes = 1,

@role_name = NULL;

-- review the state of CDC

SELECT name, is_tracked_by_cdc FROM sys.tables;

And let's then add three rows of data, then change one of them:

INSERT dbo.NewEmployees 
(
    FullName
)
VALUES('Fred Jones'),('Steve Ballmer'),('Bill Gates');

UPDATE dbo.NewEmployees SET FullName = 'Greg Low' 
    WHERE NewEmployeeID = 1;
GO

INSERT dbo.NewEmployees

(

FullName

)

VALUES('Fred Jones'),('Steve Ballmer'),('Bill Gates');

UPDATE dbo.NewEmployees SET FullName = 'Greg Low'

WHERE NewEmployeeID = 1;

There are two ways we can see the changes:

DECLARE @From_LSN binary(10) = sys.fn_cdc_get_min_lsn('dbo_NewEmployees');
DECLARE @To_LSN binary(10) = sys.fn_cdc_get_max_lsn();

SELECT c.* 
FROM cdc.fn_cdc_get_all_changes_dbo_NewEmployees
         (@From_LSN, @To_LSN, 'all') AS c;
SELECT c.* 
FROM cdc.fn_cdc_get_net_changes_dbo_NewEmployees
         (@From_LSN, @To_LSN, 'all') AS c;
GO

DECLARE @From_LSN binary(10) = sys.fn_cdc_get_min_lsn('dbo_NewEmployees');

DECLARE @To_LSN binary(10) = sys.fn_cdc_get_max_lsn();

SELECT c.*

FROM cdc.fn_cdc_get_all_changes_dbo_NewEmployees

(@From_LSN, @To_LSN, 'all') AS c;

SELECT c.*

FROM cdc.fn_cdc_get_net_changes_dbo_NewEmployees

(@From_LSN, @To_LSN, 'all') AS c;

That returns the following:

The first SELECT returns all changes as they occur, and the second SELECT returns the net affect of those changes. Because NewEmployeeID #1 was inserted as Fred Jones, and later changed to Greg Low, the net affect is just of an insert as Greg Low.

But notice, there's no time value. What we can use to get the time is the LSN (log sequence number) by calling the sys.fn_cdc_map_lsn_to_time() function like this:

DECLARE @From_LSN binary(10) = sys.fn_cdc_get_min_lsn('dbo_NewEmployees');
DECLARE @To_LSN binary(10) = sys.fn_cdc_get_max_lsn();

SELECT c.*, sys.fn_cdc_map_lsn_to_time(c.__$start_lsn) AS ChangeDateTime 
FROM cdc.fn_cdc_get_all_changes_dbo_NewEmployees
         (@From_LSN, @To_LSN, 'all') AS c;

SELECT c.*, sys.fn_cdc_map_lsn_to_time(c.__$start_lsn) AS ChangeDateTime 
FROM cdc.fn_cdc_get_net_changes_dbo_NewEmployees
         (@From_LSN, @To_LSN, 'all') AS c;
GO

DECLARE @From_LSN binary(10) = sys.fn_cdc_get_min_lsn('dbo_NewEmployees');

DECLARE @To_LSN binary(10) = sys.fn_cdc_get_max_lsn();

SELECT c.*, sys.fn_cdc_map_lsn_to_time(c.__$start_lsn) AS ChangeDateTime

FROM cdc.fn_cdc_get_all_changes_dbo_NewEmployees

(@From_LSN, @To_LSN, 'all') AS c;

SELECT c.*, sys.fn_cdc_map_lsn_to_time(c.__$start_lsn) AS ChangeDateTime

FROM cdc.fn_cdc_get_net_changes_dbo_NewEmployees

(@From_LSN, @To_LSN, 'all') AS c;

Now that returns:

You can see that the function returns us the time. The time is in local time for the server.

If you want to tear the example back down, just do this:

EXEC sys.sp_cdc_disable_db;
GO

DROP TABLE IF EXISTS dbo.NewEmployee;
GO

USE master;
GO

ALTER DATABASE CDC SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE IF EXISTS CDC;
GO

EXEC sys.sp_cdc_disable_db;

DROP TABLE IF EXISTS dbo.NewEmployee;

USE master;

ALTER DATABASE CDC SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE IF EXISTS CDC;

SDU Tools: Date Dimension Period Columns in SQL Server T-SQL

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we're especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE FUNCTION [SDU_Tools].[DateDimensionPeriodColumns]
(
    @Date date,
    @FiscalYearStartMonth int,
    @Today date
)
RETURNS TABLE
AS
-- Function:      Returns a table (single row) of date dimension period columns
-- Parameters:    @Date date => date to process
--                @FiscalYearStartMonth int => month number when the financial year starts
--                @Today date => the current day (or the target day)
-- Action:        Returns a single row table with date dimension period columns
-- Return:        Single row rowset with date dimension period columns
-- Refer to this video: https://youtu.be/pcoaHYK70nU
--
-- Test examples: 
/*

SELECT * FROM SDU_Tools.DateDimensionPeriodColumns('20200131', 7, SYSDATETIME());

SELECT db.DateValue, ddpc.* 
FROM SDU_Tools.DatesBetween('20190201', '20200420') AS db
CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc
ORDER BY db.DateValue;

SELECT ddc.*, ddpc.* 
FROM SDU_Tools.DatesBetween('20200201', '20200401') AS db
CROSS APPLY SDU_Tools.DateDimensionColumns(db.DateValue, 7) AS ddc
CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc
ORDER BY db.DateValue;

*/
RETURN 

WITH FiscalYearDates
AS
(
    SELECT CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth
                THEN CAST(CAST(YEAR(@Today) AS varchar(4)) 
                               + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                               + '01' AS date)
                ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4)) 
                               + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                               + '01' AS date)
           END AS StartOfFiscalYear,
           DATEADD(day, -1, 
                   DATEADD(year, 1, 
                           CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth
                                THEN CAST(CAST(YEAR(@Today) AS varchar(4)) 
                                          + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                                          + '01' AS date)
                                ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4)) 
                                          + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                                          + '01' AS date)
           END)) AS EndOfFiscalYear
)
SELECT CASE WHEN @Date = @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsToday,
       CASE WHEN @Date = DATEADD(day, -1, @Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsYesterday,
       CASE WHEN @Date = DATEADD(day, 1, @Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsTomorrow,
       CASE WHEN @Date > @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsFuture,
       CASE WHEN DATEPART(weekday, @Date) 
                 NOT IN (DATEPART(weekday, '19000106'), -- Saturday
                         DATEPART(weekday, '19000107')) -- Sunday
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsWorkingDay,
       CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000108') -- Monday
                              THEN DATEADD(day, -3, @Today)
                              ELSE DATEADD(day, -1, @Today)
                         END 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastWorkingDay,
       CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000105') -- Friday
                              THEN DATEADD(day, 3, @Today)
                              ELSE DATEADD(day, 1, @Today)
                         END 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsNextWorkingDay,
       CASE WHEN DATEPART(weekday, @Date) 
                 IN (DATEPART(weekday, '19000106'), -- Saturday
                     DATEPART(weekday, '19000107')) -- Sunday
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsWeekend,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameMonth,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)
                                              AND DAY(@Date) BETWEEN 1 AND DAY(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsMonthToDate,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameMonthLastYear,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)
                                              AND DAY(@Date) BETWEEN 1 AND DAY(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsMonthToDateLastYear,
       CASE WHEN YEAR(@Date) = YEAR(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameCalendarYear,
       CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) AS varchar(4)) + '0101' AS date)
                       AND @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsCalendarYearToDate,
       CASE WHEN YEAR(@Date) = (YEAR(@Today) - 1)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastCalendarYear,
       CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) - 1 AS varchar(4)) + '0101' AS date)
                       AND DATEADD(year, -1, @Today) 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastCalendarYearToDate,
       CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND fyd.EndOfFiscalYear 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameFiscalYear,
       CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND @Date  
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameFiscalYearToDate,
       CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear) 
                       AND DATEADD(year, -1, fyd.EndOfFiscalYear)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastFiscalYear,
       CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear) 
                       AND DATEADD(year, -1, @Date)  
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastFiscalYearToDate
FROM FiscalYearDates AS fyd;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

CREATE FUNCTION [SDU_Tools].[DateDimensionPeriodColumns]

(

@Date date,

@FiscalYearStartMonth int,

@Today date

)

RETURNS TABLE

-- Function: Returns a table (single row) of date dimension period columns

-- Parameters: @Date date => date to process

-- @FiscalYearStartMonth int => month number when the financial year starts

-- @Today date => the current day (or the target day)

-- Action: Returns a single row table with date dimension period columns

-- Return: Single row rowset with date dimension period columns

-- Refer to this video: https://youtu.be/pcoaHYK70nU

-- Test examples:

SELECT * FROM SDU_Tools.DateDimensionPeriodColumns('20200131', 7, SYSDATETIME());

SELECT db.DateValue, ddpc.*

FROM SDU_Tools.DatesBetween('20190201', '20200420') AS db

CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc

ORDER BY db.DateValue;

SELECT ddc.*, ddpc.*

FROM SDU_Tools.DatesBetween('20200201', '20200401') AS db

CROSS APPLY SDU_Tools.DateDimensionColumns(db.DateValue, 7) AS ddc

CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc

ORDER BY db.DateValue;

RETURN

WITH FiscalYearDates

(

SELECT CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth

THEN CAST(CAST(YEAR(@Today) AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

END AS StartOfFiscalYear,

DATEADD(day, -1,

DATEADD(year, 1,

CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth

THEN CAST(CAST(YEAR(@Today) AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

END)) AS EndOfFiscalYear

)

SELECT CASE WHEN @Date = @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsToday,

CASE WHEN @Date = DATEADD(day, -1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsYesterday,

CASE WHEN @Date = DATEADD(day, 1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsTomorrow,

CASE WHEN @Date > @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsFuture,

CASE WHEN DATEPART(weekday, @Date)

NOT IN (DATEPART(weekday, '19000106'), -- Saturday

DATEPART(weekday, '19000107')) -- Sunday

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsWorkingDay,

CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000108') -- Monday

THEN DATEADD(day, -3, @Today)

ELSE DATEADD(day, -1, @Today)

END

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastWorkingDay,

CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000105') -- Friday

THEN DATEADD(day, 3, @Today)

ELSE DATEADD(day, 1, @Today)

END

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsNextWorkingDay,

CASE WHEN DATEPART(weekday, @Date)

IN (DATEPART(weekday, '19000106'), -- Saturday

DATEPART(weekday, '19000107')) -- Sunday

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsWeekend,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameMonth,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)

AND DAY(@Date) BETWEEN 1 AND DAY(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsMonthToDate,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameMonthLastYear,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)

AND DAY(@Date) BETWEEN 1 AND DAY(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsMonthToDateLastYear,

CASE WHEN YEAR(@Date) = YEAR(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameCalendarYear,

CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) AS varchar(4)) + '0101' AS date)

AND @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsCalendarYearToDate,

CASE WHEN YEAR(@Date) = (YEAR(@Today) - 1)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastCalendarYear,

CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) - 1 AS varchar(4)) + '0101' AS date)

AND DATEADD(year, -1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastCalendarYearToDate,

CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND fyd.EndOfFiscalYear

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameFiscalYear,

CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND @Date

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameFiscalYearToDate,

CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear)

AND DATEADD(year, -1, fyd.EndOfFiscalYear)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastFiscalYear,

CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear)

AND DATEADD(year, -1, @Date)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastFiscalYearToDate

FROM FiscalYearDates AS fyd;

Opinion: Does a human respond to your website contact requests?

Most websites that I visit have a link at the bottom of the page that suggests that you can use it to contact either the website team or the company that owns the site. (Might not be the same people) Based on years of trying, my expectation of ever getting a response from using one of these links is close to zero.

If you have a website that has a contact link, does it lead anywhere sensible?

Does it have a contact form that sends the request to an email address that no human ever monitors?

I see two common issues: the website creator bit bucket, and the sales-proof company.

Website Creator Bit Bucket

This is one of my least favourite issues. Countless websites have flaws that stop you interacting with them. Often, the team that designed the site might have included a link for letting them know if you have a technical issue with the site.

So often, the team that built the site has moved on, perhaps aren't even associated with the company any more, and the output of the contact form might as well go straight in the bin. No-one is ever going to see it.

Sales-Proof Company

This is the worst of the two, and it's especially important in the current pandemic-related situation. I'll give you an example.

There's a tech company in Melbourne that I've loved to deal with in the past. Their people are knowledgeable and friendly and are just pleasant to deal with.

So when I wanted to buy another high-end NVMe drive the other day, they were the first people I checked. Their site said they had them in stock, the price was fine, and I decided to buy one from them. However, their online order entry application would not allow me to enter the correct billing address for my credit card, based on silly rules. (I'll write more about that another day).

Bottom line is that I couldn't complete the order. If that's happened in the past, I've called them, and they've processed it over the phone. But with COVID-19 happening, they haven't managed to get a good system to divert calls to their own sales people working from home. So they just have a note saying their phones are temporarily not being answered. (Mistake #1)

I had no choice but to use their "Sales Enquiry" contact form.

The Wait

I waited, and waited and heard nothing. (Mistake #2)

After two days, I ordered the drive from another supplier, and it arrived quickly.

Nearly three weeks after I filled in their contact form, the first company did send me an email to check if I still needed help. But that was way, way too late.

But they did respond

On the positive side, they did at least contact me. I've read reports that say that up to 80 percent of online sales enquiry forms are never responded to at all.

Don't be one of these companies!

T-SQL 101: #69 Extracting date components in SQL Server T-SQL with DAY, MONTH, and YEAR

When you're writing T-SQL code, it's really common to need to extract components of a date, and the most common need is for year, month, and day. So T-SQL has separate functions just for that.

You can see the output from this query here:

The YEAR(), MONTH(), and DAY() functions take a date (or datetime, datetime2, smalldatetime) as input and return an integer for the year, month, or day.

You can also do this with the DATEPART() function but these are simpler and clearer when you want these main components of a date.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Qixi Festival on postage stamps

I wrote my last two Mandarin blog posts about the Qixi festival and the legend of the Cowherd and Weaver Girl. I wanted to round out that discussion today.

In the West, we have a few tales like Santa Clause, and the Easter Bunny that seem pervasive. St Valentine isn't such a big deal, even though Valentine's Day is named after him.

People who haven't studied China much usually don't understand just how embedded these tales are within society, and also in the language. I see old tales appearing as idioms that end up meaning something quite different to what they say.

One place that I've seen Qixi appearing is in postage stamps. You can see one set in the main image above.

Here's another set:

Qixi postage stamp — Awesome image from youlai18

These are more recent but note the price hasn't changed. (Unlike our local postage prices)

Qizi postage stamp — Awesome image from youlai18

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: How do I stop my database files going into the Users folder with localdb?

I've seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn't seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it's trying to find a location that the current user will have permission to write to. While I understand the rationale, that's not where most users will want those files.

The answer is to not let localdb pick where the files will go.

Instead of writing:

CREATE DATABASE Sales;

1	CREATE DATABASE Sales;

use a more complete command like:

CREATE DATABASE Sales
ON
(
    NAME = 'Sales_Data',
    FILENAME = 'C:\SomeAppropriatePath\Sales.mdf'
)
LOG ON
(
    NAME = 'Sales_Log',
    FILENAME = 'C:\SomeAppropriatePath\Sales.ldf'
);

CREATE DATABASE Sales

(

NAME = 'Sales_Data',

FILENAME = 'C:\SomeAppropriatePath\Sales.mdf'

)

LOG ON

(

NAME = 'Sales_Log',

FILENAME = 'C:\SomeAppropriatePath\Sales.ldf'

);

While you're at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.