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.