Optimize Query

Could you give the following a try ?

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
max(T1.cur_prior_inc), max(cur_next_inc), max(cur_end_date)
from NYGICSPNL A
left outer join #rk3 T1 ON T1.contract=A.contract and T1.cur_end_date=A.[end date]
where A.[end date] = '4/27/2005' and A.contract is not null
group by A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle
order by 2,1

if above fits your requirements, this version does roughly the same but should be faster

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
cur_prior_inc, cur_next_inc, cur_end_date
from NYGICSPNL A
left outer join (
      select contract, max(cur_prior_inc) as cur_prio_inc,
                       max(cur_next_inc) as cur_next_inc, max(cur_end_date) as cur_end_date
      from #rk3  Where cur_end_date='4/27/2005'
      group by contract
) B on A.Contract = B.Contract
where A.[end date] = '4/27/2005'
order by 2,1

Getting Invalid colum name in cur_prior_inc, cur_next_inc, cur_end_date

These columns come from #rk3 and not nygicspnl and how do I join them ? Meanign its goign to be same

Nope. I am getting NULL in cur_prior_inc, cur_next_inc, cur_end_date

please try

select A.Contract,
case when A.[Product Type]='DEPOSIT' then
(case when  isnull(A.[Next Inc],0) - isnull(A.[Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
else A.[Product Type] end ,
isnull(A.[Prior Inc]*1000000,0),isnull(A.[Next Inc]*1000000,0),A.[End Date], A.settle,
B.cur_prior_inc, B.cur_next_inc, B.cur_end_date
from NYGICSPNL A
left outer join (
      select contract, max(cur_prior_inc) as cur_prio_inc,
      max(cur_next_inc) as cur_next_inc, max(cur_end_date) as cur_end_date
      from #rk3  Where cur_end_date='4/27/2005'
      group by contract
) B on A.Contract = B.Contract
where A.[end date] = '4/27/2005'
order by 2,1

That works but still 22 seconds and that could casue me a timeout on my front end app. Any other ways to optimize ?

>>still 22 seconds <<
is it better than your initial version ?

>>Any other ways to optimize ?<<
Difficult to say whithout knowing the data model

Is 22 seconds just for this part, or do the 22 seconds include the #temp table filling and/or other processes ?

An index on NYGICSPNL ([end date]) would help, most likely
If you don't have one the query might perform a full scan on the whole table ...

Can you give more details on the surrounding code ?

22 seocnds is defintely better. But it could go upto much more as the # of rows goes up. I already have e index on [end date]. I have a SP that calculates something for the entire year for each contract and each date and users gets timed out and planning to have a physical table to sore historical data and only process todays data.
Here is this SP

--drop PROCEDURE up_Summary_Pnl_ravi
--exec up_Summary_Pnl_ravi '12/10/2004'

CREATE PROCEDURE up_Summary_Pnl_ravi
@currentdate varchar(10)
AS

if object_id('tempdb..#contract', 'u') is not null drop table #contract
if object_id('tempdb..#rk3', 'u') is not null       drop table #rk3

declare @contract varchar(30)

create table #rk3
   (      contract varchar(50) null,
      Product_Type varchar(25),

      cur_Prior_Inc numeric(20,6)  ,
          cur_Next_Inc numeric(20,6),
      cur_End_Date smalldatetime,

      pre_Prior_Inc numeric(20,6)  ,
      pre_Next_Inc numeric(20,6),
      pre_End_Date smalldatetime,

      Daily numeric(20,6)  ,
          BackValue numeric(20,6),
      settle smalldatetime,
      seq int )

select distinct Contract
into #contract
from NYGICSPNL
where contract is not null


while ( select count(*) from #contract) > 0
begin

set rowcount 1
select @contract = contract from #contract

set rowcount 0

delete #contract where contract = @contract


if object_id('tempdb..#rk', 'u') is not null       drop table #rk

if object_id('tempdb..#rk2', 'u') is not null       drop table #rk2

create table #rk
   (      contract varchar(50) null,
      Product_Type varchar(25),

      cur_Prior_Inc numeric(20,6)  ,
          cur_Next_Inc numeric(20,6),
      cur_End_Date smalldatetime,
      settle smalldatetime,
      seq int identity )

      insert into #rk
      select Contract,
      case when [Product Type]='DEPOSIT' then
      (case when  isnull([Next Inc],0) - isnull([Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
      else [Product Type] end ,
      isnull([Prior Inc]*1000000,0),isnull([Next Inc]*1000000,0) ,[End Date],settle
      from NYGICSPNL
      where contract = @contract
      order by 1,5

      select       contract,
            product_type,
            cur_prior_inc,
            cur_next_inc,
            cur_end_date,
            settle,
            seq +1 seq
      into #rk2
      from #rk


insert into #rk3
select       a.contract,
      a.Product_Type,
      a.cur_Prior_Inc,
          a.cur_Next_Inc,
      a.cur_End_Date,
      0,
          a.cur_Prior_Inc,
      a.cur_End_Date,
      0,
      0,
      a.settle,
      a.seq
from #rk a
where seq = 1
      
insert into #rk3
select       a.contract,
      a.Product_Type,
      a.cur_Prior_Inc,
          a.cur_Next_Inc,
      a.cur_End_Date,
      b.cur_Prior_Inc,
          b.cur_Next_Inc,
      b.cur_End_Date,
      0,
      0,
      a.settle,
      a.seq
from #rk a, #rk2 b
where a.seq = b.seq

end

update #rk3
set       Daily = cur_Next_Inc - cur_Prior_Inc,
          BackValue = cur_Prior_Inc - pre_Next_Inc


if object_id('tempdb..#temp100', 'u') is not null
      drop table #temp100

create table #temp100
   (myProduct varchar(50) not null,Daily numeric(20,6) ,Monthly numeric(20,6),Yearly numeric(20,6),Inception numeric(20,6))

insert into #temp100
select distinct product_type ,0,0,0,0
from #rk3

update #temp100
set Daily= s.Daily
from (
select product_type ,
sum(daily + backvalue ) as Daily from #rk3
where cur_end_date= @currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Monthly = s.Monthly
from (
select product_type ,
sum(daily + backvalue ) as Monthly from #rk3
where cur_end_date >=(select DATEADD(mm, DATEDIFF(mm,0,@currentdate), 0)) and cur_end_date<=@currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Yearly = s.Yearly
from (
select
product_type ,sum(daily + backvalue ) as Yearly from #rk3
where cur_end_date >=(select DATEADD(yy, DATEDIFF(yy,0,@currentdate), 0)) and cur_end_date<=@currentdate
group by product_type ) s
where s.Product_type=#temp100.myproduct

update #temp100
set Inception = s.Inception
from (
select
product_type ,
sum(daily + backvalue )  as Inception from #rk3            --Inception
where cur_End_Date>=settle and cur_End_Date <=@currentdate
group by product_type) s
where s.Product_type=#temp100.myproduct


select *,@currentdate as currentdate from #temp100 order by 1

GO

This one should be faster, but can definitely be optimized further
Can you give it a try

CREATE PROCEDURE up_Summary_Pnl_ravi
@currentdate varchar(10)
AS

if object_id('tempdb..#contract', 'u') is not null drop table #contract
if object_id('tempdb..#rk3', 'u') is not null      drop table #rk3

declare @contract varchar(30)

create table #rk3
   (     contract varchar(50) null,
     Product_Type varchar(25),

     cur_Prior_Inc numeric(20,6)  ,
         cur_Next_Inc numeric(20,6),
     cur_End_Date smalldatetime,

     pre_Prior_Inc numeric(20,6)  ,
     pre_Next_Inc numeric(20,6),
     pre_End_Date smalldatetime,

     Daily numeric(20,6)  ,
         BackValue numeric(20,6),
     settle smalldatetime,
     seq int )

select distinct Contract
into #contract
from NYGICSPNL
where contract is not null


while ( select count(*) from #contract) > 0
begin

set rowcount 1
select @contract = contract from #contract

set rowcount 0

delete #contract where contract = @contract


if object_id('tempdb..#rk', 'u') is not null      drop table #rk

if object_id('tempdb..#rk2', 'u') is not null      drop table #rk2

create table #rk
   (     contract varchar(50) null,
     Product_Type varchar(25),

     cur_Prior_Inc numeric(20,6)  ,
         cur_Next_Inc numeric(20,6),
     cur_End_Date smalldatetime,
     settle smalldatetime,
     seq int identity )

     insert into #rk
     select Contract,
     case when [Product Type]='DEPOSIT' then
     (case when  isnull([Next Inc],0) - isnull([Prior Inc],0) >= 0 then 'Deposit Placed' else 'Deposit Taken' end)
     else [Product Type] end ,
     isnull([Prior Inc]*1000000,0),isnull([Next Inc]*1000000,0) ,[End Date],settle
     from NYGICSPNL
     where contract = @contract
     order by 1,5

     select      contract,
          product_type,
          cur_prior_inc,
          cur_next_inc,
          cur_end_date,
          settle,
          seq +1 seq
     into #rk2
     from #rk


insert into #rk3
select      a.contract,
     a.Product_Type,
     a.cur_Prior_Inc,
         a.cur_Next_Inc,
     a.cur_End_Date,
     0,
         a.cur_Prior_Inc,
     a.cur_End_Date,
     0,
     0,
     a.settle,
     a.seq
from #rk a
where seq = 1
     
insert into #rk3
select      a.contract,
     a.Product_Type,
     a.cur_Prior_Inc,
         a.cur_Next_Inc,
     a.cur_End_Date,
     b.cur_Prior_Inc,
         b.cur_Next_Inc,
     b.cur_End_Date,
     0,
     0,
     a.settle,
     a.seq
from #rk a, #rk2 b
where a.seq = b.seq

end

update #rk3
set      Daily = cur_Next_Inc - cur_Prior_Inc,
         BackValue = cur_Prior_Inc - pre_Next_Inc

select product_type as myproduct,
sum(case when cur_end_date = @currentdate then daily + backvalue end) as Daily,
sum(case when cur_end_date between convert(varchar(8), @currentdate, 102)+'01' and @currentdate then daily + backvalue end) as Monthly,
sum(case when cur_end_date between datename(year,@currentdate)+ '.01.01' and @currentdate then daily + backvalue end) as Yearly,
sum(case when cur_end_date between Settle and @currentdate then daily + backvalue end) as Inception,
@currentdate as currentdate
from #rk3
group by product_type
order by 1

GO

Syntax error converting character string to smalldatetime data type.

Probably here

select product_type as myproduct,
sum(case when cur_end_date = @currentdate then daily + backvalue end) as Daily,
sum(case when cur_end_date between convert(varchar(8), @currentdate, 102)+'01' and @currentdate then daily + backvalue end) as Monthly,
sum(case when cur_end_date between datename(year,@currentdate)+ '.01.01' and @currentdate then daily + backvalue end) as Yearly,
sum(case when cur_end_date between Settle and @currentdate then daily + backvalue end) as Inception,
@currentdate as currentdate
from #rk3
group by product_type
order by 1