March 19, 2018 at 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,
| ID | DATE_ | AMOUNT | PRICE |
| 1 | 2018-01-01 00:00:00.000 | 8 | 100 |
| 1 | 2018-08-01 00:00:00.000 | 10 | 500 |
March 20, 2018 at 7:38 am
meryemkurs072 - Monday, March 19, 2018 2:21 PMHi,
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,
ID DATE_ AMOUNT PRICE 1 2018-01-01 00:00:00.000 8 100 1 2018-08-01 00:00:00.000 10 500
March 20, 2018 at 12:43 pm
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)
March 20, 2018 at 2:34 pm
I do not know english.
Google translates only so much 🙂
The first-in, last-out logic is this
March 20, 2018 at 2:42 pm
ORDER BY LIFO_COLUMN DESC
March 21, 2018 at 9:57 am
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
April 4, 2018 at 11:28 am
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".
April 4, 2018 at 12:42 pm
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
April 4, 2018 at 12:58 pm
Thank You 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply