Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Sum function in outer Apply

Unanswered Sum function in outer Apply

  • Thursday, July 12, 2012 2:55 AM
     
      Has Code
    Sign In to Vote
    0

    HI, i had Error in this SQL script  which is sum the INV_QTY in outer apply function,  how to use group by in outer apply ?kindly advise. thank you

    select ROW_NUMBER() OVER(ORDER BY det.inv_id DESC) as RowNumber,
 s.inv_date as Date,det.inv_id as INV_ID,
 s.INV_TYPE as Type,
 sum(s.INV_QTY - (s.INV_USAGE * s.RowNumber)) as OnHandbalance,
 s.INV_USAGE as Demand,
 cast(s.INV_QTY /s.INV_USAGE - s.RowNumber as int) as DSB, 
 s.INV_USAGE * 7 as WeeklyUsage
  
  from OTH_INV_DETAILS det
   

  outer apply
  (
  
  select ROW_NUMBER() OVER(ORDER BY det.INV_ID DESC) as RowNumber, 
  date1.INV_DATE,a.inv_id ,a.INV_TYPE,a.INV_USAGE, b.inv_qty , b.INV_LOCATION
  
  from OTH_INV_DETAILS a join OTH_INV_QTY_LOC b on a.INV_ID = b.INV_ID 

outer apply
(
  select INV_DATE from OTH_INV_CAL
  )date1
  
 
    where 1=1
   

    and date1.INV_DATE >= '2012-07-01' and date1.INV_DATE <= '2012-12-31'
    and a.INV_ID= 'C5S-CLR-002'
    and b.INV_LOCATION= 'Engineering'
    
)s

outer apply
(
select  a.inv_id, a.inv_location ,  b.INV_USAGE, (s.INV_QTY - b.INV_USAGE)  as a
from OTH_INV_QTY_LOC a
join OTH_INV_DETAILS b
on a.INV_ID = b.INV_ID

outer apply
(
  select INV_DATE from OTH_INV_CAL
)date1
  
where 1=1
and a.INV_ID = s.INV_ID
and date1.inv_DATE < s.INV_DATE
) countdata

where 1=1
and det.inv_id = s.INV_ID


    • Edited by Caulson Thursday, July 12, 2012 2:57 AM
    •  
     

All Replies

  • Thursday, July 12, 2012 3:00 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    This is weird piece of code

    outer apply
(
select  a.inv_id, a.inv_location ,  b.INV_USAGE, (s.INV_QTY - b.INV_USAGE)  as a
from OTH_INV_QTY_LOC a
join OTH_INV_DETAILS b
on a.INV_ID = b.INV_ID

outer apply
(
  select INV_DATE from OTH_INV_CAL
)date1
  
where 1=1
and a.INV_ID = s.INV_ID
and date1.inv_DATE < s.INV_DATE
) countdata
    You can not do OUTER APPLY inside the OUTER APPLY

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, July 12, 2012 3:04 AM
     
      Has Code
    Sign In to Vote
    0

    Hi, Naomi, this SQL code  process get the final result i want..Hope can get your advise. May i know how to use group by in outer apply ? because i stack in. thank you.

    sum(s.INV_QTY - (s.INV_USAGE * s.RowNumber)) as OnHandbalance,

     
  • Thursday, July 12, 2012 3:07 AM
    Moderator
     
     
    Sign In to Vote
    0
    I don't think I understand you or this complex code.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog