CODEWITHSUNDEEP
t-sql
pmh
pmh

Reputation: 312

t-sql compare trailing average with current data

I have a task to get the trailing average of the number of records received in past 10 months and the number of records in this month. For example, in the set of records below, the average of records per month for last three months is 4.33. and the number of records of in this month is 3. Hence the result expected is,

Result

declare @recordsReceived table (id int, dob date)

INSERT @recordsReceived VALUES (1,'2020-01-01')
INSERT @recordsReceived VALUES (2,'2020-01-08')
INSERT @recordsReceived VALUES (3,'2020-01-17')
INSERT @recordsReceived VALUES (5,'2020-02-03')
INSERT @recordsReceived VALUES (6,'2020-02-09')
INSERT @recordsReceived VALUES (7,'2020-02-16')
INSERT @recordsReceived VALUES (8,'2020-02-21')
INSERT @recordsReceived VALUES (9,'2020-02-28')
INSERT @recordsReceived VALUES (10,'2020-03-01')
INSERT @recordsReceived VALUES (11,'2020-03-07')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (13,'2020-03-28')
INSERT @recordsReceived VALUES (14,'2020-04-03')
INSERT @recordsReceived VALUES (15,'2020-04-09')
INSERT @recordsReceived VALUES (16,'2020-04-30')
INSERT @recordsReceived VALUES (5,'2020-02-03')
INSERT @recordsReceived VALUES (6,'2020-02-09')
INSERT @recordsReceived VALUES (7,'2020-02-16')
INSERT @recordsReceived VALUES (8,'2020-02-21')
INSERT @recordsReceived VALUES (9,'2020-02-28')
INSERT @recordsReceived VALUES (10,'2020-03-01')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (13,'2020-03-28')
INSERT @recordsReceived VALUES (14,'2020-04-03')
INSERT @recordsReceived VALUES (15,'2020-04-09')
INSERT @recordsReceived VALUES (16,'2020-04-30')

Upvotes: 2

Views: 34

Answers (1)

jagannath sahoo
jagannath sahoo

Reputation: 36

You can try this,

Select * From (
  Select count(month(dob))*1.00/count(distinct(month(dob))) As [TAVG] 
  from @recordsReceived 
  Where month(dob)  < (Select max(month(dob)) from @recordsReceived)) A

 ,
 (Select count(month(dob)) [Current]
 from @recordsReceived 
 Where month(dob)  = (Select max(month(dob)) from @recordsReceived)) B

Upvotes: 2

Related Questions