Calculate average with sql query

Question

I want to know the average hourly wage of each user from month March 2018. I want to calculate this with an SQL query.

I have this kind of data:

The start date indicates when the hourly wage starts and when there is a new start date that hourly wage applies etc.

The outcome should be:

2 €10,65
4 €9,90

How can I do this in SQL?

Answer 1

The below query is grouping the all the records for each userid and then calculating the average. For grouping, we have used group by clause.

Also, the MONTH function returns an integer that represents the month of the specified date. Since you are interested in March, so we are comparing this with 3.

Select UserId, avg(HourlyWage) 
from <<table>>
where month(StartDate) = 3
group by UserId

Please remember to change the <<table>> with actual table name.

Answer 2

Try below query. Creating a calendar table and then a cte to add missing data for missing dates. once we have data for missing dates, simple filter in WHERE clause and GROUPBY will work.

IF OBJECT_ID ('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

IF OBJECT_ID('tempdb..#calendar') IS NOT NULL
    DROP TABLE #calendar;

CREATE TABLE #temp
(
UserId INT,
StartDate DATE,
HourlyWage decimal(10,2)
)

DECLARE @date DATE = '20180305'
INSERT INTO #temp VALUES 
(2,'20180205', 10.3),
(2,@date, 10.3),
(2,DATEADD(DAY,1,@date), 10.3),
(2,DATEADD(DAY,2,@date), 10.3),
(2,DATEADD(DAY,3,@date), 10.3),
(2,DATEADD(DAY,4,@date), 10.3),
(2,DATEADD(DAY,5,@date), 10.3),
(2,DATEADD(DAY,6,@date), 10.3),
(2,DATEADD(DAY,7,@date), 10.3),
(2,DATEADD(DAY,8,@date), 10.3),
(2,DATEADD(DAY,9,@date), 10.3),
(2,DATEADD(DAY,10,@date), 10.3),
(2,DATEADD(DAY,11,@date), 10.3),
(2,DATEADD(DAY,12,@date), 10.3),
(2,DATEADD(DAY,13,@date), 10.3),
(2,DATEADD(DAY,14,@date), 10.3),
(2,DATEADD(DAY,15,@date), 10.3),
(2,DATEADD(DAY,16,@date), 10.3),
(2,DATEADD(DAY,17,@date), 10.3),
(2,DATEADD(DAY,18,@date), 10.3),
(2,DATEADD(DAY,19,@date), 10.3),
(2,DATEADD(DAY,20,@date), 10.3),
(2,DATEADD(DAY,21,@date), 10.3),
(2,DATEADD(DAY,22,@date), 12.5),
(2,DATEADD(DAY,23,@date), 12.5),
(2,DATEADD(DAY,24,@date), 12.5),
(2,DATEADD(DAY,25,@date), 12.5),
(2,DATEADD(DAY,26,@date), 12.5),
(4,'20170221', 9.5),
(4,'20170301', 9.7),
(4,'20180227', 9.9)

DECLARE @FromDate DATETIME ,
        @ToDate DATETIME;

SELECT @FromDate = MIN(StartDate) ,
       @ToDate = MAX(StartDate)
FROM   #Temp;


SELECT   TOP ( DATEDIFF(day, @FromDate, @ToDate) + 1 ) calendarDate = CAST(DATEADD(
                                                                                 DAY ,
                                                                                 number ,
                                                                                 @FromDate) AS DATE) 
INTO     #calendar
FROM     [master].dbo.spt_values
WHERE    [type] = N'P'
ORDER BY number;

;WITH tempCTE
AS ( SELECT  DISTINCT t.userid ,
                      cal.calendarDate
     FROM    (   SELECT   calendarDate
                 FROM     #calendar c
                 ) cal
             CROSS JOIN (   SELECT UserId ,
                                   MIN(StartDate) OVER ( PARTITION BY UserId ) AS Mindate ,
                                   MAX(StartDate) OVER ( PARTITION BY UserId ) AS Maxdate 
                            FROM   #Temp ) t
     WHERE   cal.calendarDate
     BETWEEN t.Mindate AND t.Maxdate )

         SELECT   cal.UserId ,
         AVG(x.HourlyWage ) AS Avg_Wage
FROM     tempCTE cal
         CROSS APPLY (   SELECT   TOP 1 t.HourlyWage
                         FROM     #Temp t
                         WHERE    t.UserId = cal.UserId
                                  AND t.StartDate <= cal.calendarDate
                         ORDER BY t.StartDate DESC ) AS x
WHERE CalendarDate BETWEEN '20180301' AND '20180331' --your date filter here
GROUP BY cal.UserId

Result:

+--------+-----------+
| UserId | Avg_Wage  |
+--------+-----------+
|      2 | 10.654838 |
+--------+-----------+