Calculate average value per day based on single date column-CodePudding

My data is as follows:

ID  DATE_ADM                DRUG_NAME   DRUG_VALUE  
2   2010-12-01 05:00:00.00  FORMULA VI  50          
2   2010-12-01 10:30:00.00  FORMULA VI  40          
2   2010-12-02 01:00:00.00  FORMULA V   20          
1   2014-01-01 11:00:00.00  FORMULA V   40          
1   2014-01-01 23:00:00.00  FORMULA IV  80          
1   2014-01-02 11:00:00.00  FORMULA IV  80

I want to calculate the Average value per day for a similar drug name. So that I end up with a table as follows:

ID  DATE_ADM                DRUG_NAME   DRUG_VALUE  AVG_DAY_VALUE
2   2010-12-01 05:00:00.00  FORMULA VI  50          45
2   2010-12-01 10:30:00.00  FORMULA VI  40          45
2   2010-12-02 01:00:00.00  FORMULA V   20          20
1   2014-01-01 11:00:00.00  FORMULA V   40          60
1   2014-01-01 23:00:00.00  FORMULA IV  80          60
1   2014-01-02 11:00:00.00  FORMULA IV  80          80

Joel Coehoorn suggested the following to calculate the datediff, and I adapted it to something like this

datediff(day, date, coalesce(lag(date) over(partition by ID, DRUG_NAME order by id, date)))  as day_diffs



avg(drug_value) over(partition by id, drug_name, day_diffs) as AVG_DAY_VALUE

No Luck so far. Any help would be appreciated.

CodePudding user response：

Your desired results would seem to be just:

select *,
  Avg(drug_value) over(partition by id, Convert(date,date)) Avg_Day_Value
from t
order by id desc, date;