Converting MySQL Simple Query to SQL Server

Question

I have Completed MySQL Simple Query,

SELECT 
  acc_trans.VoucherNumber,
  acc_trans.EntryDate,
  acc_trans.Debit,
  acc_trans.Credit,
  @Balance:= round(@Balance,2) + acc_trans.Debit - acc_trans.Credit AS  Balance
FROM acc_trans, (SELECT @Balance := 0) AS variableInit Where AccountName='Cash Account'
ORDER BY  acc_trans.TransactionID ASC

output

VoucherNumber  EntryDate    Debit         Credit      Balance
-------------------------------------------------------------
1            2019-01-12      0.00       2500.00     -2500.00
2            2019-02-12      0.00      15000.00    -17500.00
3            2019-02-12      0.00       1500.00    -19000.00
1            2019-02-12   4800.00        0.00      -14200.00

This Query Working Good MySQL Database. I have Same Database with SQL Server. How to Do this Query in SQL Server?

SQL is not database is that mean sql server?

Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin

2019-03-11 07:09:08 +00:00
Commented Mar 11, 2019 at 7:09 — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Mar 11, 2019 at 7:09
@ZaynulAbadinTuhin hmm made my mistake

safvan saf
– safvan saf

2019-03-11 07:13:07 +00:00
Commented Mar 11, 2019 at 7:13 — safvan saf
– safvan saf, Commented Mar 11, 2019 at 7:13

Zhorov · Accepted Answer · 2019-03-11 09:18:22Z

Starting from SQL Server 2008 you may use OVER clause:

Input:

CREATE TABLE acc_trans (
    TransactionID int,
    VoucherNumber int,
    EntryDate date,
    Debit numeric(20, 2),         
    Credit numeric(20, 2)
)
INSERT INTO acc_trans 
    (TransactionID, VoucherNumber, EntryDate, Debit, Credit)
VALUES
    (1, 1, '2019-01-12',    0.00,  2500.00),
    (2, 2, '2019-02-12',    0.00, 15000.00),
    (3, 3, '2019-02-12',    0.00,  1500.00),
    (4, 1, '2019-02-12', 4800.00,     0.00)

Statement:

SELECT 
  acc_trans.VoucherNumber,
  acc_trans.EntryDate,
  acc_trans.Debit,
  acc_trans.Credit,
  [Balance] = SUM(acc_trans.Debit - acc_trans.Credit) OVER (ORDER BY  acc_trans.TransactionID ASC)
FROM acc_trans
WHERE AccountName = 'Cash Account'

Output:

VoucherNumber   EntryDate   Debit    Credit     Balance
1               2019-01-12  0.00     2500.00    -2500.00
2               2019-02-12  0.00     15000.00   -17500.00
3               2019-02-12  0.00     1500.00    -19000.00
1               2019-02-12  4800.00  0.00       -14200.00

Notes:

If you want to assign values to variables in SQL Server with SELECT statement, the remarks from documentation are important:

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-03-11 07:47:56Z

1

This needs to work

declare @Balance int=0;

SELECT 
  acc_trans.VoucherNumber,
  acc_trans.EntryDate,
  acc_trans.Debit,
  acc_trans.Credit,
  @Balance = round(@Balance,2) + acc_trans.Debit - acc_trans.Credit AS  Balance
FROM acc_trans
Where AccountName='Cash Account'
ORDER BY  acc_trans.TransactionID ASC

answered Mar 11, 2019 at 7:47

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

Converting MySQL Simple Query to SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related