schleep, that sounds interesting.  It's a 3 part naming convention?  Is there a . missing before dbo.?  Maybe it's simpler to query the sys table and idk.  It could be...

Thanks Roger.  Regarding "Errors Unaffected by a Try/Catch Construct" the issues are mitigate-able (imo (all of what follows)) if handled properly (knock on wood).  I must be biased tho because...

Declare @msg varchar(100)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM Alldatabases

WHERE OBJECT_ID(Name + 'dbo.<tablename>') IS NOT NULL

ORDER BY Name

OPEN wh_cursor

In your post, you had 2 tables, Settings and Employee, so maybe...

TRY/CATCH doesn't catch everything. In fact it won't catch most of the things you really want it to so I simply avoid it completely. Nothing worse than an unreliable...

select 
  a.[name],
  sum(iif(b.gender='M', 1, 0)) m_count,
  sum(iif(b.gender='F', 1, 0)) f_count
from
  #department a
 join
  #employee b on a.departmentID = b.departmentID
group by 
...

.

There's a lot going on in the code which could be looked at.  Instead of EXEC(@qry) where the parameters are strung together it could be parameterized using sp_executesql.  In terms...

• This reply was modified 6 years ago by Steve Collins.

To get SQL Server to ignore and continue is possible from a script or procedure by executing another (or more...) stored procedure which contain try/catch where the error is output...

In theory it should be possible but maybe not easily.  The tvf is parameterized so I think to remove it would require splitting...

The query could be without the cte

select
  t1.id, cast(t1.startdate as date) startdate, cast(t1.enddate as date) enddate, cast(td.enddate as date) range_end_dt
from
  dbo.test_1 t1
 cross apply
 ...

The OP wrote: "output is expected as if member is continuously enrolled without any gap in enrollment get min and max and if there is enrollment gap leave as is." ...

To resolve for arbitrary relationships in the data requires hierarchical recursion, no?  The OP's question and Phil's too

drop table if exists dbo.test_1;
go
create table dbo.test_1(id int,startdate datetime ,enddate...

drop table if exists dbo.test_T1;
go
create table dbo.test_T1(
  ID1       varchar(8) not null,
  ID2       varchar(8)...

• This reply was modified 6 years ago by Steve Collins.

In 2016 there are some nice built in features to support time zone conversions

/* the list of time zones your instance supports */
select * from sys.time_zone_info;

declare
 ...

this

declare @frDate         date='2020-03-31';

with a_cte(DocType, Debit, Credit, Account, Mdate) as 
    (select 'TC', 10, 20, 123, '2020-03-30'
...