I was going to report the same thing. The article should be updated to add rows to both tables, and to explain the usefulness of the joins, in restricting...

*facepalm* Sorry, I keep an eye on several forums, I forgot this one had that level of granularity....

Am I right in thinking your solution requires SS2012 ? Did we discuss that already ?

I would agree that there's some confusion, and the OP needs to clear it up. I worked based on the description of the task, and the sample data given...

There are four 'B' rows, the closed dates are:

2013-08-15 00:00:00.000

2013-08-02 00:00:00.000

2013-09-03 00:00:00.000

2013-12-01 00:00:00.000

1970-01-01 00:00:00.000

Three of those are on or after 2013-01-01. Ergo, my SQL is doing what the OP...

I did slog through it and found some bugs in what I had posted. I believe what I just posted, does return what he wants, and anticipates the possibility...

As far as I can see, the due day is either the starting day, or it's the next day, which is meant to be calculated, using that string value. ...

Assuming it's the network impact you care about, that's precisely the metric that matters. Your network does not care how hard SQL Server had to work to create the...

Yes, but the starting point is, which days fall under '3 days a week' or '8 times a month'. That's why I asked the OP to define business rules,...

Yes, you have missed the point. The join is easy. Parsing strings like '3 times a week' and 'daily' and 'every second Monday' and then deciding if that...

My solution did not work if either value was zero. This does:

DECLARE @date datetime = '2013-01-01'

;WITH opened

AS(

SELECT [group], count(1) as [count] FROM #TempTable WHERE cast(createDate as date) <= @DATE...

It's a subquery and so it will affect performance. It's good practice to avoid subqueries where-ever possible. The approach I took, assumed you were looking for an aggregate...

Thanks - good to know.

I did not know that a clustered index is the default for a PK. Doesn't a clustered index mean the entire row is written in the index ?

lead/lag is awesome, I have 2012 🙂

And I was wrong again. He's after a count, so I'm not sure how lead/lag help, esp as he wants to count two...