December 14, 2019 at 5:51 am
Hi
I have below Data . Using Fifo i want to display output
CodeDateDebit AmountCredit Amount
102-10-201920000
104-10-201910000
110-11-201915000
112-11-201940000
114-11-201930000
304-10-201940000
310-10-201920000
310-11-201945000
OUTPUT
110-11-20195000
114-11-201930000
310-10-201915000
Thanks
December 14, 2019 at 6:40 am
Allah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
December 15, 2019 at 12:34 am
Hi
I have below Data . Using Fifo i want to display output
CodeDateDebit AmountCredit Amount
102-10-201920000
104-10-201910000
110-11-201915000
112-11-201940000
114-11-201930000
304-10-201940000
310-10-201920000
310-11-201945000
OUTPUT
110-11-20195000
114-11-201930000
310-10-201915000
Thanks
For future posts, please help us help you. Read the article at the first link in my signature line below for both how to ask a question and how to provide readily consumable data for us to test/demonstrate with.
With that in mind, here's one way to create the test data for the question being posed...
/********************************************************************************
Create a test table and populate it with test data.
This is NOT a part of the solution. We're just building something to test with.
REF: https://www.sqlservercentral.com/forums/topic/query-using-fifo
********************************************************************************/
--===== Set the default date format according to the data provided by the OP.
SET DATEFORMAT DMY
;
--===== If the test table already exists, drop it to make testing in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create the test table.
-- It would take all of the columns to make a PK, so I''m not making a PK.
CREATE TABLE #TestTable
(
Code TINYINT
,Date DATE
,[Debit Amount] INT
,[Credit Amount] INT
)
;
--===== Populate the test table with data provided by the OP.
INSERT INTO #TestTable
(Code,Date,[Debit Amount],[Credit Amount])
SELECT Code,Date,[Debit Amount],[Credit Amount]
FROM (VALUES
(1,'02-10-2019',20000,NULL)
,(1,'04-10-2019',10000,NULL)
,(1,'10-11-2019',15000,NULL)
,(1,'12-11-2019',NULL,40000)
,(1,'14-11-2019',30000,NULL)
,(3,'04-10-2019',40000,NULL)
,(3,'10-10-2019',20000,NULL)
,(3,'10-11-2019',NULL,45000)
)v(Code,Date,[Debit Amount],[Credit Amount])
;
--===== Let''s see what we''ve built.
SELECT *
FROM #TestTable
ORDER BY Code,Date
;
Shift gears to the question that was asked, I believe that the output isn't what you really want because it's NOT true FIFO. If you have larger Credit Amounts than Debit Amounts, then there will be Credit Amounts that are NEVER consumed. I strongly recommend that you read the rules that were used for true FIFO in the article at the link that Vedat OZER posted above.
That, not withstanding, the following code will produce the output you indicated. If you want the zeros to go away, you'll need to convert the amounts to a string and convert zero-values to strings. I can't have all the fun on this one so I'll leave that up to you. I did convert the date to your desired date format of dd/mm/yyyy. There will be a whole lot of people that will admonish us both for doing ANY formatting in T-SQL and they'll be mostly correct. Formatting should normally be done in the GUI or in the reporting program you're using. If the output of a script or stored procedure IS the reporting program, then formatting in T-SQL may be justified. "It Depends".
WITH
cteCreateGrouping AS
(--==== Create groupings to control the aggregation and bring the rest of the columns forward.
SELECT *
,Grp = COUNT([Credit Amount]) OVER (PARTITION BY Code ORDER BY Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM #TestTable
)
,
cteAggregate AS
(--==== These are simple aggregates based on the groupings we made above.
SELECT Code
,Date = MAX(DATE)
,[Debit Amount] = ISNULL(SUM([Debit Amount]),0)
,[Credit Amount] = ISNULL(SUM([Credit Amount]),0)
FROM cteCreateGrouping
GROUP BY Code,Grp
)--==== This figures out whether we have a an overall debit or a credit and creates the output requested except
-- that zeros will show up in the "other" column.
SELECT Code
,Date = CONVERT(CHAR(10),Date,103) --Ensure dd/mm/yyyy format
,[Debit Amount] = IIF([Debit Amount] >= [Credit Amount], [Debit Amount]-[Credit Amount], 0)
,[Credit Amount] = IIF([Credit Amount] >= [Debit Amount], [Credit Amount]-[Debit Amount], 0)
FROM cteAggregate
ORDER BY Code,Date
;
Be advised that just like the winning FIFO code in the link, you're going to need some indexes on the source table because there's a substantial sort at the beginning of this code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply