June 17, 2012 at 12:38 am
Table Structure
CREATE TABLE [dbo].[Stock](
[clientcode] [varchar](10) NULL,
[productid] [varchar](10) NULL,
[pdate] [date] NULL,
[ptime] [varchar](20) NULL,
[pno] [numeric](18, 0) NULL,
[ptype] [varchar](1) NULL,
[qty] [int] NULL,
[rate] [numeric](18, 2) NULL,
[uniqno] [int] NULL
) ON [PRIMARY]
GO
Sample Data
-------------------------------------------------------------------------------
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:25:19', CAST(1231 AS Numeric(18, 0)), N'B', 10, CAST(115.20 AS Numeric(18, 2)), 1)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:30:01', CAST(1232 AS Numeric(18, 0)), N'B', 20, CAST(118.30 AS Numeric(18, 2)), 2)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x76350B00 AS Date), N'10:10:10', CAST(1231 AS Numeric(18, 0)), N'B', 30, CAST(200.30 AS Numeric(18, 2)), 3)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:27:19', CAST(1233 AS Numeric(18, 0)), N'S', 5, CAST(114.35 AS Numeric(18, 2)), 4)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'10:10:12', CAST(1234 AS Numeric(18, 0)), N'S', 15, CAST(116.25 AS Numeric(18, 2)), 5)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x76350B00 AS Date), N'11:10:20', CAST(1232 AS Numeric(18, 0)), N'S', 25, CAST(119.35 AS Numeric(18, 2)), 6)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P002', CAST(0x75350B00 AS Date), N'09:26:20', CAST(1235 AS Numeric(18, 0)), N'S', 50, CAST(200.25 AS Numeric(18, 2)), 7)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P001', CAST(0x75350B00 AS Date), N'15:10:25', CAST(1236 AS Numeric(18, 0)), N'S', 30, CAST(120.25 AS Numeric(18, 2)), 8)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P002', CAST(0x75350B00 AS Date), N'16:10:25', CAST(1237 AS Numeric(18, 0)), N'S', 100, CAST(130.25 AS Numeric(18, 2)), 9)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P002', CAST(0x76350B00 AS Date), N'10:12:52', CAST(1233 AS Numeric(18, 0)), N'B', 100, CAST(190.25 AS Numeric(18, 2)), 10)
GO
Sample Record
-----------------------
select * from stock ;
-----------------------------------------------------------------------------------------
clientcodeproductidpdateptimepnoptypeqtyrateuniqno
--------------------------------------------------------------------------------------------
A001P0012012-03-2009:25:191231B10115.201
A001P0012012-03-2009:30:011232B20118.302
A001P0012012-03-2110:10:101231B30200.303
A001P0012012-03-2009:27:191233S5114.354
A001P0012012-03-2010:10:121234S15116.255
A001P0012012-03-2111:10:201232S25119.356
A002P0022012-03-2009:26:201235S50200.257
A002P0012012-03-2015:10:251236S30120.258
A001P0022012-03-2016:10:251237S100130.259
A002P0022012-03-2110:12:521233B100190.2510
-------------------------------------------------------------------------------------------
B -- BUY , S-- SELL
Required Output:
-----------------
Clientcode ProductId buydate butqty buyrate sellqty selldate sellrate buyuniqno selluniqno
A001 P001 2012-03-20 5 115.20 5 2012-03-20 114.35 1 4
A001 P001 2012-03-20 5 115.20 5 2012-03-20 116.25 1 5
A001 P001 2012-03-20 10 118.30 10 2012-03-20 116.25 2 5
A001 P001 2012-03-20 10 118.30 10 2012-03-21 119.35 2 6
A001 P001 2012-03-21 15 200.30 15 2012-03-21 119.35 3 6
A001 P001 2012-03-21 15 200.30 3
A001 P002 100 2012-03-20 130.25 9
A002 P001 30 2012-03-20 120.25 8
A002 P002 2012-03-21 50 190.25 50 2012-03-20 200.25 10 7
A002 P002 2012-03-21 50 190.25 50 10
validation :
1. Match the buy/sell record in FIFO basis(The allocation order should be in Client wise/Product wise/Date wise/time/Pno wise)
Its possible using CTS/Set based query?
Please help
June 18, 2012 at 12:32 am
How is SQL Server supposed to know that you have a FIFO business logic in mind.
Do you have a transactions table where you record the Transactions, quantity sold/bought, Date of Sales/Purchases, Amounts etc.???
If such a table does exist and is maintained according to the FIFO logic then you can match the records in both tables to get a Result Set which shows the FIFO approach you want.
If the table does not exist then please do explain what is the logic behind the change that you want to see in the Result Set. The original table has only one field for quantity....How do you know when you sell it or buy it??
Please elaborate on the Logic.
June 18, 2012 at 8:41 am
This generates the correct results from the given sample data, however I'd be more comfortable with a larger sample:
;WITH Buys AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY clientcode, productid ORDER BY pdate, ptime, pno), s.*
FROM stock s
CROSS APPLY (SELECT TOP(s.qty) a.name from sys.columns a, sys.columns b) x
WHERE ptype = 'B'
),
Sells AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY clientcode, productid ORDER BY pdate, ptime, pno), s.*
FROM stock s
CROSS APPLY (SELECT TOP(s.qty) a.name from sys.columns a, sys.columns b) x
WHERE ptype = 'S'
)
SELECT *
FROM (
SELECT
b.clientcode,
b.productid,
buydate = b.pdate,
buyqty = COUNT(b.rn),
buyrate = b.rate,
sellqty = COUNT(s.rn),
selldate = s.pdate,
sellrate = s.rate,
buyuniqno = b.uniqno,
selluniqno = s.uniqno
FROM Buys b
LEFT JOIN Sells s
ON s.clientcode = b.clientcode AND s.productid = b.productid AND s.rn = b.rn
GROUP BY
b.clientcode,
b.productid,
b.pdate,
b.ptime,
b.rate,
b.uniqno,
s.pdate,
s.rate,
s.uniqno
UNION ALL
SELECT
s.clientcode,
s.productid,
buydate = NULL,
buyqty = NULL,
buyrate = NULL,
sellqty = s.qty,
selldate = s.pdate,
sellrate = s.rate,
buyuniqno = NULL,
selluniqno = s.uniqno
FROM stock s
WHERE s.ptype = 'S'
AND NOT EXISTS (
SELECT 1
FROM stockb
WHERE b.ptype = 'B'
AND s.clientcode = b.clientcode
AND s.productid = b.productid)
) d
ORDER BY clientcode, productid, buyuniqno, ISNULL(selluniqno,9999)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2012 at 8:46 pm
Nice one Chris!
I've been tearing out what little hair I have left over this one. I knew a recursive CTE was the way to go but both of my attempts got tied up in knots.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 8:56 pm
You most likely want to work off the lot inventory instead off the transactions list because you have to resum all sells to determine what tax lot you're involved with for FIFO financial transactions from the beginning of time. This is primarily because of capital gains taxation.
What you've described here is merely the trade log, and I'm not sure how you're tracking which lot is sold when, considering you can sell portions of lots and that's not indicated in the SELL component unless I'm misreading it.
Is this a custom system or are you working off a vendor build? Can you drop a data dictionary on what those column names mean (the pseries and rate) and if you have any subsidiary tables? This is a very painful way to approach the issue unless you allow for historical edits for late trade data, as you'll have to recalculate tax statements constantly without lot associations in the trade log or elsewhere.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 18, 2012 at 9:39 pm
Thanks a lot Chris...:-):-) Superb...:-) i got what i want...:-D
Can i use this concept for more than 10,00,000 records, any performance issue?
My actual requirement is allocate the qty in FIFO basis and then store this result in 5 different tables..
From ur solution i can store the result.
Surly i will follow on (http://www.sqlservercentral.com/articles/Best+Practices/61537/) from my next post
June 18, 2012 at 10:12 pm
Very nice solution Chris.
Sorry for my last post. I had missed(not understood, actually) the "B--Buy, S--Sell" part. That is why I was asking for a Transactions Table. My bad.
June 19, 2012 at 3:26 am
Gosh, thanks for the generous comments guys - however, the method fails if the sample data set is expanded to have more sales than buys within a partition. Here's code for extending the data set:
SELECT *
FROM (
SELECT 'A001', 'P001', '2012-03-20', '09:25:19', 1231, 'B', 10, 115.20, 1 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '09:30:01', 1232, 'B', 20, 118.30, 2 UNION ALL
SELECT 'A001', 'P001', '2012-03-21', '10:10:10', 1231, 'B', 30, 200.30, 3 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '09:27:19', 1233, 'S', 5, 114.35, 4 UNION ALL
SELECT 'A001', 'P001', '2012-03-20', '10:10:12', 1234, 'S', 10, 116.25, 5 UNION ALL -- changed row
SELECT 'A001', 'P001', '2012-03-21', '11:10:20', 1232, 'S', 25, 119.35, 6 UNION ALL
SELECT 'A001', 'P001', '2012-03-22', '10:10:12', 1234, 'S', 35, 116.25, 11 UNION ALL -- new row
SELECT 'A001', 'P002', '2012-03-20', '16:10:25', 1237, 'S', 100, 130.25, 9 UNION ALL
SELECT 'A002', 'P001', '2012-03-20', '15:10:25', 1236, 'S', 30, 120.25, 8 UNION ALL
SELECT 'A002', 'P002', '2012-03-20', '09:26:20', 1235, 'S', 50, 200.25, 7 UNION ALL
SELECT 'A002', 'P002', '2012-03-21', '10:12:52', 1233, 'B', 100, 190.25, 10
) d (clientcode, productid, pdate, ptime, pno, ptype, qty, rate, uniqno)
Here's a tweaked query to fix:
;WITH Buys AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY clientcode, productid ORDER BY pdate, ptime, pno), s.*
FROM stock s
CROSS APPLY (SELECT TOP(s.qty) a.name from sys.columns a, sys.columns b) x
WHERE ptype = 'B'
),
Sells AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY clientcode, productid ORDER BY pdate, ptime, pno), s.*
FROM stock s
CROSS APPLY (SELECT TOP(s.qty) a.name from sys.columns a, sys.columns b) x
WHERE ptype = 'S'
)
SELECT
clientcode= ISNULL(b.clientcode,s.clientcode),
productid= ISNULL(b.productid,S.productid),
buydate= b.pdate,
buyqty= COUNT(b.rn),
buyrate= b.rate,
sellqty= COUNT(s.rn),
selldate= s.pdate,
sellrate= s.rate,
buyuniqno= b.uniqno,
selluniqno= s.uniqno
FROM Buys b
FULL OUTER JOIN Sells s
ON s.clientcode = b.clientcode
AND s.productid = b.productid
AND s.rn = b.rn
GROUP BY
ISNULL(b.clientcode,s.clientcode),
ISNULL(b.productid,S.productid),
b.pdate,
b.ptime,
b.rate,
b.uniqno,
s.pdate,
s.rate,
s.uniqno
ORDER BY
ISNULL(b.clientcode,s.clientcode),
ISNULL(b.productid,s.productid),
ISNULL(b.uniqno,s.uniqno),
ISNULL(s.uniqno,2147483647)
Nageswari.J (6/18/2012)
...Can i use this concept for more than 10,00,000 records, any performance issue?...
The performance of this query is likely to be poor, I wouldn't like to guess how long it would take
to process 5 million rows.
I'd suggest that instead of using CTE's as in the code, create #temp tables using the same logic,
and create a unique clustered index on clientcode, productid and rn.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2012 at 1:21 am
Hi Chris
Yeah u r correct, I also face the same problem when i checked.. I thought to ask to u. before itself u answered... Thanks once again...
my tesing process is going on... now i tested in 50000 records only... Its ok.
Thanks once again for ur reply..
June 20, 2012 at 1:52 am
Nageswari.J (6/20/2012)
Hi ChrisYeah u r correct, I also face the same problem when i checked.. I thought to ask to u. before itself u answered... Thanks once again...
my tesing process is going on... now i tested in 50000 records only... Its ok.
Thanks once again for ur reply..
Anytime. Let us know how you get on.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2016 at 10:32 pm
This is a great solution but my problem is something different.
I want to skip records where the date of sale is lesser than the date of purchase.
That is any short sell ( selling without the stock in hand ( needs to be ignored.)
Please help.
April 13, 2016 at 2:03 am
b ghanekar (4/12/2016)
This is a great solution but my problem is something different.I want to skip records where the date of sale is lesser than the date of purchase.
That is any short sell ( selling without the stock in hand ( needs to be ignored.)
Please help.
Sure. Run through the posts on this thread and you will find that the original poster provided an easily-consumable set of data for folks to code against, and a table of expected results from that data. Can you do the same please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2016 at 10:38 pm
Yes sir I created a subset of the data as follows.
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', 'JAN 1 2010', N'09:25:19', CAST(1231 AS Numeric(18, 0)), N'S', 10, CAST(120.20 AS Numeric(18, 2)), 1)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:25:19', CAST(1231 AS Numeric(18, 0)), N'B', 10, CAST(115.20 AS Numeric(18, 2)), 1)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:30:01', CAST(1232 AS Numeric(18, 0)), N'B', 20, CAST(118.30 AS Numeric(18, 2)), 2)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x76350B00 AS Date), N'10:10:10', CAST(1231 AS Numeric(18, 0)), N'B', 30, CAST(200.30 AS Numeric(18, 2)), 3)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'09:27:19', CAST(1233 AS Numeric(18, 0)), N'S', 5, CAST(114.35 AS Numeric(18, 2)), 4)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x75350B00 AS Date), N'10:10:12', CAST(1234 AS Numeric(18, 0)), N'S', 15, CAST(116.25 AS Numeric(18, 2)), 5)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', CAST(0x76350B00 AS Date), N'11:10:20', CAST(1232 AS Numeric(18, 0)), N'S', 25, CAST(119.35 AS Numeric(18, 2)), 6)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P002', CAST(0x75350B00 AS Date), N'09:26:20', CAST(1235 AS Numeric(18, 0)), N'S', 50, CAST(200.25 AS Numeric(18, 2)), 7)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P001', CAST(0x75350B00 AS Date), N'15:10:25', CAST(1236 AS Numeric(18, 0)), N'S', 30, CAST(120.25 AS Numeric(18, 2)), 8)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P002', CAST(0x75350B00 AS Date), N'16:10:25', CAST(1237 AS Numeric(18, 0)), N'S', 100, CAST(130.25 AS Numeric(18, 2)), 9)
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A002', N'P002', CAST(0x76350B00 AS Date), N'10:12:52', CAST(1233 AS Numeric(18, 0)), N'B', 100, CAST(190.25 AS Numeric(18, 2)), 10)
Please note the first record
INSERT [dbo].[Stock] ([clientcode], [productid], [pdate], [ptime], [pno], [ptype], [qty], [rate], [uniqno]) VALUES (N'A001', N'P001', 'JAN 1 2010', N'09:25:19', CAST(1231 AS Numeric(18, 0)), N'S', 10, CAST(120.20 AS Numeric(18, 2)), 1)
This is a sale record for which there is no preceding "Buy". In such a case I want to ignore the "Sale" record from Fifo calculation.
The solution provided gives output like below.
clientcodeproductidbuydatebuyqtybuyratesellqtyselldatesellratebuyuniqnoselluniqno
A001 P001 2012-03-20 10 115.20 10 2010-01-01120.20 1 1
A001 P001 2012-03-20 5 118.30 5 2012-03-20114.35 2 4
A001 P001 2012-03-20 15 118.30 15 2012-03-20116.25 2 5
A001 P001 2012-03-21 25 200.30 25 2012-03-21119.35 3 6
A001 P001 2012-03-21 5 200.30 0 NULL NULL 3 NULL
A001 P002 NULL 0 NULL 100 2012-03-20130.25 NULL 9
A002 P001 NULL 0 NULL 30 2012-03-20120.25 NULL 8
A002 P002 2012-03-21 50 190.25 50 2012-03-20200.25 10 7
A002 P002 2012-03-21 50 190.25 0 NULLNULL 10NULL
I do not want the system to consider the sale record for "JAN 1 2010" since there is no Buy before that day. I want to ignore all "Short Sale" transactions from the calculation
Thanks in advance.
April 16, 2016 at 7:59 pm
FYI... The FIFO problem was the subject of an SSC competition some years ago. Many interesting techniques to take into consideration: The ‘FIFO Stock Inventory’ SQL Problem
-- Itzik Ben-Gan 2001
May 13, 2016 at 5:04 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply