SQL Query to pull the avg values for 1day gap dob&

问题:

i have a requirement with a below table.

conditions:-
1> i have to take the avg of salaries clints, who has 1day date of birth gap.
2> if there are no nearest 1day dob's gap between the gap between the clients, then no need to take that client into consideration.

please see the results.

Table:

ClientID    ClinetDOB's         Slaries
1           2012-03-14              300  
2           2012-04-11              400  
3           2012-05-09              200  
4           2012-06-06              400  
5           2012-07-30              600  
6           2012-08-14              1200  
7           2012-08-15              1800  
8           2012-08-17              1200  
9           2012-08-20              2400  
10          2012-08-21              1500  

Result Should looks LIKE this:-

ClientID    ClinetDOB's         AVG(Slaries)
7           2012-08-15              1500        --This avg of 1200,1800(because clientID's 6,7 have dob's have 1day gap)    
10          2012-08-20              1950        --This avg of 2400,1500(because clientID's 9,10 have dob's have 1day gap))

Please help.

Thank You In advance!

回答1:

A self-join will connect current record with all records having yesterday's date. In this context group by allows many records having the same date to be counted. t1 needs to be accounted for separately, so the Salary is added afterwards, and count(*) is incremented to calculate average.

Here is Sql Fiddle with example.

select t1.ClientID, 
       t1.ClinetDOBs,
       (t1.Slaries + sum (t2.Slaries)) / (count (*) + 1) Avg_Slaries
  from table1 t1
 inner join table1 t2
    on t1.ClinetDOBs = dateadd(day, 1, t2.ClinetDOBs)
 group by t1.ClientID, 
       t1.ClinetDOBs,
       t1.Slaries