I have a sql query in which the 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;
It throws me the twelve months, but in separate tables, I want them to be joined in a single row:
I did this ANOTHER one before they appear joined in a single row, but I had problems with the 'PER' column (I can't 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 abnormal example.
An easy way, and one that allows you to do it all in one query, is to use a common table expression with the SQL you already have: