'FOR' cycle in SQL

1

I have a query in sql which columns of the result table are the months of the year: January - December.

DECLARE @Ventas MONEY
declare @i int
declare @ano int
declare @mes int
set @ano = 2016
set @mes = 12
set @i = 1

WHILE @i <= @mes
    BEGIN
        set @Ventas = (SELECT SUM(LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT) BALANCE  
                       FROM LEDGER_ACCOUNT AS LAC, LEDGER_ACCOUNT_BALANCE AS LAB 
                       WHERE YEAR(LAB.POSTING_DATE)= @ano 
                       and MONTH(LAB.POSTING_DATE)= @i 
                       and  LAC.ACCOUNT_ID = LAB.ACCOUNT_ID 
                       AND  LAB.CURRENCY_ID = 'NIO'  
                       AND  (LAC.ACCOUNT_ID LIKE '43' + '%'))

        SELECT '43' CUENTA, 'Ventas' DESCRIPTION, @Ventas BALANCE , @Ventas/@Ventas PER
        set @i = @i + 1;
END;

He throws me twelve months, but in separate tables, I want them to join in a single row:

I made this one OTHER previously appear to me united in a single row, but I had problems with the column 'PER' (I can not mount it):

            SELECT '43' CUENTA, 'Ventas' DESCRIP ,  
             SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 1 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ENERO, 
              SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 2 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) FEBRERO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 3 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MARZO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 4 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ABRIL  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 5 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MAYO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 6 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JUNIO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 7 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JULIO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 8 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) AGOSTO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 9 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) SEPTIEMBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 10 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) OCTUBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 11 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) NOVIEMBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 12 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) DICIEMBRE  
             FROM LEDGER_ACCOUNT AS LAC, LEDGER_ACCOUNT_BALANCE AS LAB 
             WHERE  YEAR(LAB.POSTING_DATE)= @ano 
             and  LAC.ACCOUNT_ID = LAB.ACCOUNT_ID 
             AND  LAB.CURRENCY_ID = 'NIO'  
             AND  (LAC.ACCOUNT_ID LIKE '43' + '%')

I get something like this, I need the column PER (Sales / Sales) for each month of the year and we are at 100, this is just an example, there are many more PER to calculate ... Sales / Sales is just an example abnormal.

    
asked by N'oel C'alero 27.10.2016 в 17:27
source

1 answer

1

A simple way, and one that allows you to do everything in a query, is using a common table expression with the SQL you already have:

;with CTE as (
      SELECT '43' CUENTA, 'Ventas' DESCRIP ,  
             SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 1 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ENERO, 
              SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 2 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) FEBRERO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 3 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MARZO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 4 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ABRIL  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 5 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MAYO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 6 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JUNIO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 7 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JULIO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 8 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) AGOSTO  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 9 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) SEPTIEMBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 10 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) OCTUBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 11 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) NOVIEMBRE  ,  
               SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 12 THEN
             LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) DICIEMBRE  
             FROM LEDGER_ACCOUNT AS LAC, LEDGER_ACCOUNT_BALANCE AS LAB 
             WHERE  YEAR(LAB.POSTING_DATE)= @ano 
             and  LAC.ACCOUNT_ID = LAB.ACCOUNT_ID 
             AND  LAB.CURRENCY_ID = 'NIO'  
             AND  (LAC.ACCOUNT_ID LIKE '43' + '%')
)
select cuenta, descrip,
       enero, enero/enero as enero_per,
       febrero, febrero/febrero as febrero_per,
       ...
  from CTE
    
answered by 27.10.2016 / 18:03
source