By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Subcode
(`Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(12))
;
INSERT INTO Subcode
(`Maincode`, `Controlcode`, `Subcode`, `Description`)
VALUES
(01, 01, 123, 'Test Account'),
(01, 02, 124, 'Test Account')
;
CREATE TABLE Voucher
(`Voucherdate` Date, `Vouchertype` varchar(2), `Vouchernumber` int, `Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(14), `Debit` int, `Credit` int)
;
INSERT INTO Voucher
(`Voucherdate`, `Vouchertype`, `Vouchernumber`, `Maincode`, `Controlcode`, `Subcode`, `Description`, `Debit`, `Credit`)
VALUES
('2019-07-13', 'BV', 01, 01, 01, 123, 'Entering Test', 100, 0),
('2019-07-13', 'BV', 01, 01, 02, 124, 'Enterting Test', 0, 100)
;
SELECT * FROM Voucher;
| Voucherdate | Vouchertype | Vouchernumber | Maincode | Controlcode | Subcode | Description | Debit | Credit |
|---|---|---|---|---|---|---|---|---|
| 2019-07-13 | BV | 1 | 1 | 1 | 123 | Entering Test | 100 | 0 |
| 2019-07-13 | BV | 1 | 1 | 2 | 124 | Enterting Test | 0 | 100 |
CREATE TABLE OpeningBalance
(`Maincode` int, `Controlcode` int, `Subcode` int, `Debitbalance` int, `Creditbalance` int)
;
INSERT INTO OpeningBalance
(`Maincode`, `Controlcode`, `Subcode`, `Debitbalance`, `Creditbalance`)
VALUES
(01, 01, 123, 100, 0),
(01, 02, 124, 100, 0)
;
SELECT
s.Description
,o.Maincode
,o.Controlcode
,o.Subcode
, o.`Debitbalance` Mainbalance
, v.`Debit` + o.`Debitbalance` Totaldebit
, v.`Credit` + o.`Creditbalance` Totalcredit
, v.`Debit` + o.`Debitbalance` -( v.`Credit` + o.`Creditbalance`) Openingbalance
FROM OpeningBalance o inner Join Voucher v
ON o.Maincode = v.Maincode AND o.Controlcode = v.Controlcode AND o.Subcode = v.Subcode
INNER JOIN Subcode s ON o.Maincode = s.Maincode AND o.Controlcode = s.Controlcode AND o.Subcode = s.Subcode
WHERE MONTH(v.voucherdate) <= 7 AND YEAR(v.voucherdate) <= 2019
| Description | Maincode | Controlcode | Subcode | Mainbalance | Totaldebit | Totalcredit | Openingbalance |
|---|---|---|---|---|---|---|---|
| Test Account | 1 | 1 | 123 | 100 | 200 | 0 | 200 |
| Test Account | 1 | 2 | 124 | 100 | 100 | 100 | 0 |
SELECT (SELECT sc.description AS description,
sc.maincode,
sc.controlcode,
sc.subcode,
( ob.debitbalance - ob.creditbalance ) AS mainbalance
FROM Subcode AS sc
LEFT JOIN OpeningBalance AS ob
ON ( sc.maincode = ob.maincode
AND sc.controlcode = ob.controlcode
AND sc.subcode = ob.subcode )
GROUP BY sc.maincode,
sc.controlcode,
sc.subcode
ORDER BY sc.maincode,
sc.controlcode,
sc.subcode ASC) AS test,
(SELECT Sum(v.debit) AS totaldebit,
Sum(v.credit) AS totalcredit,
( mainbalance + totaldebit - totalcredit ) AS openingbalance
FROM Subcode AS sc
LEFT JOIN Voucher AS v
ON ( sc.maincode = v.maincode
AND sc.controlcode = v.controlcode
AND sc.subcode = v.subcode )
WHERE Substring(v.voucherdate, 1, 7) < '07-2019'
GROUP BY sc.maincode,
sc.controlcode,
sc.subcode
ORDER BY sc.maincode,
sc.controlcode,
sc.subcode ASC) AS test2
Operand should contain 1 column(s)
SELECT Substring(voucherdate, 1, 7) FROM Voucher
| Substring(voucherdate, 1, 7) |
|---|
| 2019-07 |
| 2019-07 |