January 6, 2020 at 1:39 pm
Hi
I have below Data and i want Balance Invoices on accoun of Fifo .
In case of C00220 it should return 54575 entry
In case of C00575 it should return 1593 & 401 entry
In case of C00576 it should return 180304 entry
CodedebitCreditRefDate
C0022041477006-05-2019
C0022004147716-05-2019
C0022057525001-04-2019
C0022005752608-07-2019
C0022045843015-10-2019
C002201008-07-2019
C0022054575006-12-2019
C0022004584321-12-2019
C005751593016-12-2019
C00575401016-12-2019
C00576180304019-12-2019
Thanks
January 6, 2020 at 2:13 pm
What have you already tried? The FIRST_VALUE function may help you here.
John
January 6, 2020 at 2:35 pm
If you want a working solution, please provide your sample data in the form of CREATE TABLE/INSERT statements.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 6, 2020 at 2:44 pm
Hi
I have below Data and i want Balance Invoices on accoun of Fifo .
In case of C00220 it should return 54575 entry
In case of C00575 it should return 1593 & 401 entry
In case of C00576 it should return 180304 entry
CodedebitCreditRefDate
C0022041477006-05-2019
C0022004147716-05-2019
C0022057525001-04-2019
C0022005752608-07-2019
C0022045843015-10-2019
C002201008-07-2019
C0022054575006-12-2019
C0022004584321-12-2019
C005751593016-12-2019
C00575401016-12-2019
C00576180304019-12-2019Thanks
You did a nice job with the verbal part of the problem but not so well with the data part of the problem. It's always better if you make the data "readily consumable" because people want to try their coded solutions before posting them. Please Read'n'Heed the first article in my signature line below for more information on that nuance.
In the meantime, here's one possible solution to your problem...
--===== Setup for DD/MM/YYYY dates.
-- YOU should always use ISO YYYYMMDD dates in the future.
SET DATEFORMAT dmy
;
--===== Create a test table and populate it.
-- This is NOT a part of the solution.
-- We''re just building test data here.
-- YOU should do such a thing on all your posts
-- to help you help others give you a better answer
-- more quickly.
CREATE TABLE #TestData
(
Code CHAR(6)
,Debit INT
,Credit INT
,RefDate DATE
)
;
INSERT INTO #TestData
(Code,Debit,Credit,RefDate)
SELECT *
FROM (VALUES
('C00220',41477 ,0 ,'06-05-2019')
,('C00220',0 ,41477,'16-05-2019')
,('C00220',57525 ,0 ,'01-04-2019')
,('C00220',0 ,57526,'08-07-2019')
,('C00220',45843 ,0 ,'15-10-2019')
,('C00220',1 ,0 ,'08-07-2019')
,('C00220',54575 ,0 ,'06-12-2019')
,('C00220',0 ,45843,'21-12-2019')
,('C00575',1593 ,0 ,'16-12-2019')
,('C00575',401 ,0 ,'16-12-2019')
,('C00576',180304,0 ,'19-12-2019')
) td (Code,Debit,Credit,RefDate)
;
--===== Demonstrate a solution
WITH cteEnumerate AS
(
SELECT PickNum = DENSE_RANK() OVER (PARTITION BY Code ORDER BY RefDate DESC)
,*
FROM #TestData
)
SELECT Code,Debit,Credit,RefDate
FROM cteEnumerate
WHERE PickNum = 1
ORDER BY Code
;
And, no... this is NOT a FIFO problem. At best, it only comes close to a LIFO problem but even fails that because it can return more than one row per code.
[EDIT] Apparently, (according to the other posts) I may have misread the original requirement. My code above doesn't produce "balances" that include previous rows. It only returns the last row(s) for each code, which seemed to be what was requested in the original post.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2020 at 3:43 pm
So if I understand the request - you're looking to find the unused lots from a FIFO inventory system. or rather the remaining inventory by lot and inventory code.
Reusing Jeff's test data harness. it looks something like this (note - this likely will not scale so great given the window functions, so you will want to test this carefully)
CREATE TABLE #TestData
(
Code CHAR(6)
,Debit INT
,Credit INT
,RefDate DATE
)
;
INSERT INTO #TestData
(Code,Debit,Credit,RefDate)
SELECT *
FROM (VALUES
('C00220',41477 ,0 ,'2019-05-06')
,('C00220',0 ,41477,'2019-05-16')
,('C00220',57525 ,0 ,'2019-04-01')
,('C00220',0 ,57526,'07-08-2019')
,('C00220',45843 ,0 ,'10-15-2019')
,('C00220',1 ,0 ,'07-08-2019')
,('C00220',54575 ,0 ,'12-06-2019')
,('C00220',0 ,45843,'12-21-2019')
,('C00575',1593 ,0 ,'12-16-2019')
,('C00575',401 ,0 ,'12-19-2019')
,('C00576',180304,0 ,'12-19-2019')
) td (Code,Debit,Credit,RefDate)
;
with balCTE as (
select code, sum(debit) d, sum(credit) c, sum(debit-credit) balance from #TestData
group by code
),
runningbal as (
select b.balance, b.c,
t.*,
sum(t.debit) over (partition by t.code order by t.refdate
rows unbounded preceding) fiforunning
from balcte b
join #TestData t on b.code=t.code
)
select *, case when fiforunning-c>debit
then debit else fiforunning-c end fifoopen
from runningbal
where fiforunning-c>0 and credit=0
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 6, 2020 at 6:29 pm
It would've been nice if we've gotten usable DDL. Did you read the requirements for posting DDL on forums? Apparently you don't seem to know that we need to have keys, constraints, and datatypes. So here's my guess what you might have done:
CREATE TABLE Ledger
(acct_nbr CHAR(6) NOT NULL,
posting_date DATE DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (acct_nbr, posting_date),
posting_amt DECIMAL NOT NULL
CHECK (posting_amt <> 0.00));
Please note that they had to create a primary key. A table must have a key but you gave us in your picture left us guessing. It looks like the date and the account number are the only keys you've got. You also used the most ambiguous date display format available to you. Please be professional enough to follow ISO 8601 in the future; it is the only display format allowed in ANSI/ISO standard SQL
I would assume center talking about debits and credits, that the amounts shown are monetary. As far as I know, there's no currency on earth that is not decimal. So you pick the wrong data type.
My next correction is a little more controversial. The reason that debits and credits exist has to do with the fact that when modern bookkeeping began in the Renaissance, they had to use Roman numerals. In fact, a number of the accounting firms in Italy bragged that they only use Roman numerals and not those pagan, unchristian, newfangled Arabic numerals! Starting in the 1950s, this style of recording financial data was replaced in these newfangled computer systems (called electronic data processing back then) , with positive and negative numbers. Did you notice that you have to have a zero debit when you have a positive credit, or a zero credit when you have a positive debit? It didn't take too long for people to realize that keeping the old debit/credit columns in the incredibly expensive computer storage of the day was a total waste. It also meant that you had to enforce that rule with more programming as well as wasted storage. If I could find it. I would quote a little handbook I had entitled "matrix methods in accounting" that give some of the rules for modern bookkeeping.
INSERT INTO Ledger
VALUES
('C00220', '2019-05-06', -41477.00),
('C00220', '2019-05-16', 41477.00),
('C00220', '2019-04-01', -57525.00),
('C00220' ,'2019-07-08', 57526.00),
('C00220', '2019-10-15', -45843.00),
('C00220', '2019-07-08', -1.00),
('C00220', '2019-12-06', -54575.00),
('C00220' ,'2019-12-21', -45843.00),
('C00575', '2019-12-16', -1593.00),
('C00575', '2019-12-16', -401.00),
('C00576', '2019-12-19', -180304.00);
SELECT acct_nbr, SUM(posting_amt) AS acct_balance
FROM Ledger
GROUP BY acct_nbr;
Please post DDL and follow ANSI/ISO standards when asking for help.
January 6, 2020 at 9:15 pm
Joe -
not sure I would jump to the conclusion that OP is wrong. Given the request, pretty sure that debit and credits represent units in inventory NOT currency.
As to just simply recommending fundamental changes to an accounting ledger....wow. I'd be careful with dispensing the usual diatribe when dealing with financial content, and I can't believe you don't know better.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 6, 2020 at 11:11 pm
>> Given the request, pretty sure that debit and credits represent units in inventory NOT currency. <<
Why would you say "debit" and "credit" which are terms from accounting? What
about "restock" , "withdrawals", "shrinkage", etc?
>> As to just simply recommending fundamental changes to an accounting ledger....wow <<
And I also recommend normalizing databases ..Wow! Seriously, I do not like using a data model that is hundreds of years old and based on Roman numerals. O probably ought to write an article or book chapter on the changes that came with EDP and Arabic numerals.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 7, 2020 at 1:27 am
And I also recommend normalizing databases ..Wow! Seriously, I do not like using a data model that is hundreds of years old and based on Roman numerals. O probably ought to write an article or book chapter on the changes that came with EDP and Arabic numerals.
While I understand and may even agree with your assessment about the terms used, there's not a Roman Numeral in sight. No need to resort to all that passive-aggressive snarky junk, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2020 at 5:22 pm
>> While I understand and may even agree with your assessment about the terms used, there's not a Roman Numeral in sight. No need to resort to all that passive-aggressive snarky junk. <<
I don't think I was particularly snarky. The point I'm trying to make: we keep doing whatever we inherited from our ancestors, long after it's been replaced by much better methods. One of my favorite cartoons is to cavemen trying to move a big rock while a third caveman is standing off to the side, offering them a wheel. The caption is "We don't have time to try that." and "We've always done it this way and it works."
How many postings have you seen where the code is in all uppercase, just like we did on punch cards? Short cryptic names for data elements, just like we did on punch cards? A leading comma on each line of code, just like we did on punch cards? Local dialect date formats, instead of the ISO standards?
My point about Roman numerals was that they don't have signs (among other things) because they were intended for recording and not computation. There's a famous woodcut in one of the first math books on the use of the new Arabic numerals. It features an angelic figure at the top (of course), and on one side the algorismist (really happy guy) and the traditional accountant (frowning and miserable) on the other side. This is a really important part of how we now view mathematical notation. It's not just for recording something like we had to do with debits and credits but to use it for computation.
The term "bankrupt" comes from the Italian that meant to break (rupture) somebody's bank (computing board) . The actual computations were done on a board with a line down the center (just like the classic accounting debit and credit diagrams and all the bookkeeping text) and horizontal lines across (units, fives, tens, fractional currency units, etc.) and a bowl full of tokens (jetons) that were used much like the beads on an abacus.
I really need to do this up as an article somewhere! People don't appreciate how hard it is to avoid mimicking the original ancestral technology when you never bothered to learn the replacements.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 7, 2020 at 5:23 pm
>> Given the request, pretty sure that debit and credits represent units in inventory NOT currency. <<
Why would you say "debit" and "credit" which are terms from accounting? What
about "restock" , "withdrawals", "shrinkage", etc?
>> As to just simply recommending fundamental changes to an accounting ledger....wow <<
And I also recommend normalizing databases ..Wow! Seriously, I do not like using a data model that is hundreds of years old and based on Roman numerals. O probably ought to write an article or book chapter on the changes that came with EDP and Arabic numerals.
For someone who just finished expounding on the use of standards, recommending that someone change away from the *well established and still currently in force* industry standard for the field of accounting rings a bit phony. The review standards applied to said antiquated concepts would make ISO look like playground antics. Perhaps you should go get on the IFRS review committee and tell them how antiquated and ridiculous their model is.
Of course I probably wouldn't be objecting so strenuously if you weren't spending so much time on answering anything other than something useful to the OP's request.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 7, 2020 at 5:48 pm
My next correction is a little more controversial. The reason that debits and credits exist has to do with the fact that when modern bookkeeping began in the Renaissance, they had to use Roman numerals.
Baloney. Debits and credits exist because they are two separate pieces of information, both of which are needed. How they are physically written or stored in the table is a separate issue, albeit still an important one.
Separate entries / columns is better in written ledgers, since it's easier for people to add columns of numbers without having to add and subtract.
But separate entries are still often useful in a computerized system as well. Keep in mind, for some accounting categories, debits are negative while credits are positive, and vice versa for other categories. If you store both debit and credit values in one column, you must constantly determine the category of data before knowing how to aggregate debits or credits.
For example, with separate columns you can do this:
SUM(debit_amount) AS debit_total
and it's correct, no matter the accounting category of the entry.
With a single column, you must do EITHER this:
SUM(CASE WHEN posting_amount > 0 THEN posting_amount ELSE 0 END) AS debit_total
OR this, depending on the specific type of accounting entry:
SUM(CASE WHEN posting_amount < 0 THEN posting_amount ELSE 0 END) AS debit_total
:Shudder:. What a mess!
Certainly there are times when the entry type is known you may prefer to store the amount in a single column. But if you might need to combine lots of different data, you might still prefer to keep separate columns.
I defy you to produce a proper Balance Sheet without distinguishing between debits and credits. Show me how that would be done.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 7, 2020 at 7:05 pm
>> I don't think I was particularly snarky.
THAT, good Sir, is the crux of the problem. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2020 at 7:41 pm
jcelko212 32090 wrote:>> I don't think I was particularly snarky.
THAT, good Sir, is the crux of the problem. 😉
I don't think his first post was particularly snarky, esp. for him, although the bit about Roman numerals was absurd. As was punch cards, as always:
Joe, the vast majority of people working today on computer systems have never used punch cards! Enough already with that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 8, 2020 at 10:19 pm
I defy you to produce a proper Balance Sheet without distinguishing between debits and credits. Show me how that would be done.
I gotta go with Joe on this. The sign on the entry line in combination with the chart of accounts should square away what a debit is and what a credit is. You REALLY REALLY need that chart of accounts in any case, otherwise what would you validate your account number against? Heck even with your proposal that mandates a column be null for every line, you STILL have to consult your chart of accounts to determine which column holds a number, and which column is null, right? So since you have produced your chart of accounts, make it work, and for petes sake, learn case statements.
There is another alternative, including information in the journal entry line that specifies debit or credit, although its probably not as efficient as the sign of our numeric amount. Your idea is of course another alternative, but it seems counterintuitive because it spends a numeric sized column that for each line, one of them must be null, and heck with everyone here jumping on every last byte in an effort to gain advantage, aren't you sort of going against the flow? (well, not the flow of attacking Mr. Celko that is, that's a different flow)
Really, once you've written an accounting system from scratch, even if its a toy, you'll come to realise that rather than summing your debit column only, in reality you add the credits and subtract the debits (or vice versa depending on the account) while grouping by the account number. Summing debits only in a journal entry is a bit nonsensical honestly, because chances are, a multiline journal entry may have lines for multiple accounts and the only reason you'd sum an individual journal entry's debits is when you'd also sum the credits and get that balance to zero for THAT PARTICULAR journal entry. Getting your assets - liabilities = capital come from valid journal entries. Dual entry accounting does not mean you have a column for a debit and a column for a credit, it means something a bit more higher level than that.
We use the debit and credit column in old green lined journals because we were always going to have one journal line on one physical line on the page and it really reads well and frankly your wasting paper by having each journal line be at least a physical line. Because we can reproduce journal entry reports based on only the chart of accounts and Joe's signed amount column, there's no need for your mandatory null numeric sized column on each row when a simple bit sized piece of info will do the job. Joe just picked the most efficient place to put the bit, and furthermore he probably doesn't face your struggles with case statements 🙂
Here's a pretty darn good writeup in general:
https://stackoverflow.com/questions/59432964/relational-data-model-for-double-entry-accounting
But seriously, since having a chart of accounts literally lets you have your credit / debit info that can apply to THOUSANDS of individual journal lines, having the two columns you're talking about just doesn't seem to buy you anything except allow rookies that struggle with case statements to query your financials. And do you REALLY want rookies querying your financials without reaching the level that requires understanding case statements?
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply