Hi Folks I did try the routines but am stuck here
I have split data is 2 tables for ease one for Buy and another for Sale
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