Create Journal Ledger view

[akhafaji]

I have one view contains all transactions done between accounts
The following rows will explain my problem

TransNo| JVno| AcNo| debit| credit | AcName
13 | 7 | 1430 |70000 | 0 | machines
14 | 7 | 1001 | 0 | 30000 | cash in bank
15 | 7 | 2010 | 0 | 40000 | suppliers


I want to make journal ledger view for account name machines
But when i do that it come like this

TNo| JVno|MyAcNo| debit | credit| otherAc | otherAcName
13 | 7 | 1430 | 70000| 0 | 1001 | cash in bank
13 | 7 | 1430 | 70000 | 0 | 2010 | suppliers

I want reduce this 2 rows in one row to make like
TNo| JVno|MyAcNo| debit | credit| otherAc | otherAcName
13 | 7 | 1430 | 70000| 0 | ..... | AFORESAID

Can I ?!

[MAK]

what is MyAcno, otherAc and OtherAcname?

need more explanation on how you are making up 2 rows first.

[akhafaji]

Hiya,


In accounting the transaction is often done between 2 accounts, but some time the transactions can be done between more than 2 accounts
For example if you sale some lands from your assets in your company and get your amount cash the transaction will come like this:

||accountid||accountname||debit ||credit
||1000 || cash in hand||15000 || 0
||1400 || lands || 0 || 15000

(Simple transaction has 2 side debit and credit)

I have no problem with this transaction.
But some time if the transactions done between more than 2 accounts it come complex, for example
If you purchased machines for you company and paid the amount in two parts, first part is cash and the second part by cheque the transaction will come like this

||accounted||accountname ||debit ||credit
||1450 || machines ||10000 || 0
||1000 || cash in hand || 0 || 5000
||1001 || cash in bank || 0 || 5000

Please copy those statements and use it in tempdb
/************************************************** ***********/
CREATE TABLE [dbo].[Accounts](
[Accountid] [int] NOT NULL,
[AccountName] [nvarchar](200) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
(
[Accountid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/************************************************** ***********/
INSERT INTO Accounts VALUES ('1000','CASH IN HAND')
INSERT INTO Accounts VALUES ('1001','CASH IN BANK')
INSERT INTO Accounts VALUES ('1400','LANDS')
INSERT INTO Accounts VALUES ('1450','MACHINES')
/************************************************** ***********/
CREATE TABLE [dbo].[JournalVoucherHeader](
[JVNO] [int] NOT NULL,
[JVDate] [datetime] NULL CONSTRAINT [DF_JournalVoucherHeader_JVDate] DEFAULT (getdate()),
[JVTXT] [text] COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_JournalVoucherHeader] PRIMARY KEY CLUSTERED
([JVNO] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

/************************************************** ***********/
CREATE TABLE [dbo].[TransactionsDetails](
[TransactionNo] [int] IDENTITY(1,1) NOT NULL,
[JVNO] [int] NULL,
[Accountid] [int] NULL,
[Debit] [decimal](18, 0) NULL,
[Credit] [decimal](18, 0) NULL,
CONSTRAINT [PK_TransactionsDetails] PRIMARY KEY CLUSTERED
([TransactionNo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/************************************************** ***********/
/************************************************** ***********/

Note :
Table [JournalVoucherHeader] is for write journal header
And its contain the following columns
[JVNO]: Is Journal Voucher Header No
[JVDate]: journal Date
[JVTXT] : the description of the transaction

Table [TransactionsDetails] which is contain all transactions done between accounts, and we have jvno as reference from [JournalVoucherHeader] Table,
Its contain the following columns
[TransactionNo]: Is transaction No
[JVNO]: Is Journal Voucher Header No
[Accountid]: account number
[Debit]: the debit amount
[Credit]: the credit amount

Now if I want to insert those records which in the examples in above it will
Come like this

Example 1:

INSERT INTO [JournalVoucherHeader]
VALUES('1','01-01-06',' sale lands ')
/*------------------------------------------------*/
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit])
VALUES
('1' ,'1000',15000,0)
/*------------------------------------------------*/
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit])
VALUES
('1' ,'1400',0,15000)

Example 2:

INSERT INTO [JournalVoucherHeader]
VALUES('2','01-01-06','purchase machines')
/*------------------------------------------------*/
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit])
VALUES
('2' ,'1450',10000,0)
/*------------------------------------------------*/
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit])
VALUES
('2' ,'1000',0,5000)
/*------------------------------------------------*/
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit])
VALUES
('2' ,'1001',0,5000)

Now I will start explain what I need to do.
In accounting we create journal ledger which is contain all posted transactions
Indexed by account, I will show how it creates in accounting:
- Journal ledger for account Name: land ( accounted=1400)

Jvno || accountid || description || debit ||credit || balance||
1 ||1000 || CASH IN HAND ||0 || 15000 || -15000 ||

If I try to do it by SQL It will be no problem:

First:
CREATE view
[dbo].[vTransactionsDetails] as
SELECT TransactionsDetails.*, Accounts.AccountName
FROM TransactionsDetails INNER JOIN Accounts
ON TransactionsDetails.Accountid = Accounts.Accountid

Second Statement:

SELECT
t.JVNO,t1.Accountid, t1.AccountName,
t.Debit, t.Credit,
(SELECT Sum(debit-credit) FROM TransactionsDetails AS x
WHERE t.JVNO >= x.JVNO and accountid='1400') AS Balance
FROM
vTransactionsDetails t INNER JOIN vTransactionsDetails t1
ON t.JVNO = t1.JVNO AND t.Accountid <> t1.Accountid
WHERE t.Accountid = '1400'

The result will come like this:
Jvno || accountid || description || debit ||credit || balance||
1 || 1000 || CASH IN HAND || 0 || 15000 || -15000||

What actually happen when we create the journal ledger?
When you try to display journal ledger for any account it will give you the other side
From that transaction which is done on this account, like in this example the transaction was between 2 accounts (cash in hand and lands) like this

||accountid||accountname||debit ||credit
||1000 || cash in hand||15000 || 0
||1400 || lands || 0 || 15000

And we need our account side of this transacton but with other account name like this
Jvno || accountid || description || debit ||credit || balance||
1 || 1000 || CASH IN HAND || 0 || 15000 || -15000||

Until now no problem.

The problem will appear in this case:
If we need to make journal ledger for account name machines (accounted=1450).
It should be (in accounting) like this:

Jvno || accountid || description || debit ||credit || balance||
1 || ……. || mentioned || 0 || 10000 || -10000 ||

But if we try to make the same statement in above to display the journal ledger for this account it will come like this

SELECT
t.JVNO,t1.Accountid, t1.AccountName,
t.Debit, t.Credit,
(SELECT Sum(debit-credit) FROM TransactionsDetails AS x
WHERE t.JVNO >= x.JVNO and accountid='1450') AS Balance
FROM
vTransactionsDetails t INNER JOIN vTransactionsDetails t1
ON t.JVNO = t1.JVNO AND t.Accountid <> t1.Accountid
WHERE t.Accountid = '1450'

Jvno || accountid || description || debit ||credit || balance||
2 || 1000 || cash in hand || 10000 || 0 || 10000 ||
2 || 1001 || cash in bank || 10000 || 0 || 10000 ||


My Q is = how I can make journal ledger in this case to come like this

Jvno || accountid || description || debit ||credit || balance||
1 || ….. || mentioned || 0 || 10000 || -10000 ||

[MAK]

--please change @accountno value to your desired accountno like 1450 or 1400 etc. Let me know

use tempdb
go
CREATE TABLE [dbo].[Accounts](
[Accountid] [int] NOT NULL,
[AccountName] [nvarchar](200) NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
([Accountid] )
)
CREATE TABLE [dbo].[JournalVoucherHeader](
[JVNO] [int] NOT NULL,
[JVDate] [datetime] NULL CONSTRAINT [DF_JournalVoucherHeader_JVDate] DEFAULT (getdate()),
[JVTXT] [text] NULL,
CONSTRAINT [PK_JournalVoucherHeader] PRIMARY KEY CLUSTERED
([JVNO] ASC
)
)
CREATE TABLE [dbo].[TransactionsDetails](
[TransactionNo] [int] IDENTITY(1,1) NOT NULL,
[JVNO] [int] NULL,
[Accountid] [int] NULL,
[Debit] [decimal](18, 0) NULL,
[Credit] [decimal](18, 0) NULL,
CONSTRAINT [PK_TransactionsDetails] PRIMARY KEY CLUSTERED
([TransactionNo] ASC
)
)

INSERT INTO [JournalVoucherHeader] VALUES('1','01-01-06','Sale lands')
INSERT INTO [JournalVoucherHeader] VALUES('2','01-01-06','Purchase machines')


INSERT INTO Accounts VALUES ('1000','CASH IN HAND')
INSERT INTO Accounts VALUES ('1001','CASH IN BANK')
INSERT INTO Accounts VALUES ('1400','LANDS')
INSERT INTO Accounts VALUES ('1450','MACHINES')

INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit]) VALUES ('1' ,'1000',15000,0)
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit]) VALUES ('1' ,'1400',0,15000)
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit]) VALUES ('2' ,'1450',10000,0)
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit]) VALUES ('2' ,'1000',0,5000)
INSERT INTO [tempdb].[dbo].[TransactionsDetails]
([JVNO],[Accountid],[Debit],[Credit]) VALUES ('2' ,'1001',0,5000)


go
CREATE view
[dbo].[vTransactionsDetails] as
SELECT TransactionsDetails.*, Accounts.AccountName
FROM TransactionsDetails INNER JOIN Accounts
ON TransactionsDetails.Accountid = Accounts.Accountid

go




declare @accountid int
set @accountid = 1400

select JVNO,min(accountname) as AccountName,min(Accountid) as AccountId, sum(Debit) as debit,
sum(Credit) as credit, (select debit-credit from vTransactionsDetails where accountid=@accountid ) AS Balance from
(
SELECT
t.JVNO,t1.Accountid, t1.AccountName,
t.Debit, t.Credit
FROM
vTransactionsDetails t INNER JOIN vTransactionsDetails t1
ON t.JVNO = t1.JVNO AND t.Accountid <> t1.Accountid
WHERE t1.Accountid =@accountid
) as mytable
group by JVNO

[akhafaji]

DEAR MAK
ITS WORK WHEN THE SELECTED ACCOUNT
DID NOT HAS COMPLEX TRANSACTION (2 ACCOUNT) BUT IF I USE THIS STATEMENT ON ANY ACCOUNT WAS A SIDE IN COMPLEX TRANSACTION THIS MESSAGE COME TO ME :
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
THANKS

[MAK]

1450 has 2 accounts. it works.

If you have more complex accounts please post the insert statements, so that I can test it.

[akhafaji]

please copy those statement and use temp

INSERT INTO [dbo].[Accounts]
VALUES ('1060' ,'Customers')

INSERT INTO [Accounts]
VALUES ('3001' ,'revenue')

INSERT INTO [JournalVoucherHeader]
VALUES ('3' ,'2-1-2006' ,'COMPLEX TRANSACTION')


INSERT INTO [TransactionsDetails]
VALUES ('3', '1000',28000,0)

INSERT INTO [TransactionsDetails]
VALUES ('3', '1060',12000,0)

INSERT INTO [TransactionsDetails]
VALUES ('3', '3001',0,40000)

now if you set @accountno =1000 or 1060 or 3001
you will see that message.

thanks

[MAK]

you cannot use 1000 . you should use 3001 for this.

or else

if you have the same account used many times, you have to find the uniqueness which is JVno.

declare @JVno int
declare @accountno int
set @JVno=3
set @accountno=3001 --or 1000

select JVNO,min(accountname) as AccountName,min(Accountid) as AccountId, sum(Debit) as debit,
sum(Credit) as credit, (select debit-credit from vTransactionsDetails where JVNO=@JVNO and accountid=@accountno) AS Balance from
(
SELECT
t.JVNO,t1.Accountid, t1.AccountName,
t.Debit, t.Credit
FROM
vTransactionsDetails t INNER JOIN vTransactionsDetails t1
ON t.JVNO = t1.JVNO AND t.Accountid <> t1.Accountid
WHERE t1.Accountid =@accountno and t.jvno=@JVNo
) as mytable
group by JVNO

[akhafaji]

thanks
but it will not be easy for end users , actually after i copy your statement i got an idea i am not sure but i will try to make it .
thanks again for your useful help

[MAK]

let us know how it goes

[akhafaji]

dear MAK
It Can done by this statement :


CREATE proc [dbo].[tst]
@accountid int as
SELECT transactionno,y.JVNO,jvdate,accountid,
--1
(case when
exists(select count(N.credit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0 and N.credit=0
group by N.credit having count(N.credit)<1 )
then
(select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0 and N.credit=0 )

else case when
exists(select count(N.DEBIT) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0 and N.credit=0
group by N.debit having count(N.DEBIT)<1 )
then
(select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0 and N.credit=0 )
--3
else case when
exists (select count(jvno) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit<>N.credit
and Y.debit<>N.debit group by jvno having count(jvno)>1 )
then
' Mentioned '


ELSE CASE WHEN
(select count(N.debit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=N.credit
and Y.debit<>N.debit AND Y.debit<>0 AND N.debit<>0
group by N.debit having count(N.debit)=0 ) <>' '
OR
(select count(N.credit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit=N.debit
and Y.credit<>N.credit AND Y.credit<>0 AND N.credit<>0
group by N.credit having count(N.credit)=0 ) <>' '
then
' Mentioned '
ELSE CASE WHEN
(select count(N.debit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=N.credit
and Y.debit<>N.debit AND Y.debit<>0 AND N.debit<>0
group by N.debit having count(N.debit)=1 ) <>' '
AND
(select count(N.credit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit=N.debit
and Y.credit<>N.credit AND Y.credit<>0 AND N.credit<>0
group by N.credit having count(N.credit)=1 ) <>' '
then ' Mentioned '

ELSE CASE WHEN
(select count(N.debit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=N.credit
and Y.debit<>N.debit AND Y.debit<>0 AND N.debit<>0
group by N.debit having count(N.debit)=1 ) <>' '

then (select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND N.credit<>0)

ELSE CASE WHEN
(select count(N.credit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit=N.debit
and Y.credit<>N.credit AND Y.credit<>0 AND N.credit<>0
group by N.credit having count(N.credit)=1 ) <>' '
then (select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND N.DEBIT<>0)

else (select accountname FROM vTRANSACTIONsDETails AS n
WHERE Y.JVNO = N.JVNO and n.accountid<>@accountid
and Y.transactionno<>N.transactionno)
END END END END END END END ) as Description
,Debit,Credit,
(SELECT Sum(debit-credit)
FROM TRANSACTIONsDETails AS x
WHERE y.JVNO >= x.JVNO and accountid=@accountid) AS
Balance
FROM vTRANSACTIONsDETails AS y join journalvoucherheader jv
on y.jvno=jv.jvno
WHERE accountid=@accountid

thanks

[MAK]

Cool. One suggestion. Try to optimize this if you get a chance.

[akhafaji]

Now it like this :


CREATE proc [dbo].[tst]
@accountid int as
SELECT transactionno,y.JVNO,jvdate,accountid,
-------------------------------------------------

(case when
exists(select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0
having count(N.credit)=1)
then
(select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0 )

-------------------------------------------------

ELSE CASE WHEN
exists(Select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit=0
having count(N.debit)=1)
then
(Select accountname FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno)
-------------------------------------------------

else case when
exists(select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit<>0
AND N.credit<>0 having count(N.credit)=1)
then
((SELECT N.ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND N.DEBIT<>0))

-------------------------------------------------

else case when
exists(select count(N.credit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit<>0
GROUP BY N.credit having count(N.credit)>1)
then
' Mentioned '

-------------------------------------------------

else case when
exists(select COUNT(N.debit) FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno
GROUP BY N.debit having count(N.debit)>1 )
then
' Mentioned '

-------------------------------------------------

else case when
exists(select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.credit=0
AND N.credit=0 having count(N.DEBIT)=1)
then
((SELECT N.ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND N.DEBIT=0 ))

-------------------------------------------------

else case when
exists(select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND n.debit<>0
having count(N.credit)>1)
then
' Mentioned '

-------------------------------------------------

ELSE CASE WHEN
exists(select * FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit=0
having count(N.debit)>1)
then
(select ACCOUNTNAME FROM VTRANSACTIONSDETAILS AS N
WHERE Y.JVNO = N.JVNO and N.accountid<>@accountid
and Y.transactionno<>N.transactionno AND Y.debit<>0)

-------------------------------------------------

END END END END END END END END ) as xxx
,Debit,Credit,
(SELECT Sum(debit-credit)
FROM TRANSACTIONsDETails AS x
WHERE y.JVNO >= x.JVNO and accountid=@accountid) AS
Balance
FROM vTRANSACTIONsDETails AS y join journalvoucherheader jv
on y.jvno=jv.jvno
WHERE Y.accountid=@accountid