How to Find the Average Value in a Column of Dates in SQL Server

How to find the average value in a column of dates in SQL Server

If you want to average date only:

SELECT CAST(AVG(CAST(datefield AS INT)) AS DATETIME) FROM dates;

And if you want to consider time:

SELECT CAST(AVG(CAST(datefield AS FLOAT)) AS DATETIME) FROM dates;

See fiddle to test.

Calculate the monthly average including the date where data is missing

Using a calendar table might be the easiest way to go here:

WITH dates AS (
    SELECT date_trunc('day', t)::date AS dt
    FROM generate_series('2020-01-01'::timestamp, '2020-12-31'::timestamp, '1 day'::interval) t
),
cte AS (
    SELECT t.dt, COALESCE(SUM(s.sales), 0) AS sales
    FROM dates t
    LEFT JOIN sales s ON t.dt = s.s_date
    GROUP BY t.dt
)

SELECT
    LEFT(dt::text, 7) AS ym,
    AVG(sales) AS avg_sales
FROM cte
GROUP BY
    LEFT(dt::text, 7);

The logic here is to first generate an intermediate table in the second CTE which has one record for each data in your data set, along with the total sales for that date. Then, we aggregate by year/month, and report the average sales.

SQL Server Query for average value over a date period

You may try with the following approach, using DATEADD(), windowed COUNT() and VALUES() table value constructor:

Table:

DECLARE @SampleOrderTable TABLE (
   pkPersonID INT,
   OrderDate DATETIME,
   Amount NUMERIC(18, 6)
)
INSERT INTO @SampleOrderTable (pkPersonID, OrderDate, Amount) 
VALUES (1, '20191210', '762.84'),
       (2, '20191210', '886.32'),
       (3, '20191109', '10245.00')

Statement:

SELECT 
   t.pkPersonID,
   DATEADD(day, -v.Day, t.OrderDate) AS [Date],
   CONVERT(numeric(18, 6), Amount / COUNT(Amount) OVER (PARTITION BY t.pkPersonID)) AS Amount
FROM @SampleOrderTable t
CROSS APPLY (VALUES (0), (1), (2), (3)) v(Day)
ORDER BY t.pkPersonID, [Date]

Result:

pkPersonID  Date                Amount
1           07/12/2019 00:00:00 190.710000
1           08/12/2019 00:00:00 190.710000
1           09/12/2019 00:00:00 190.710000
1           10/12/2019 00:00:00 190.710000
2           07/12/2019 00:00:00 221.580000
2           08/12/2019 00:00:00 221.580000
2           09/12/2019 00:00:00 221.580000
2           10/12/2019 00:00:00 221.580000
3           06/11/2019 00:00:00 2561.250000
3           07/11/2019 00:00:00 2561.250000
3           08/11/2019 00:00:00 2561.250000
3           09/11/2019 00:00:00 2561.250000

Calculate average in SQL based on condition for end date and number of records

I came to a solution which seems to be working:

SELECT Id,
AVG(Value) OVER(partition by ID Order by [date] rows between 2 preceding and current row) as avg_value,
FROM (
      SELECT *, 
             Max([Date]) OVER(partition by Id, Code) as MaxDate, 
             COUNT(*) OVER(partition by Id, Code) as RecordsPerCode
      FROM [table]
      WHERE Code = 'SLS'
  ) a
  WHERE 1=1 
  AND YEAR(MaxDate) = YEAR(DATEADD(year, -1, GETDATE()))
  AND RecordsPerCode >= 3 
  

Any suggestions or advice will be appreciated.

Calculate average value per day based on single date column

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;

---

Related Topics