We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

How to get data for different months RSS

Last post Mar 02, 2020 04:49 PM by limno

• thepast

Member

10 Points

145 Posts

How to get data for different months

Mar 02, 2020 09:35 AM|thepast|LINK

Hi

I have table cold  xx   has 3 columns      amount    due_date    supp_no

I want      to get  amount   divided by months

forex sample

 supp_no amount for pervious 3 month amount for pervious 6 month amount for pervious 9 months amount old 1 (this amount starts from last previous 3 months till today) ( this amount start from 9th month   and ends where previous column started         means from 3th month to 9th month) means from 9th month to 12th month) all amounts before 1 year              )

hope that clear

• limno

All-Star

123252 Points

10024 Posts

Moderator

Re: How to get data for different months

Mar 02, 2020 04:49 PM|limno|LINK

create table Coldxx(amount int,
due_date date
, supp_no int)

INSERT INTO Coldxx values(100,'2019-10-1',1)
,(100,'2019-10-1',1)
,(100,'2019-10-1',1)
,(100,'2020-01-01',1)
,(100,'2020-02-01',1)
,(100,'2020-03-01',1)
,(100,'2019-01-01',1)
,(100,'2019-02-01',1)
,(100,'2019-03-01',1)
,(100,'2019-04-01',1)
,(100,'2019-05-01',1)
,(100,'2019-06-01',1)
,(100,'2019-07-01',1)
,(100,'2019-08-01',1)
,(100,'2019-09-01',1)

select supp_no,
Sum(Case when due_date >= dateadd(month, datediff(month,0,getdate())-3,0)
and due_date<dateadd(month, datediff(month,0,getdate()),0) then amount else null end) last3
,Sum(Case when due_date >= dateadd(month, datediff(month,0,getdate())-9,0)
and due_date<dateadd(month, datediff(month,0,getdate()),0)-3 then amount else null end) last9_3
,Sum(Case when due_date >= dateadd(month, datediff(month,0,getdate())-12,0)
and due_date<dateadd(month, datediff(month,0,getdate()),0)-9 then amount else null end) last12_9
,Sum(Case when due_date<dateadd(month, datediff(month,0,getdate()),0)-12 then amount else null end) lastbefore12
from Coldxx
group by supp_no

drop table Coldxx

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm