asked on

Simplified SQL Script

Hi All,

I have below script.

DECLARE @Year CHAR(4) = 2018
DECLARE @Month INT = 10

DECLARE @FileLedger VARCHAR(100) = 'THLEDGER' + @Year
DECLARE @MonthFrom INT = 1
DECLARE @MonthTo INT = 1

DECLARE @SQL VARCHAR(MAX) = ''

SET @SQL = 'SELECT'
SET @SQL = @SQL + ' A.AccNo' 
SET @SQL = @SQL + ', CAST(0 AS INTEGER) AS NoSeq'

WHILE @MonthTo <= @Month

BEGIN
  
  IF @MonthTo = 1 
  BEGIN
     SET @SQL = @SQL + ', SUM(SaldoAwalDbt) + SUM('
  END
  
   IF @MonthTo < @Month
   BEGIN
    SET @SQL = @SQL + 'Debet' + FORMAT(@MonthTo, '00') + ' + '
   END
   ELSE
   BEGIN
       SET @SQL = @SQL + 'Debet' + FORMAT(@MonthTo, '00')  
   END

  SET @MonthTo = @MonthTo + 1
END

SET @SQL = @SQL + ') AS SaldoAwalDbt'

SET @SQL = @SQL + ' FROM ' + @FileLedger + ' AS A'

SET @SQL = @SQL + ' INNER JOIN TMGLACCNO AS B'
SET @SQL = @SQL + ' ON A.AccNo = B.AccNo'

SET @SQL = @SQL + ' INNER JOIN TMGLTYPE AS C'
SET @SQL = @SQL + ' ON B.GLTypeCode = C.GLTypeCode'

SET @SQL = @SQL + ' WHERE B.Global <> ''Y'''

SET @SQL = @SQL + ' GROUP BY A.AccNo'

PRINT (@SQL)

Open in new window

Is that any other better way ?

Thank you.

Raja Jegan R

Since the Column names and Table names are dynamically selected, this seems to be fine..
If you want to simplify, then Line#39-47 can be converted to a single SET statement as there are no variables involved..

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi ste5an,

Thank for the code.

- But I would normalize the model instead.
To what?

Could it help by create view ?

Thank you.

ste5an

Your ledger table should look normalized like ( year, month, debit ). Thus the query would be:

DECLARE @Year CHAR(4) = 2018;
DECLARE @MonthFrom INT = 1;
DECLARE @MonthTo INT = 10;

WITH Summed
AS ( SELECT   A.AccNo ,
              SUM(A.Debt) AS SumSaldoAwalDbt
     FROM     THLEDGER A
     WHERE    A.YearNumber = @Year
              AND A.MonthNumber
              BETWEEN @MonthFrom AND @MonthTo
     GROUP BY A.AccNo )
SELECT A.AccNo ,
       CAST(0 AS INT) AS NoSeq ,
       B.SaldoAwalDbt + A.SumSaldoAwalDbt
FROM   Summed A
       INNER JOIN TMGLACCNO B ON A.AccNo = B.AccNo
       INNER JOIN TMGLTYPE C ON B.GLTypeCode = C.GLTypeCode
WHERE  B.Global <> 'Y';

Open in new window

emi_sastra

ASKER

- Your ledger table should look normalized like ( year, month, debit ). Thus the query would be:
Yes, it should be.

Thus using view could help ?

Thank you.

ste5an

Thus using view could help ?

Only if you materialize it. While this logical equivalent to a normalize table, it means that you need to store redundant/duplicated data.

So in overall: No. Cause the flaw of the non-normalized table would be still existent. btw, this is normally called a technical debt.

emi_sastra

ASKER

Hi ste5an,

Thank you very much for your help.