Needs sample data or it's unlikely to work as intended the first time.

alter procedure [dbo].[_BARank]
as
set nocount on;
set xact_abort on;

begin transaction
begin try
    Declare
      @executor        ...

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

delete tc
from
  #TradeCode tc
where not exists
  (select 1 from #MappingCodeValue mcv 
   where mcv.ChildCodeType=tc.CodeType 
        ...

This puts it all together in 2 styles.

/* Jeff's style */
 SELECT  Cust_num, Co_num
        ,Total    ...

Very very nice pre-aggregation!  Now thaaaat's the way to do it.

This is great article!  I'm going to give it a try.

select
  Cust_num, Co_num,
  sum(amount) Total,
  sum(iif(floor(datediff(d,due_date,getdate())/30)=0,amount,0)) [1-30 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)=1,amount,0)) [31-60 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)=2,amount,0)) [61-90 Days],
  sum(iif(floor(datediff(d,due_date,getdate())/30)>2,amount,0)) [>90 Days]
from EES_App.dbo.artran_all
group by Cust_num, Co_num;

I think this is horrible advice. The customer number is an identifier for a customer (I assume), and not a magnitude or quantity of some attribute. Therefore, according to...

Something like this maybe

;with
SiteInfo(SiteId, SiteName, JobId, Grade) as (
    select 1, 'aaa', 1, 'a'
    union all
    select 1,...

To see more about what Jonathan's suggesting maybe have a look at this page:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

It could be looked at like there're 2 ways to "include" columns in an index: 1) as...

drop table if exists #tblRecorddetails;
go
create table #tblRecorddetails(
  Recordid          int,
  Userid      ...

drop table if exists #temp;
go
create table #temp (
  userid int , rid varchar(10), value varchar(100));

insert #temp(userid,rid,value) values
(1,'D01','3'),
(1,'D01','4'),
(2,'C01','hey'),
(2,'C01','1');

with t_cte(userid,rid,value) as (
    select
  ...

Maybe this could work too

select 
  coalesce(ph.username, (select Message from itrm_itbf.temp.security))
from
  ITRM_ITBF.[PMD].[PMD_HR] ph
 join
  ITRM_ITBF.[Dflt].[MANUAL_LSV_PERMISSIONS] mlp on ph.username=mlp.username
      ...

Instead of returning the SSN (which is being sent to the procedure already) could you return COUNT(*)?  Count always returns a value.

It's data obfuscation project, thus no strict rules what value should be, rather it should be random. Each row should have its own value. When we are out of...