question

keshar avatar image
keshar asked

get one month back data for each row in ms sql

t is my humble request, if some one can solve my problem. it would be greatly appreciated.

questoin: I have a table with id, date and amount. and I want to get one month prior data for the same id in another column joining same table and one month prior date also should be displayed in another column. actual data and result is also shown below.

create table my_table ( id int, a_date date, amount money )

insert into my_table values (1,'12-31-2020','100') insert into my_table values (2,'12-31-2020','110') insert into my_table values (1,'10-31-2020','120') insert into my_table values (2,'10-31-2020','130') insert into my_table values (1,'1-31-2021','140') insert into My_table values (2,'1-31-2021','150')

id a_date amount

1 2020-12-31 100.00

1 2020-11-30 120.00

1 2021-01-31 140.00

2 2021-01-31 150.00

2 2020-11-30 130.00

2 2020-12-31 110.00


answer should be like this:

id a_date amount a_date_b amount_b

1 2020-12-31 100.00 2020-11-30 120.00

1 2020-11-30 120.00 2020-10-31 0.00

1 2021-01-31 140.00 2020-12-31 100.00

2 2021-01-31 150.00 2020-12-31 110.00

2 2020-11-30 130.00 2020-10-31 0.00

2 2020-12-31 110.00 2020-11-30 130.00

Thank you in advance for the support and help.


ms sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
harbingergroup avatar image
harbingergroup answered
@keshar, Use below query to get your desired result.

select t1.id, t1.a_date, t1.amount, isnull(t2.a_date, dateadd(month,-1,t1.a_date)) b_date, isnull(t2.amount,0.00) b_amount 
from my_table t1
left join my_table t2 on t1.id = t2.id and dateadd(month,-1,t1.a_date) = t2.a_date
order by t1.id
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

keshar avatar image keshar commented ·

Thank you harbingergroup. this is exactly the result what I want. Thank you for the help. appreciate it.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.