Preparation of entry list according to subject

  • Hi,
    According to the purpose of the output, the output of the last entry on the output date as LIFO

    IF OBJECT_ID('TempDB..##SQLCENTER') IS NOT NULL
    DROP TABLE ##SQLCENTER
    ;
    CREATE TABLE [dbo].##SQLCENTER
    (
    [ID] [int] NOT NULL,
    [DATE_] DATETIME NOT NULL,
    [AMOUNT] DECIMAL(38,2) NULL,
    [PRICE] DECIMAL(38,2) NULL)
    INSERT INTO [dbo].##SQLCENTER (ID,DATE_,AMOUNT,PRICE)
    SELECT '1',CONVERT(DATETIME,'01.01.2018',104),'11','100' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.03.2018',104),'10','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.08.2018',104),'10','500'
    ;
    IF OBJECT_ID('TempDB..##OUTER') IS NOT NULL
    DROP TABLE ##OUTER
    ;
    CREATE TABLE [dbo].##OUTER
    (
    [ID] [int] NOT NULL,
    [DATE_] DATETIME NOT NULL,
    [AMOUNT] DECIMAL(38,2) NULL,
    [PRICE] DECIMAL(38,2) NULL)
    INSERT INTO ##OUTER (ID,DATE_,AMOUNT,PRICE)
    SELECT '1',CONVERT(DATETIME,'01.04.2018',104),'2','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.06.2018',104),'4','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.07.2018',104),'7','157.44'

    List to have,

    IDDATE_AMOUNTPRICE
    12018-01-01 00:00:00.0008100
    12018-08-01 00:00:00.00010500
  • meryemkurs072 - Monday, March 19, 2018 2:21 PM

    Hi,
    According to the purpose of the output, the output of the last entry on the output date as LIFO

    IF OBJECT_ID('TempDB..##SQLCENTER') IS NOT NULL
    DROP TABLE ##SQLCENTER
    ;
    CREATE TABLE [dbo].##SQLCENTER
    (
    [ID] [int] NOT NULL,
    [DATE_] DATETIME NOT NULL,
    [AMOUNT] DECIMAL(38,2) NULL,
    [PRICE] DECIMAL(38,2) NULL)
    INSERT INTO [dbo].##SQLCENTER (ID,DATE_,AMOUNT,PRICE)
    SELECT '1',CONVERT(DATETIME,'01.01.2018',104),'11','100' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.03.2018',104),'10','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.08.2018',104),'10','500'
    ;
    IF OBJECT_ID('TempDB..##OUTER') IS NOT NULL
    DROP TABLE ##OUTER
    ;
    CREATE TABLE [dbo].##OUTER
    (
    [ID] [int] NOT NULL,
    [DATE_] DATETIME NOT NULL,
    [AMOUNT] DECIMAL(38,2) NULL,
    [PRICE] DECIMAL(38,2) NULL)
    INSERT INTO ##OUTER (ID,DATE_,AMOUNT,PRICE)
    SELECT '1',CONVERT(DATETIME,'01.04.2018',104),'2','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.06.2018',104),'4','200' UNION ALL
    SELECT '1',CONVERT(DATETIME,'01.07.2018',104),'7','157.44'

    List to have,

    IDDATE_AMOUNTPRICE
    12018-01-01 00:00:00.0008100
    12018-08-01 00:00:00.00010500

  • Sorry, but I doubt anyone has any idea what you are looking for.   Could be a language barrier problem, but a fairly clear English explanation is needed here.   The words used in your post don't really say anything sufficiently specific.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I do not know english.
    Google translates only so much  🙂
    The first-in, last-out logic is this

  • ORDER BY LIFO_COLUMN DESC

  • The solution I'm looking for

    drop table #t
    drop table #r
    create table #T
    (
      stockId int not null,
      dealId int identity (1,1) not null,
      dealType char(1) not null,
      stockDate datetime not null,
      stockAmount DECIMAL(38,2) not null,
      pricePerStock DECIMAL(38,2) not null
    );
    insert into #T (stockId, dealType, stockDate, stockAmount, pricePerStock) values
      (111, 'B', '01.01.2010', 11, 100),
      (111, 'B', '01.03.2010', 10, 200),
      (111, 'S', '01.04.2010', 2, 200),
      (111, 'S', '01.06.2010', 4, 200),
      (111, 'S', '01.07.2010', 7, 157.44),
      (111, 'B', '01.08.2010', 10, 500);
    create table #R
    (
      lvl int not null,
      stockId int not null,
      dealId int not null,
      stockDate datetime not null,
      stockAmount int not null,
      pricePerStock int not null,
      stockRemaining int not null,
      amountDeducted int not null
    );
    insert into #R (lvl, stockId, dealId, stockDate, stockAmount, pricePerStock, stockRemaining, amountDeducted)
    select 0, stockId, dealId, stockDate, stockAmount, pricePerStock, stockAmount as stockRemaining, 0 as amountDeducted
    from #T
    where dealtype = 'B'
    declare @rowCount int =1;
    declare @lvl int = 0;
    while @rowCount > 0
    begin
      set @lvl = @lvl + 1;
      with sells as (
       select stockId, dealId as saleId, row_number() over (order by dealId) as sellNum, stockAmount as sellAmount
       from #T where dealType = 'S'
      )
      update #R
      set lvl = @lvl, /* debugging only */
       amountDeducted = (select sellAmount from sells where sellNum = @lvl), /* debugging only */
       stockRemaining = (
        select stockRemaining
        from (
          select dealId,
           case
            when r.stockRemaining + s.sellAmount < sum(stockRemaining) over (order by dealId desc) then r.stockRemaining
            when sum(stockRemaining) over (order by dealId desc) < s.sellAmount then 0
            else sum(stockRemaining) over (order by dealId desc) - s.sellAmount
           end as stockremaining
          from sells s inner join #R r on r.stockId = s.stockId and r.dealId < s.saleId
          where s.stockId = #R.stockId and s.sellNum = @lvl
        ) data
        where dealId = #R.dealId
       )
      where dealId < (select saleId from sells where sellNum = @lvl);

      set @rowCount = @@rowCount;
      if @rowCount >0 select * from #R;
    end

  • Hi

    Error message

    declare @T table
    (
    stockId int not null,
    dealId int identity (1,1) not null,
    dealType char(1) not null,
    stockDate datetime not null,
    stockAmount DECIMAL(38,2) not null,
    pricePerStock DECIMAL(38,2) not null
    );
    insert into @T (stockId, dealType, stockDate, stockAmount, pricePerStock) values
    (111, 'B', '01.01.2010', 11, 100),
    (111, 'B', '01.03.2010', 10, 200),
    (111, 'S', '01.04.2010', 2, 200),
    (111, 'S', '01.06.2010', 4, 200),
    (111, 'S', '01.07.2010', 7, 157.44),
    (111, 'B', '01.08.2010', 10, 500);
    DECLARE @r table
    (
    lvl int not null,
    stockId int not null,
    dealId int not null,
    stockDate datetime not null,
    stockAmount int not null,
    pricePerStock int not null,
    stockRemaining int not null,
    amountDeducted int not null
    );
    insert into @r (lvl, stockId, dealId, stockDate, stockAmount, pricePerStock, stockRemaining, amountDeducted)
    select 0, stockId, dealId, stockDate, stockAmount, pricePerStock, stockAmount as stockRemaining, 0 as amountDeducted
    from @T
    where dealtype = 'B'
    declare @rowCount int =1;
    declare @lvl int = 0;
    while @rowCount > 0
    begin
    set @lvl = @lvl + 1;
    with sells as (
      select stockId, dealId as saleId, row_number() over (order by dealId) as sellNum, stockAmount as sellAmount
      from @T where dealType = 'S'
    )
    update @r
    set lvl = @lvl,
      amountDeducted = (select sellAmount from sells where sellNum = @lvl),
      stockRemaining = (
      select stockRemaining
      from (
      select dealId,
       case
       when r.stockRemaining + s.sellAmount < sum(stockRemaining) over (order by dealId desc) then r.stockRemaining
       when sum(stockRemaining) over (order by dealId desc) < s.sellAmount then 0
       else sum(stockRemaining) over (order by dealId desc) - s.sellAmount
       end as stockremaining
      from sells s inner join @r r on r.stockId = s.stockId and r.dealId < s.saleId
      where s.stockId = @r.stockId and s.sellNum = @lvl
      ) data
      where dealId = @r.dealId
      )
    where dealId < (select saleId from sells where sellNum = @lvl);

    set @rowCount = @@rowCount;
    if @rowCount >0 select * from @r;
    end

    Msg 137, Level 16, State 1, Line 54
    Must declare the scalar variable "@R".
    Msg 137, Level 16, State 1, Line 56
    Must declare the scalar variable "@R".

  • Try:

    declare @T table
    (
      stockId int not null,
      dealId int identity (1,1) not null,
      dealType char(1) not null,
      stockDate datetime not null,
      stockAmount DECIMAL(38,2) not null,
      pricePerStock DECIMAL(38,2) not null
    );
    insert into @T (stockId, dealType, stockDate, stockAmount, pricePerStock) values
      (111, 'B', '01.01.2010', 11, 100),
      (111, 'B', '01.03.2010', 10, 200),
      (111, 'S', '01.04.2010',  2, 200),
      (111, 'S', '01.06.2010', 4, 200),
      (111, 'S', '01.07.2010',  7, 157.44),
      (111, 'B', '01.08.2010',  10, 500);
    DECLARE @r table
    (
      lvl int not null,
      stockId int not null,
      dealId int not null,
      stockDate datetime not null,
      stockAmount int not null,
      pricePerStock int not null,
      stockRemaining int not null,
      amountDeducted int not null
    );
    insert into @r (lvl, stockId, dealId, stockDate, stockAmount, pricePerStock, stockRemaining, amountDeducted)
    select 0, stockId, dealId, stockDate, stockAmount, pricePerStock, stockAmount as stockRemaining, 0 as amountDeducted
    from @T
    where dealtype = 'B'
    declare @rowCount int =1;
    declare @lvl int = 0;
    while @rowCount > 0
    begin
      set @lvl = @lvl + 1;
      with sells as (
      select stockId, dealId as saleId, row_number() over (order by dealId) as sellNum, stockAmount as sellAmount
      from @T where dealType = 'S'
      )
      update @r
      set lvl = @lvl,
      amountDeducted = (select sellAmount from sells where sellNum = @lvl),
      stockRemaining = (
      select stockRemaining
      from (
      select dealId,
        case
        when r.stockRemaining + s.sellAmount < sum(stockRemaining) over (order by dealId desc) then r.stockRemaining
        when sum(stockRemaining) over (order by dealId desc) < s.sellAmount then 0
        else sum(stockRemaining) over (order by dealId desc) - s.sellAmount
        end as stockremaining
      from sells s inner join @r r on r.stockId = s.stockId and r.dealId < s.saleId
      where s.stockId = [@R].stockId and s.sellNum = @lvl -- added square braces around @r
      ) data
      where dealId = [@R].dealId -- added square braces around @r
      )
      where dealId < (select saleId from sells where sellNum = @lvl);
      set @rowCount = @@rowCount;
      if @rowCount >0 select * from @r;
    end
  • Thank You 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply