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.
LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Hi  ste5an,

Thank for the code.

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

Could it help by create view ?

Thank you.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ste5anSenior DeveloperCommented:
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_sastraAuthor 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 DeveloperCommented:
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_sastraAuthor Commented:
Hi ste5an,

Thank you very much for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.