September 22, 2014 at 12:26 am
Hi,
I had a table #DueAmount ,
which has detail date wise amount.
For eg
create table #DueAmount
(
Duedate datetime,
DueAmount Int
)
Insert into #DueAmount
values ('2014-04-30',1000),
('2014-04-05',500),
('2014-05-30',1000),
('2014-05-7',500),
('2014-06-30',1000),
('2014-06-7',500),
('2014-07-30',1000),
('2014-07-7',500),
('2014-08-30',1000),
('2014-08-7',500)
and from this table, I generated report summary using following query,
declare @Asondate as datetime = '2014-04-30'
select convert(varchar(6),Duedate ,112) as monthflag ,
SUM(case when convert(varchar(6),Duedate ,112) = convert(varchar(6),@Asondate,112)
then
DueAmount
else 0
end) as prin_int_due,
SUM(case when convert(varchar(6),Duedate ,112) = convert(varchar(6),dateadd(mm,1,@Asondate) ,112)
then
DueAmount
else 0
end) as prin_int_due_1,
SUM(case when convert(varchar(6),Duedate ,112) = convert(varchar(6),dateadd(mm,2,@Asondate) ,112)
then
DueAmount
else 0
end) as prin_int_due_2,
SUM(case when convert(varchar(6),Duedate ,112) = convert(varchar(6),dateadd(mm,3,@Asondate) ,112)
then
DueAmount
else 0
end) as prin_int_due_3
From #DueAmount
group by convert(varchar(6),Duedate ,112)
but the problem is whenever the new month is added,I had to manualy
change the code for next month
for example in below code ,if month is added,I had to insert code
as
sum(case when convert(varchar(6),Duedate ,112) = convert(varchar(6),dateadd(mm,4,@Asondate) ,112)
then
DueAmount
else 0
end) as prin_int_due_4
in the query.
My requirement is, I want to add this month dynamically.
Please suggest me how to make this dynamically or
also suggest how the same thing can be done in SSRS.
Thanks in Advance!
September 22, 2014 at 12:52 am
Use dynamic SQL. No static query can produce varying number of columns .
Please provide more details on your requiements to get more assistance. What is the proc input? Is it a list of months or a number of months or an interval of months to be reported ?
September 22, 2014 at 2:59 am
Hi,
Actualy in #DueAmount table,
whenever the due for next month is added a new column with next month should be added.
there is now input to proc.
can we do it in SSSR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply