Here's a tvf

drop function if exists dbo.fn_test_scnd_thurs;
go
create function dbo.fn_test_scnd_thurs(
  @test_date          date)
returns table
as return 
select dateadd(day, 9, calc_dt.dt)...

• This reply was modified 5 years, 9 months ago by Steve Collins.

Ah I didn't see what was posted.  My...

Steve, if you're going to use the fnTally function, you need to tell people where to get it.  Since I use it so often, I just put the link...

Even if fnTally is not available it's still a useful article to read.  Any table with reliably more than 31 rows could be used tho.   In this case I created...

When you write "which is followed with..." it seems you're intending it to mean "which follows the..."?   The following has 2 Common Table Expressions: 1) 'test_tues_cte' which contains all of...

The JSON is oddly formed.  Are you 100% sure this is the format required?  The inner JSON items are in array list format but there's no array wrapper. I don't...

One way or another I would like to come up with a way to deal with JSON schemas.  If I observe how my time is spent and do the "5...

but my point is that I don't want to assign a value to a variable to start creating hierarchies for all accounts in my chart of accounts. Any ideas...

Well, I'm not really experienced with hierarchyid other than fiddling around with it.  Most of what can be accomplished with hierarchyid can also be done in various other ways.  In...

• This reply was modified 5 years, 9 months ago by Steve Collins.

with t2_latest_cte as (
    select *, row_number() over (partition by RunDate 
            ...

When I run each query and include the actual execution plan the results are identical.  Both do one table scan and a constant scan on the results.  So yes they...

drop table if exists #Customer
go
create table #Customer(
  CIDint  not null,
  WholeSaler        bit not null,
  ReSalerbit not...

select year(eomonth(getdate(),-1))

The way to restrict the number of rows the MERGE is applied...

Since you're counting across months the quick way to generate the daily rows would be to use a tally function.  This uses dbo.fnTally which see this link for details. ...