April 7, 2018 at 11:38 am
Hello
I want to prepare a stock list of last entry , first entry
DECLARE @IN TABLE (STOCK INT,ID INT, DATE_ DATETIME,AMOUNT DECIMAL(10,2),PRICE DECIMAL(38,2))
INSERT INTO @IN (STOCK,ID,DATE_,AMOUNT,PRICE) VALUES
('5','125',CONVERT(DATETIME,'01.01.2018',104),'11','100'),
('5','126',CONVERT(DATETIME,'01.03.2018',104),'10','200'),
('5','127',CONVERT(DATETIME,'01.08.2018',104),'10','500'),
('6','128',CONVERT(DATETIME,'01.08.2018',104),'10','500')
DECLARE @OUTE TABLE (STOCK INT,ID INT, DATE_ DATETIME,AMOUNT DECIMAL(10,2),PRICE DECIMAL(38,2))
INSERT INTO @OUTE (STOCK,ID,DATE_,AMOUNT,PRICE) VALUES
('5','652',CONVERT(DATETIME,'01.04.2018',104),'2','200'),
('5','265',CONVERT(DATETIME,'01.06.2018',104),'4','200'),
('5','548',CONVERT(DATETIME,'01.07.2018',104),'7','157.44')
must be
| STOCK | ID | DATE | AMOUNT | PRICE | OUTE_AMOUNT | OUTE_PRICE | OUTE_ID | OUTE_DATE |
| 5 | 126 | 01.03.2018 | 2 | 200 | 2 | 200 | 652 | 01.04.2018 |
| 5 | 126 | 01.03.2018 | 4 | 200 | 4 | 200 | 265 | 01.06.2018 |
| 5 | 126 | 01.03.2018 | 4 | 200 | 4 | 157,44 | 548 | 01.07.2018 |
| 5 | 125 | 01.01.2018 | 3 | 100 | 3 | 157,44 | 548 | 01.07.2018 |
| 5 | 125 | 01.01.2018 | 8 | 100 | NULL | NULL | NULL | NULL |
| 5 | 127 | 01.08.2018 | 10 | 500 | NULL | NULL | NULL | NULL |
| 6 | 128 | 01.08.2018 | 10 | 500 | NULL | NULL | NULL | NULL |
April 9, 2018 at 1:04 pm
Help please :crying:
What I want is impossible ?
April 9, 2018 at 2:53 pm
I think we need some clarification on what is actually happening here and what you're trying to do. How are you matching IN records with their corresponding OUTE record? I'm not following from the provided example, it could be sequential but I would imagine there are a number of different business cases here. Also do you need to deal with amount mismatches somehow, matching one IN to multiple OUTE or multiple IN to one OUTE records?
April 9, 2018 at 3:11 pm
Thank You 🙂
The common area of ??the table is as follows.
SELECT * FROM @IN F LEFT JOIN @OUTE G ON F.STOCK= G.STOCK
April 10, 2018 at 7:21 am
This can't be the whole criteria for matching records, or else the results would have 9 rows for STOCK = 5, and you say you want the results to have 6 rows? I still think we're missing some of the business logic here that you're trying to work with. Also, could you post the entire query that you have tried?
April 10, 2018 at 7:49 am
hi,
Last In First Out
I want to create a list according to.
April 10, 2018 at 11:01 am
meryemkurs072 - Tuesday, April 10, 2018 7:49 AMhi,Last Ä°n First Out
I want to create a list according to.
While useful to know, we still don't have any indicator as to exactly what each table that contributes to the result actually represents. Most LIFO scenarios involve an inventory table, along with some form of sales transaction table and often, some means of allocating inventory records to specific sales orders. So just showing a query and saying I want LIFO doesn't really say much, as that leaves 95% of the necessary information somewhere in the dark. Please provide more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2018 at 11:42 am
Hello
Thank you very much for your interest.
Because I do not speak English, I use translation.
There is a table where one entry is listed @IN
I have a table listing my exits, ie, sales, whose name is @OUTE
The common area of ??these two tables is 'Stock'
The logic here is to find the most recent entry and exit from it when there
is an exit date, and write the output in the output table against it
Thank you
April 11, 2018 at 12:45 pm
Hi,
The code below generally gives what I want, but I want the details. That is, when the output is the information of the relevant record
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.2018', 11, 100),
(111, 'B', '01.03.2018', 10, 200),
(111, 'S', '01.04.2018', 2, 200),
(111, 'S', '01.06.2018', 4, 200),
(111, 'S', '01.07.2018', 7, 157.44),
(111, 'B', '01.08.2018', 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;
end
select * from @r;
If this is not understood.
Please delete this subject 🙁
April 17, 2018 at 10:26 am
meryemkurs072 - Wednesday, April 11, 2018 12:45 PMHi,The code below generally gives what I want, but I want the details. That is, when the output is the information of the relevant record
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.2018', 11, 100),
(111, 'B', '01.03.2018', 10, 200),
(111, 'S', '01.04.2018', 2, 200),
(111, 'S', '01.06.2018', 4, 200),
(111, 'S', '01.07.2018', 7, 157.44),
(111, 'B', '01.08.2018', 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;end
select * from @r;
If this is not understood.
Please delete this subject 🙁
Unfortunately, the problem here is language. When you say details, that could be almost anything. As you have a working cursor, you likely need to in some way select out the relevant rows as part of your cursor processing, and I suspect that adding an OUTPUT clause to your UPDATE statement might do the trick. You'd have to create a temp table with the necessary columns, and then specify that table name and the columns from the INSERTED or DELETED meta-tables that you want. The INSERTED and DELETED meta tables exist within an UPDATE statement when you use an OUTPUT clause with it, and you can specify them inside the OUTPUT clause as table aliases. Again, not sure if that's what you need or not, but it will at least allow you to see the inner workings of the UPDATE statement.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 18, 2018 at 12:53 pm
Merhaba,
Ingilizce bilmiyorum daha önceden belirttim. Kendi dilimde size yazacagim yardimci olursan memnun kalirim.
Örnek; Asagida stok kartinin girisleri mevcut
DECLARE @IN TABLE (STOCK INT,ID INT, DATE_ DATETIME,AMOUNT DECIMAL(10,2),PRICE DECIMAL(38,2))
INSERT INTO @IN (STOCK,ID,DATE_,AMOUNT,PRICE) VALUES
('5','125',CONVERT(DATETIME,'01.01.2018',104),'11','100'),
('5','126',CONVERT(DATETIME,'01.03.2018',104),'10','200'),
('5','127',CONVERT(DATETIME,'01.08.2018',104),'10','500'),
('6','128',CONVERT(DATETIME,'01.08.2018',104),'10','500')
Stok kartlarin çikislari oldugu tabloyu yaziyorum.
DECLARE @OUTE TABLE (STOCK INT,ID INT, DATE_ DATETIME,AMOUNT DECIMAL(10,2),PRICE DECIMAL(38,2))
INSERT INTO @OUTE (STOCK,ID,DATE_,AMOUNT,PRICE) VALUES
('5','652',CONVERT(DATETIME,'01.04.2018',104),'2','200'),
('5','265',CONVERT(DATETIME,'01.06.2018',104),'4','200'),
('5','548',CONVERT(DATETIME,'01.07.2018',104),'7','157.44')
Burdaki mantik su her çikis oldugunda ilgili son giris tarihindeki miktari bulup çikis adedini çikartip yanina çikis id,date,amount gibi verileri yazacak. 2018-04-01 tarihinde bir çikis var burda 2018-03-01 tarihindeki giris'den çikarma islemini yapacaz sonrasinda yani 10 amount idi 2 amount çikarinca 8 kalir.
| STOCK | ID | DATE | AMOUNT | PRICE | OUTE_AMOUNT | OUTE_PRICE | OUTE_ID | OUTE_DATE |
| 5 | 126 | 01.03.2018 | 2 | 200 | 2 | 200 | 652 | 01.04.2018 |
2018-06-01 tarihinde çikis var 4 amount bunu 01.03.2018 tarihinde kalan 8 amount çikartacaz yani oda 8-4=4 amount kalir
| STOCK | ID | DATE | AMOUNT | PRICE | OUTE_AMOUNT | OUTE_PRICE | OUTE_ID | OUTE_DATE |
| 5 | 126 | 01.03.2018 | 2 | 200 | 2 | 200 | 652 | 01.04.2018 |
| 5 | 126 | 01.03.2018 | 4 | 200 | 4 | 200 | 265 | 01.06.2018 |
2018-07-01 tarihinde 7 amount bunuda 2018-03-01 tarihindeki giristen 4 amount 2018-01-01 tarihindeki giristen 3 amount (3+4)=7 seklinde düsme yapacaz.
| STOCK | ID | DATE | AMOUNT | PRICE | OUTE_AMOUNT | OUTE_PRICE | OUTE_ID | OUTE_DATE |
| 5 | 126 | 01.03.2018 | 2 | 200 | 2 | 200 | 652 | 01.04.2018 |
| 5 | 126 | 01.03.2018 | 4 | 200 | 4 | 200 | 265 | 01.06.2018 |
| 5 | 126 | 01.03.2018 | 4 | 200 | 4 | 157,44 | 548 | 01.07.2018 |
| 5 | 125 | 01.01.2018 | 3 | 100 | 3 | 157,44 | 548 | 01.07.2018 |
Sonrasinda tablo yukardaki sekli almasi gerekiyor. Sonrasinda elimizde kalan stoklari listelememiz lazim.
| STOCK | ID | DATE | AMOUNT | PRICE | OUTE_AMOUNT | OUTE_PRICE | OUTE_ID | OUTE_DATE |
| 5 | 125 | 01.01.2018 | 8 | 100 | NULL | NULL | NULL | NULL |
| 5 | 127 | 01.08.2018 | 10 | 500 | NULL | NULL | NULL | NULL |
| 6 | 128 | 01.08.2018 | 10 | 500 | NULL | NULL | NULL | NULL |
Umarim anlatabilmisimdir.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply