how to use order by creditdebit.id desc in the given query..
declare @expenseNames nvarchar(max)='';
--select @expenseNames ='[Building_EXPENSES], [eLECTRIC]'
--select @expenseNames= case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'
--from expenses
(select @expenseNames +=
+'['+Expenses_Name+']'
+','
FROM dbo.Expenses)
set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))
declare @dynamicSQL nvarchar(max)='';
select @dynamicSQL =
'select * from
(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,
Balance from dbo.CreditDebit inner join
dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID order by CreditDebit.ID asc
)src
pivot
(
sum(Expenses)
for Expenses_Name In ('+@expenseNames+')
)as pvt '
EXECUTE(@dynamicSQL)
print @dynamicSQL
end
select * from
(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,
Balance from dbo.CreditDebit inner join
dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID
inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID
)src
pivot
(
sum(Expenses)
for Expenses_Name In ([Electric_Expenses],[Miscelleneous_Expenses],[Lift_Expenses],[Generator_Expenses])
)as pvt
sourabh choubeyPosted Jul 19, 2016, 2:55 AM
Vinay SinghPosted Jul 19, 2016, 2:34 AM