Query Report for Cash Report
/*****************************
*******************************
* Code formatted by Kennedy
* Time: 20/08/2013 6:52:09 PM
************************************************************/
/* SELECT t1.taxdate, t2.Acctname FROM jdt1 t1
INNER JOIN oact t2 ON t1.Account=t2.AcctCode AND t2.Finanse='y' */
DECLARE @D1 AS DATETIME
DECLARE @d2 AS DATETIME
DECLARE @D3 AS NVARCHAR(100)
SET @D1 = /* t1.taxdate */ '[%0]'
SET @d2 =/* t1.taxdate */ '[%1]'
SET @D3 = /* t2.Acctname */ '[%2]'
DECLARE @PrCl dec(19 ,6)
DECLARE @debit dec(19 ,6)
DECLARE @credit dec(19 ,6)
DECLARE @account CHAR(20)
DECLARE @Pracc CHAR(20)
CREATE TABLE #TempTab
(
Account CHAR(200)
,NAME CHAR(100)
,Date DATETIME
,Opening dec(19 ,6)
,Debit dec(19 ,6)
,Credit dec(19 ,6)
,Closing dec(19 ,6)
)
INSERT INTO #TempTab
(
Account
,NAME
,Date
,Opening
,Debit
,Credit
,Closing
)
SELECT T1.[Account]
,t2.AcctName
,T1.[RefDate] Mo
,0
,SUM(T1.[Debit])De
,SUM(T1.[Credit])Cr
,0
FROM OJDT T0
INNER JOIN JDT1 T1
ON T0.TransId = T1.TransId
INNER JOIN oact t2
ON t1.Account = t2.AcctCode
AND t2.Finanse = 'y'
GROUP BY
T1.[Account]
,T1.[RefDate]
,t2.AcctName
ORDER BY
T1.[Account]
,T1.[RefDate]
DECLARE cu CURSOR
FOR
SELECT Account
,Debit
,Credit
FROM #TempTab
FOR
UPDATE
SET @PrAcc = ''
OPEN cu
FETCH NEXT FROM cu INTO @Account,@Debit,@Credit
WHILE @@FETCH_STATUS=0
BEGIN
IF @account!=@PrAcc
SET @PrCl = 0
UPDATE #TempTab
SET opening = @PrCl
,closing = @PrCl-@Credit+@Debit
WHERE CURRENT OF cu
SET @PrCl = @PrCl-@Credit+@Debit
SET @PrAcc = @Account
FETCH NEXT FROM cu INTO @Account,@debit,@credit
END
DEALLOCATE cu
SELECT c.Account AS Accountcode
,c.NAME AS AcctName
,c.Date AS [Date]
,c.Opening AS Opening
,c.Debit AS Debit
,c.Credit AS Credit
,c.Closing AS Closing
FROM #TempTab c
WHERE (date BETWEEN @D1 AND @d2)
AND NAME LIKE @D3
DROP TABLE #TempTab
Hope Helpful
