ALTER PROCEDURE [dbo].[spInventoryAge]
    -- Add the parameters for the stored procedure here
    @whID int = 0 ,
    @ageOne smallint =30,
    @ageTwo smallint =60,
    @ageThree smallint =90
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    create table #inventoryAge 
    (
        whID int,
        whName nvarchar(30),
        placeID smallint,
        placeName nvarchar(30),
        resourceID int,
        resourceName nvarchar(198),
        quantity decimal(18,4),
        ageStage1 decimal(18,4),
        ageStage2 decimal(18,4),
        ageStage3 decimal(18,4),
        ageStage4 decimal(18,4)
    )
    
    create table #MaterialAge 
    (
        resourceID int,
        resourceName nvarchar(198),
        quantity decimal(18,4),
        ageStage smallint
    )
    
    declare @currentDate datetime
    set @currentDate=GETDATE()
    
    insert into #inventoryAge
        select I.whID,w.whName,I.placeID,I.placeName,I.resourceID,I.resourceName,
               I.quantity,0,0,0,0
        from dbo.vwInventory as I inner join
             dbo.Warehouse as W on I.whID=W.whID
    
    insert into #MaterialAge
        select EI.resourceID,EI.resourceName,sum(EI.quantity) as quantity,EI.ageStage
        from 
        (
                select   
                     WI.resourceID,WI.resourceName,wi.quantity, 
                     case when @currentDate-W.entryDate<@ageOne then 1
                     when @currentDate-W.entryDate<@ageTwo then 2
                     when @currentDate-W.entryDate<@ageThree then 3
                     else 4  end as ageStage 
                from    
                     dbo.vwWarehouseEntryItem as WI inner join
                     dbo.WarehouseEntry as W ON W.entryID=WI.entryID
        ) EI
        group by EI.resourceID,EI.resourceName,EI.ageStage
    
    declare @updateSql varchar(max)
             
    set @updateSql='';
    select @updateSql=@updateSql+' ageStage'+LTRIM(M.ageStage)
           +'=max(case when ageStage='+LTRIM(M.ageStage)+' then quantity else 0 end)'
        from @MaterialAge as M group by M.ageStage
    --打印出来看看   
    print @updateSql
    exec('update @#inventoryAge set '+@updateSql+' 
          from @MaterialAge group by resourceID,resourceName')

    set nocount off;