Debit, Credit not showing correct result on Same Date

Related searches

Scenario : I am working on Users Accounts where Users add amount to there account (Credit) and they withdraw their desire amount from their account (Debit), all is going correct but when User credit or debit on same dates it gives me wrong result (Balance). here refno is reference of user. here is my Query

  declare @startdate date='2013-01-02',
            @enddate date='2013-01-12';


    With summary(id,refno,transdate,cr,dr,balance)
    as
    (
    select id,
           RefNo,
        Cast(TransDate as Varchar),
         cr,
         dr,
           (cr-dr)+( Select ISNULL(Sum(l.Cr-l.Dr) ,0)
         From Ledger l
         Where l.TransDate<Ledger.TransDate and refno=001 )  as balance 
    from Ledger 
    ),
    openingbalance(id,refno,transdate,cr,dr,balance)
    As (
    select top 1 '' id,'OPENING BAL','','','',balance
    from summary
    where transdate<@startdate
    order by transdate desc
    )


    select *
    from openingbalance
    union

    Select *
    From summary
    where transdate between @startdate and @enddate and refno=001 order by transdate

The issue is because when you query for the previous balance you are only looking at records that have a transdate earlier than the current record, so this means any records that have the same date will not be included.

The solution here would be to use a more unique sequential value, in your example you could use the ID value as the sequential identifier instead. However, ID values are not always the best for ensuring sequence. I would recommend extending your transdate column to use a more precise value and include the time of the transactions. Seconds would likely be enough precision if you can guarantee that there will never be multiple transactions made within a given second, but whatever you decide you need to be confident there will not be any duplicates.


In an attempt to provide a code change solution that will work with your existing data you can try the following, which uses the id value to determine if a record is prior to the current record:

Change the following line:

Where l.TransDate<Ledger.TransDate and refno=001 )  as balance

to this:

Where l.ID<Ledger.ID and refno=001 )  as balance

Double-entry bookkeeping, Only accounts containing at least one transaction show in the list. sum of the subtotals within each column, regardless of debit and credit values. by the correct column when using the running balance, for example, by the date column with Bills, invoices and credit notes display if the date they were created in Xero falls� table entities: id credit debit value date 1 100 101 5000 01/01/2014 table accounts: id name 100 Mark 101 Jone Debit, Credit not showing correct result on Same

If you are using SQL 2012 or above, then instead of

SELECT id, RefNo, TransDate,cr, dr, (cr-dr) + (Select ISNULL(Sum(l.Cr-l.Dr) ,0) 
FROM Ledger l 
WHERE Cast(l.TransDate as datetime) < Cast(Ledger.TransDate as datetime) 
AND refno=001) as balance from Ledger

Use:

SELECT id, RefNo, TransDate, cr, dr, SUM(cr- dr) OVER(ORDER BY TransDate ROWS 
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance

Account Transactions report, An incorrect amount has been debited or a payment has been collected on the wrong date. A Direct Debit Instruction should be set up as outlined in Making� Other Debit and Credit Issues. A debit is commonly abbreviated as dr. in an accounting transaction, while a credit is abbreviated as cr. in the transaction. Debits and credits are not used in a single entry system.

After hint by @musefan i made changes to query and it is working as i want. here is query for Date Base

declare @startdate date='2013-01-02',
        @enddate date='2013-01-12';


With summary(id,refno,transdate,cr,dr,balance)
as
(
select id,
       RefNo,
   TransDate,
     cr,
     dr,
       (cr-dr)+( Select ISNULL(Sum(l.Cr-l.Dr) ,0)
     From Ledger l
     Where Cast(l.TransDate as datetime)< Cast(Ledger.TransDate as datetime) and refno=001 )  as balance 
from Ledger 
),
openingbalance(id,refno,transdate,cr,dr,balance)
As (
select top 1 '' id,'OPENING BAL','','','',balance
from summary
where transdate<@startdate
order by transdate desc
)


select id,refno,Cast(TransDate as varchar) as datetime,cr,dr,balance
from openingbalance
union

Select id,refno,Cast(TransDate as varchar)as datetime,cr,dr,balance
From summary
where transdate between @startdate and @enddate and refno=001 order by Cast(TransDate as varchar)

and Another Query Id Based

declare @startdate date='2013-01-02',
        @enddate date='2013-01-12';


With summary(id,refno,transdate,cr,dr,balance)
as
(
select id,
       RefNo,
   TransDate,
     cr,
     dr,
       (cr-dr)+( Select ISNULL(Sum(l.Cr-l.Dr) ,0)
     From Ledger l
     Where l.id < Ledger.id  and refno=001 )  as balance 
from Ledger 
),
openingbalance(id,refno,transdate,cr,dr,balance)
As (
select top 1 '' id,'OPENING BAL','','','',balance
from summary
where transdate<@startdate
order by transdate desc
)


select id,refno,Cast(TransDate as varchar) as datetime,cr,dr,balance
from openingbalance
union

Select id,refno,Cast(TransDate as varchar)as datetime,cr,dr,balance
From summary

where transdate between @startdate and @enddate and refno=001 order by id

Incorrect payments, Exactly the Same as the Physical Credit Card (90% Get It Wrong) checkout, needless validation errors, and abandonments as a result. I have a list where amounts displayed are either Debits or Credits. I would like to match identical Amounts irrespective of whether it is debit or credit. The values in Column 1 should also be identical. Example Col1 Col2 1 A 300 2 B 500 3 C 100

Format the 'Expiration Date' Fields Exactly the Same as the Physical , Also ensure your card is actually a credit card and not just a debit card. type MO/TO (which stands for mail order/telephone order); Wrong expiration date Loan balance increases (credit) I take out a Loan for Car = Debit (increase) new Fixed Asset. I have a Car Loan = Credit (balance is higher) Then, pay down the loan principal: Check = credit Bank $100 (decreases my balance) Loan = debit (loan balance goes down by $100) Using QB means you DON'T need to think about Debits and Credits and using JE

Why does my online credit card payment fail?, id Debit Credit Balance 1 10 0 10 2 0 40 -30 3 50 0 20 4 0 10 10 5 0 10 0 6 100 10 90 7 0 10 80 8 0 110 -30 Permalink Posted 19-Feb-18 4:58am

Recording Debits and Credits for Liability and Owner's Equity Accounts Liabilities are items on a balance sheet that the company owes to vendors or financial institutions. They can be current liabilities, such as accounts payable and accruals, or long-term liabilities , such as bonds payable or mortgages payable.

Comments
  • this is because of same date.add one more condition after and refno=001 in inner sub query and exclude dr belonging to id 6
  • i made changes in my query account to your hint to transferdate to date and time but it also gives me wrong result, Would u please suggest me some suggestions
  • @SaeedKhan: I would need to see your new table structure, with example data (like your current screenshot). Why doesn't it work? the same problem as before?