Simplified SQL Script

emi_sastra
emi_sastra used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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..
Senior Developer
Commented:
Sure, e.g.

DECLARE @Year CHAR(4) = 2018;
DECLARE @MonthFrom INT = 1;
DECLARE @MonthTo INT = 10;
DECLARE @Columns NVARCHAR(255) = STUFF((   SELECT  DISTINCT ' + ' + QUOTENAME('Debt' + FORMAT(SV.number, '00'))
                                           FROM    master.dbo.spt_values SV
                                           WHERE   SV.number
                                           BETWEEN @MonthFrom AND @MonthTo
                                           FOR XML PATH('')) ,
                                       1 ,
                                       3 ,
                                       '');
DECLARE @TableName NVARCHAR(255) = QUOTENAME('THLEDGER' + @Year);
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT   A.AccNo ,
             CAST(0 AS INT) AS NoSeq ,
             SUM(SaldoAwalDbt) + SUM(@Columns) AS SaldoAwalDbt
    FROM     @TableName A
             INNER JOIN TMGLACCNO B ON A.AccNo = B.AccNo
             INNER JOIN TMGLTYPE C ON B.GLTypeCode = C.GLTypeCode
    WHERE    B.Global <> ''Y''
    GROUP BY A.AccNo;
';

SET @Statement = REPLACE(REPLACE(@Statement, '@Columns', @Columns), '@TableName', @TableName);

PRINT @Statement;

Open in new window


But I would normalize the model instead.

Author

Commented:
Hi  ste5an,

Thank for the code.

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

Could it help by create view ?

Thank you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
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

Author

Commented:
- 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.
ste5anSenior Developer

Commented:
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.

Author

Commented:
Hi ste5an,

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial