How to automise(dynamic) the following given report

  • 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!

  • 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 ?

  • 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