May 18, 2016 at 7:59 am
I want to ignore the "Sale" transactions from FIFO processing where no preceding buy transaction is available or there is no inventory available.
I am doing it this way but not getting correct output.
CREATE TABLE [dbo].[BuyData](
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[TradeDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [bigint] IDENTITY(1,1) NOT NULL
) ON [Primary]
CREATE TABLE [dbo].[SaleData](
[SrNo] [bigint] IDENTITY(1,1) NOT NULL,
[Party_Code] [varchar](10) NULL,
[Item] [varchar](15) NULL,
[TradeDate] [smalldatetime] NULL,
[SellQty] [int] NULL,
[Dummy1] [varchar](1) NULL
) ON [Primary]
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-16 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-20 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-22 00:00:00',5)
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',5,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-18 00:00:00',1,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2013-01-30 00:00:00',5,'Y')
;WITH Buys AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY Party_code, Item ORDER BY TradeDate), s.*
FROM Buydata s
CROSS APPLY (SELECT TOP(s.PurchaseQty) a.name from sys.columns a, sys.columns b) x
WHERE PurchaseQty > 0
And Party_code = 'M33945' and Item = 'A01022'
),
Sells AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY Party_code, Item ORDER BY TradeDate), s.*
FROM SaleData s
CROSS APPLY (SELECT TOP(s.SellQty) a.name from sys.columns a, sys.columns b) x
WHERE SellQty > 0
And Party_code = 'M33945' and Item = 'A01022'
And TradeDate >= (select Top 1 TradeDate from Buys where S.item = buys.item and s.Party_code = Buys.Party_code and S.TradeDate >= Buys.TradeDate)
)
SELECT
Party_code = ISNULL(b.Party_code,s.Party_code),
Item = ISNULL(b.Item,S.Item),
TradeDate = Cast(Isnull(b.TradeDate,'DEC 31 2049')As Datetime),
PurchaseQty = COUNT(b.rn),
SaleQty = COUNT(s.rn),
SaleTradedate = Isnull(s.TradeDate,'DEC 31 2049')
FROM Buys b
Full Outer JOIN
Sells s
ON
s.Party_code = b.Party_code
AND s.Item = b.item
And S.Rn = B.Rn
And S.TradeDate >= (select Top 1 TradeDate from Buys where S.Item = buys.Item and s.Party_code = Buys.Party_code and S.TradeDate >= Buys.TradeDate)
GROUP BY
ISNULL(b.Party_code,s.Party_code),
ISNULL(b.Item,S.Item),
b.TradeDate,
b.TsrNo,
s.TradeDate,
s.SrNo
ORDER BY
ISNULL(b.Party_code,s.Party_code),
ISNULL(b.Item,s.ITem),
B.TradeDate ,
ISNULL(b.TsrNo,s.SrNo),
ISNULL(s.SrNo,2147483647)
Gives Me below output
Party_code Item TradeDate PurchaseQty SaleQty SaleTradedate
---------- -------------------- ----------------------- ----------- ----------- -----------------------
M33945 A01022 2011-06-16 00:00:00.000 5 5 2011-06-17 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 1 1 2011-06-18 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 4 4 2013-01-30 00:00:00
M33945 A01022 2011-06-22 00:00:00.000 1 1 2013-01-30 00:00:00
M33945 A01022 2011-06-22 00:00:00.000 4 0 2049-12-31 00:00:00
While I want output as
Party_code Item TradeDate PurchaseQty SaleQty SaleTradedate
---------- -------------------- ----------------------- ----------- ----------- -----------------------
M33945 A01022 2011-06-16 00:00:00.000 5 5 2011-06-17 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 5 5 2013-01-30 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 5 0 2049-12-31 00:00:00
The programme should ignore Sale transaction of 2011-06-18 because on this date because there is no
previous purchased stock available for sale.
I want to ignore all sale transaction where there is no purchase quantity left.
Please help
May 18, 2016 at 8:05 am
b ghanekar (5/18/2016)
I want to ignore the "Sale" transactions from FIFO processing where no preceding buy transaction is available or there is no inventory available.I am doing it this way but not getting correct output.
CREATE TABLE [dbo].[BuyData](
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[TradeDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [bigint] IDENTITY(1,1) NOT NULL
) ON [Primary]
CREATE TABLE [dbo].[SaleData](
[SrNo] [bigint] IDENTITY(1,1) NOT NULL,
[Party_Code] [varchar](10) NULL,
[Item] [varchar](15) NULL,
[TradeDate] [smalldatetime] NULL,
[SellQty] [int] NULL,
[Dummy1] [varchar](1) NULL
) ON [Primary]
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-16 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-20 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-22 00:00:00',5)
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',5,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-18 00:00:00',1,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2013-01-30 00:00:00',5,'Y')
;WITH Buys AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY Party_code, Item ORDER BY TradeDate), s.*
FROM Buydata s
CROSS APPLY (SELECT TOP(s.PurchaseQty) a.name from sys.columns a, sys.columns b) x
WHERE PurchaseQty > 0
And Party_code = 'M33945' and Item = 'A01022'
),
Sells AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY Party_code, Item ORDER BY TradeDate), s.*
FROM SaleData s
CROSS APPLY (SELECT TOP(s.SellQty) a.name from sys.columns a, sys.columns b) x
WHERE SellQty > 0
And Party_code = 'M33945' and Item = 'A01022'
And TradeDate >= (select Top 1 TradeDate from Buys where S.item = buys.item and s.Party_code = Buys.Party_code and S.TradeDate >= Buys.TradeDate)
)
SELECT
Party_code = ISNULL(b.Party_code,s.Party_code),
Item = ISNULL(b.Item,S.Item),
TradeDate = Cast(Isnull(b.TradeDate,'DEC 31 2049')As Datetime),
PurchaseQty = COUNT(b.rn),
SaleQty = COUNT(s.rn),
SaleTradedate = Isnull(s.TradeDate,'DEC 31 2049')
FROM Buys b
Full Outer JOIN
Sells s
ON
s.Party_code = b.Party_code
AND s.Item = b.item
And S.Rn = B.Rn
And S.TradeDate >= (select Top 1 TradeDate from Buys where S.Item = buys.Item and s.Party_code = Buys.Party_code and S.TradeDate >= Buys.TradeDate)
GROUP BY
ISNULL(b.Party_code,s.Party_code),
ISNULL(b.Item,S.Item),
b.TradeDate,
b.TsrNo,
s.TradeDate,
s.SrNo
ORDER BY
ISNULL(b.Party_code,s.Party_code),
ISNULL(b.Item,s.ITem),
B.TradeDate ,
ISNULL(b.TsrNo,s.SrNo),
ISNULL(s.SrNo,2147483647)
Gives Me below output
Party_code Item TradeDate PurchaseQty SaleQty SaleTradedate
---------- -------------------- ----------------------- ----------- ----------- -----------------------
M33945 A01022 2011-06-16 00:00:00.000 5 5 2011-06-17 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 1 1 2011-06-18 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 4 4 2013-01-30 00:00:00
M33945 A01022 2011-06-22 00:00:00.000 1 1 2013-01-30 00:00:00
M33945 A01022 2011-06-22 00:00:00.000 4 0 2049-12-31 00:00:00
While I want output as
Party_code Item TradeDate PurchaseQty SaleQty SaleTradedate
---------- -------------------- ----------------------- ----------- ----------- -----------------------
M33945 A01022 2011-06-16 00:00:00.000 5 5 2011-06-17 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 5 5 2013-01-30 00:00:00
M33945 A01022 2011-06-20 00:00:00.000 5 0 2049-12-31 00:00:00
The programme should ignore Sale transaction of 2011-06-18 because on this date because there is no
previous purchased stock available for sale.
I want to ignore all sale transaction where there is no purchase quantity left.
Please help
May 18, 2016 at 8:48 am
I am confused and I think your sample data may be over simplified.
kudos for the set up data though...it helps a lot.
what would you expect in this sample data?
CREATE TABLE [dbo].[BuyData](
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[TradeDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [bigint] IDENTITY(1,1) NOT NULL
) ON [Primary]
CREATE TABLE [dbo].[SaleData](
[SrNo] [bigint] IDENTITY(1,1) NOT NULL,
[Party_Code] [varchar](10) NULL,
[Item] [varchar](15) NULL,
[TradeDate] [smalldatetime] NULL,
[SellQty] [int] NULL,
[Dummy1] [varchar](1) NULL
) ON [Primary]
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-16 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-20 00:00:00',5)
Insert into Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-22 00:00:00',5)
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',2,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',1,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',1,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-18 00:00:00',1,'Y')
Insert into saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2013-01-30 00:00:00',5,'Y')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 18, 2016 at 11:19 am
so what results would you expect from here.....(earlier post was not the sample data I intended posting...sorry)
CREATE TABLE #BuyData(
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[TradeDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [bigint] IDENTITY(1,1) NOT NULL
) ON [Primary]
CREATE TABLE #SaleData(
[SrNo] [bigint] IDENTITY(1,1) NOT NULL,
[Party_Code] [varchar](10) NULL,
[Item] [varchar](15) NULL,
[TradeDate] [smalldatetime] NULL,
[SellQty] [int] NULL,
[Dummy1] [varchar](1) NULL
) ON [Primary]
Insert into #Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-15 00:00:00',3)
Insert into #Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-16 00:00:00',5)
Insert into #Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-20 00:00:00',5)
Insert into #Buydata (Party_Code,Item,TradeDate,PurchaseQty) Values('M33945','A01022','2011-06-22 00:00:00',5)
Insert into #saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',2,'Y')
Insert into #saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',4,'Y')
Insert into #saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-17 00:00:00',1,'Y')
Insert into #saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2011-06-18 00:00:00',3,'Y')
Insert into #saledata (Party_Code,Item,TradeDate,SellQty,Dummy1) Values('M33945','A01022','2013-01-30 00:00:00',5,'Y')
SELECT Party_Code,
Item,
TradeDate,
Qty,
stocktype,
SUM(Qty) OVER(PARTITION BY item ORDER BY TradeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_tot
FROM
(
SELECT Party_Code,
Item,
TradeDate,
PurchaseQty AS Qty,
'P' as stocktype
FROM #BuyData
UNION ALL
SELECT Party_Code,
Item,
TradeDate,
SellQty * -1 AS Qty,
'S' as stocktype
FROM #SaleData
) x
DROP TABLE #BuyData
DROP TABLE #SaleData
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 18, 2016 at 3:53 pm
Thanks for your reply.
Buy Transactions
'M33945','A01022','2011-06-15',3
'M33945','A01022','2011-06-16',5
'M33945','A01022','2011-06-20 ,5
'M33945','A01022','2011-06-22 ,5
Sale Transactions
'M33945','A01022','2011-06-17’,2,'Y'
'M33945','A01022','2011-06-17',4,'Y'
'M33945','A01022','2011-06-17',1,'Y'
'M33945','A01022','2011-06-18',3,'Y'
'M33945','A01022','2013-01-30',5,'Y'
Output desired
M33945, A01022, 2011-06-15, 3, 2011-06-17, 3 N
M33945, A01022, 2011-06-16, 4, 2011-06-17, 4 N
M33945, A01022, 2011-06-16, 1, 2011-06-18, 1 N
- , - , - , -, 2011-06-18, 2 Y
M33945, A01022, 2011-06-20, 5, 2013-01-30, 5 N
M33945, A01022, 2011-06-22 ,5, - , - -
May 18, 2016 at 3:58 pm
b ghanekar (5/18/2016)
Thanks for your reply.Buy Transactions
'M33945','A01022','2011-06-15',3
'M33945','A01022','2011-06-16',5
'M33945','A01022','2011-06-20 ,5
'M33945','A01022','2011-06-22 ,5
Sale Transactions
'M33945','A01022','2011-06-17’,2,'Y'
'M33945','A01022','2011-06-17',4,'Y'
'M33945','A01022','2011-06-17',1,'Y'
'M33945','A01022','2011-06-18',3,'Y'
'M33945','A01022','2013-01-30',5,'Y'
Output desired
M33945, A01022, 2011-06-15, 3, 2011-06-17, 3 N
M33945, A01022, 2011-06-16, 4, 2011-06-17, 4 N
M33945, A01022, 2011-06-16, 1, 2011-06-18, 1 N
- , - , - , -, 2011-06-18, 2 Y
M33945, A01022, 2011-06-20, 5, 2013-01-30, 5 N
M33945, A01022, 2011-06-22 ,5, - , - -
perhaps you will be so kind to put your desired output into a table script that we can all see........thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 18, 2016 at 10:16 pm
Sorry sir I am not clear about table script? Can you tell me further please. Thanks for bearing me so long.
The output from your data should be as below
CREATE TABLE #Temp(
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[PurchaseDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [BigInt],
[SaleDate][Smalldatetime],
[SaleQty] [Int],
[Dummy1] [Char](1)
) ON [Primary]
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
( 'M33945', 'A01022', '2011-06-15', '3',1, '2011-06-17', '3', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-16', '4',2, '2011-06-17', '4', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-16', '1',2, '2011-06-18', '1', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
( '-' , '-' , 'JAN 1 2079' , '0',0, '2011-06-18', '2', 'Y')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-20', '5',3, '2013-01-30', '5', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-22' ,'5',4, 'JAN 1 2079' , '0', 'Y')
Select * from #Temp
May 20, 2016 at 5:44 pm
b ghanekar (5/18/2016)
Sorry sir I am not clear about table script? Can you tell me further please. Thanks for bearing me so long.The output from your data should be as below
CREATE TABLE #Temp(
[Party_Code] [varchar](10) NULL,
[Item] [varchar](20) NULL,
[PurchaseDate] [smalldatetime] NULL,
[PurchaseQty] [int] NULL,
[Tsrno] [BigInt],
[SaleDate][Smalldatetime],
[SaleQty] [Int],
[Dummy1] [Char](1)
) ON [Primary]
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
( 'M33945', 'A01022', '2011-06-15', '3',1, '2011-06-17', '3', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-16', '4',2, '2011-06-17', '4', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-16', '1',2, '2011-06-18', '1', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
( '-' , '-' , 'JAN 1 2079' , '0',0, '2011-06-18', '2', 'Y')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-20', '5',3, '2013-01-30', '5', 'N')
Insert Into #temp (Party_code,Item,PurchaseDate,PurchaseQty,TsrNo,SaleDate,SaleQty,Dummy1)
Values
('M33945', 'A01022', '2011-06-22' ,'5',4, 'JAN 1 2079' , '0', 'Y')
Select * from #Temp
thanks for this.....it was just that my browser was making it difficult to see the columns alignment.
moving on....I "think" I can sort of undertstand what you are trying to acheive, but I have to ask.....why?
is this a specific request from the business and if so, what have they asked you to report on?
I dont understand the "dummy" dates that you are inserting ...can you explain why these are required?
in my experience "FIFO" is used to calculate financial margins...but you have no values of purchases/sales in your data...?
maybe you have tried to over simplify what you are really want to achieve?
would really like to know more, when you have time to reply
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 20, 2016 at 6:52 pm
The dates are not dummy dates. Dummy column indicates that the purchase and sales for a particular purchase has happened.
The particular format is for calculating taxes. Based on the difference between purchase and sale date the slab of taxation changes. So to submit the transactions to the tax authorities this particular format is essential
1] Client 2] PurchaseItem 3] Purchase Qty 4] Purchase Amt. 5] Purchase Date 5] Sale Qty 6] Sale Amt 7] Sale Date.
Where the sale transaction before a purchase date is to be ignored. That is if I have a Sale transaction happening before any actual purchase then that Sale transaction should be ignored from this report.
If after square off there is no purchase quantity remaining in that case also the Sale transaction should be ignored.
Thanks for patient hearing
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply