https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

https://www.sqlservercentral.com/blogs/tally-tables-in-t-sql

declare
  @start_yr         int=2020,
  @years            int=20;


;with
tally(n) as
(
...

The OP could generate as many years as needed with a tally table 🙂

declare
  @start_yr         int=2020,
  @years...

• This reply was modified 5 years, 2 months ago by Steve Collins. Reason: union all

Fwiw, adding meta data like ('ORD'+number and 'MO'+date) to database keys is not the best practice.  If you're stuck with it perhaps it's possible to add constraints which only allow...

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

Isn't this the same question from 3 weeks ago?

https://www.sqlservercentral.com/forums/topic/date-spans-for-continuoes-and-regular-dates-spans#post-3763906

There were some good answers offered

Another way

select s.salesman_id, s.salesman
from
  #salesman s
 cross apply 
 (SELECT COUNT(*) cnt FROM #customer  c WHERE c.salesman_id=s.salesman_id) xa
where
  xa.cnt>1;

drop table if exists #salesman;
go
create table #salesman(salesman_id int, salesman varchar(20));
go

insert #salesman(salesman_id, salesman) values 
(1, 'Tedd'),
(2, 'Fred'),
(3, 'Ed');

drop table if exists #customer;
go
create table #customer(customer_id int, salesman_id int, customer...

No...

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

Ok now it makes sense to me.   For some reason I thought the object_id() was going in the query and not the cursor.  In Azure SQL it's not allowed to...

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 5 years, 3 months ago by Steve Collins.