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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply