[Solved] SQL Server filter by date [closed]

Question

Posting as an answer, because I have a LOT of comments:

The RIGHT JOIN is certainly wrong. This would imply that some stores do not need to exist, but of course, your sales facts are tied to stores with an INNER JOIN. It is effectively being turned into an INNER JOIN anyway, so is unlikely to be the source of your issues.

The fact that you used a RIGHT JOIN tells me that you think there could be a case where a foreign key is optional in some of the facts or dimensions. I’d like to know why this is.

In any case, in a straightforward star model, you usually only see INNER JOINs and sometimes LEFT JOINs.

Because the fact table is at the center of the star, I would usually list that first in the SELECT, especially if there are LEFT JOINs out from the fact table.

Since it is a datawarehouse, and you probably have a lot of data, I’m not sure how much you can post so we can understand your expectations versus the results you are getting, but be aware that using BETWEEN includes both endpoints and that DATETIME datatype can contain a time portion. Because of this, I almost always prefer to use the notation dt >= start_date AND dt < end_date. It’s typically no performance difference, since BETWEEN is basically syntactic sugar for dt >= start_date AND dt <= end_date.

Accepted Answer

Posting as an answer, because I have a LOT of comments:

The RIGHT JOIN is certainly wrong. This would imply that some stores do not need to exist, but of course, your sales facts are tied to stores with an INNER JOIN. It is effectively being turned into an INNER JOIN anyway, so is unlikely to be the source of your issues.

The fact that you used a RIGHT JOIN tells me that you think there could be a case where a foreign key is optional in some of the facts or dimensions. I’d like to know why this is.

In any case, in a straightforward star model, you usually only see INNER JOINs and sometimes LEFT JOINs.

Because the fact table is at the center of the star, I would usually list that first in the SELECT, especially if there are LEFT JOINs out from the fact table.

Since it is a datawarehouse, and you probably have a lot of data, I’m not sure how much you can post so we can understand your expectations versus the results you are getting, but be aware that using BETWEEN includes both endpoints and that DATETIME datatype can contain a time portion. Because of this, I almost always prefer to use the notation dt >= start_date AND dt < end_date. It’s typically no performance difference, since BETWEEN is basically syntactic sugar for dt >= start_date AND dt <= end_date.