Hi , i have a table that contains the job information and i want to calculate the avg at week level.

Below are the sample table and data scripts.

CREATE TABLE #TEMP ( JOBID INT,JOBNAME VARCHAR(10),STARTDATE DATETIME,ENDDATE DATETIME)

INSERT INTO #TEMP
SELECT 1,'JOBA','2015-08-18 13:11:09.000', '2015-08-18 13:11:48.000' UNION
SELECT 1,'JOBA','2015-08-16 13:09:19.000', '2015-08-16 13:09:53.000' UNION
SELECT 1,'JOBA','2015-08-15 03:56:46.000', '2015-08-15 03:57:20.000' UNION
SELECT 1,'JOBA','2015-08-14 03:49:39.000', '2015-08-14 03:50:20.000' UNION
SELECT 1,'JOBA','2015-08-13 03:52:56.000', '2015-08-13 03:53:46.000' UNION
SELECT 1,'JOBA','2015-08-12 03:50:43.000', '2015-08-12 03:51:33.000' UNION
SELECT 1,'JOBA','2015-08-11 03:53:52.000', '2015-08-11 03:54:37.000' UNION
SELECT 1,'JOBA','2015-08-10 03:46:51.000', '2015-08-10 03:47:31.000' UNION
SELECT 1,'JOBA','2015-08-09 03:46:29.000', '2015-08-09 03:47:09.000' UNION
SELECT 1,'JOBA','2015-08-08 03:55:41.000', '2015-08-08 03:56:21.000' UNION
SELECT 1,'JOBA','2015-08-07 03:57:27.000', '2015-08-07 03:58:16.000' UNION
SELECT 1,'JOBA','2015-08-06 03:54:00.000', '2015-08-06 03:55:21.000' UNION
SELECT 1,'JOBA','2015-08-05 03:56:23.000', '2015-08-05 03:57:12.000' UNION
SELECT 1,'JOBA','2015-08-04 03:56:39.000', '2015-08-04 03:57:39.000' UNION
SELECT 1,'JOBA','2015-08-03 03:53:50.000', '2015-08-03 03:54:24.000' UNION
SELECT 1,'JOBA','2015-08-02 03:57:13.000', '2015-08-02 03:57:45.000' UNION
SELECT 1,'JOBA','2015-08-01 03:56:23.000', '2015-08-01 03:56:57.000' UNION
SELECT 1,'JOBA','2015-07-31 04:01:54.000', '2015-07-31 04:02:37.000' UNION
SELECT 1,'JOBA','2015-07-30 03:51:20.000', '2015-07-30 03:52:03.000' UNION
SELECT 1,'JOBA','2015-07-29 03:51:46.000', '2015-07-29 03:52:46.000' UNION
SELECT 1,'JOBA','2015-07-27 03:52:05.000', '2015-07-27 03:52:50.000' UNION
SELECT 1,'JOBA','2015-07-26 03:47:11.000', '2015-07-26 03:48:03.000' UNION
SELECT 2,'JOBB','2015-07-25 03:54:37.000', '2015-07-25 03:55:27.000' UNION
SELECT 2,'JOBB','2015-07-24 03:58:07.000', '2015-07-24 03:58:56.000' UNION
SELECT 2,'JOBB','2015-07-23 03:55:54.000', '2015-07-23 03:56:43.000' UNION
SELECT 2,'JOBB','2015-07-22 03:49:28.000', '2015-07-22 03:50:30.000' UNION
SELECT 2,'JOBB','2015-07-22 02:18:31.000', '2015-07-22 02:19:35.000' UNION
SELECT 2,'JOBB','2015-07-20 03:50:37.000', '2015-07-20 03:51:26.000' UNION
SELECT 2,'JOBB','2015-07-18 03:53:23.000', '2015-07-18 03:54:14.000' UNION
SELECT 2,'JOBB','2015-07-16 13:08:47.000', '2015-07-16 13:09:31.000' UNION
SELECT 2,'JOBB','2015-07-16 05:06:40.000', '2015-07-16 05:07:18.000' UNION
SELECT 2,'JOBB','2015-07-15 03:56:59.000', '2015-07-15 03:57:53.000' UNION
SELECT 2,'JOBB','2015-07-13 03:48:47.000', '2015-07-13 03:49:48.000' UNION
SELECT 2,'JOBB','2015-07-12 03:49:04.000', '2015-07-12 03:49:49.000' UNION
SELECT 2,'JOBB','2015-07-11 03:49:15.000', '2015-07-11 03:50:14.000' UNION
SELECT 2,'JOBB','2015-07-10 03:54:39.000', '2015-07-10 03:55:27.000' UNION
SELECT 2,'JOBB','2015-07-09 03:50:14.000', '2015-07-09 03:50:53.000' UNION
SELECT 2,'JOBB','2015-07-07 13:05:56.000', '2015-07-07 13:06:36.000'


select * from #TEMP

I want the out put as below

JobId   JobName  Last5DaysAvg   Last10DaysAvg  

1             A                 X                    X

2             B                 X                    X

When i calculate Last5DaysAvg , i want to consider only last 5 days duration for the Avg.

Same for 10 days.

Hi SSDL,

Try this

select avg(YourAvgColumn) from (select TOP 5 YourAvgColumn from #TEMP order by STARTDATE desc) as a

Your DDL was just a little off. Why do you have both a job_id and a job_name? Those two should be in their own table. I wlll keep the job-name as the identifer, since your job_id was a numeric. What math do you do with that numeric value? NONE!

CREATE TABLE Jobs 
(job_name VARCHAR(10) NOT NULL, 
 job_start_date DATETIME2(0) NOT NULL,
 PRIMARY KEY (job_name, job_start_date),
 job_end_date DATETIME2(0) NOT NULL,
 CHECK (job_start_date < job_end_date))

Here is the ANSI/ISO Standard insertion syntax. We do not use the old  Sybase stuff today. Please look at the DATETIME2(n) data type, too. It is replacing the old Sybase DATETIME

INSERT INTO Jobs
VALUES
('JOBA', '2015-08-18 13:11:09', '2015-08-18 13:11:48'),
('JOBA', '2015-08-16 13:09:19', '2015-08-16 13:09:53'),
('JOBA', '2015-08-15 03:56:46', '2015-08-15 03:57:20'),
('JOBA', '2015-08-14 03:49:39', '2015-08-14 03:50:20'),
('JOBA', '2015-08-13 03:52:56', '2015-08-13 03:53:46'),
('JOBA', '2015-08-12 03:50:43', '2015-08-12 03:51:33'),
('JOBA', '2015-08-11 03:53:52', '2015-08-11 03:54:37'),
('JOBA', '2015-08-10 03:46:51', '2015-08-10 03:47:31'),
('JOBA', '2015-08-09 03:46:29', '2015-08-09 03:47:09'),
('JOBA', '2015-08-08 03:55:41', '2015-08-08 03:56:21'),
('JOBA', '2015-08-07 03:57:27', '2015-08-07 03:58:16'),
('JOBA', '2015-08-06 03:54:00', '2015-08-06 03:55:21'),
('JOBA', '2015-08-05 03:56:23', '2015-08-05 03:57:12'),
('JOBA', '2015-08-04 03:56:39', '2015-08-04 03:57:39'),
('JOBA', '2015-08-03 03:53:50', '2015-08-03 03:54:24'),
('JOBA', '2015-08-02 03:57:13', '2015-08-02 03:57:45'),
('JOBA', '2015-08-01 03:56:23', '2015-08-01 03:56:57'),
('JOBA', '2015-07-31 04:01:54', '2015-07-31 04:02:37'),
('JOBA', '2015-07-30 03:51:20', '2015-07-30 03:52:03'),
('JOBA', '2015-07-29 03:51:46', '2015-07-29 03:52:46'),
('JOBA', '2015-07-27 03:52:05', '2015-07-27 03:52:50'),
('JOBA', '2015-07-26 03:47:11', '2015-07-26 03:48:03'),
('JOBB', '2015-07-25 03:54:37', '2015-07-25 03:55:27'),
('JOBB', '2015-07-24 03:58:07', '2015-07-24 03:58:56'),
('JOBB', '2015-07-23 03:55:54', '2015-07-23 03:56:43'),
('JOBB', '2015-07-22 03:49:28', '2015-07-22 03:50:30'),
('JOBB', '2015-07-22 02:18:31', '2015-07-22 02:19:35'),
('JOBB', '2015-07-20 03:50:37', '2015-07-20 03:51:26'),
('JOBB', '2015-07-18 03:53:23', '2015-07-18 03:54:14'),
('JOBB', '2015-07-16 13:08:47', '2015-07-16 13:09:31'),
('JOBB', '2015-07-16 05:06:40', '2015-07-16 05:07:18'),
('JOBB', '2015-07-15 03:56:59', '2015-07-15 03:57:53'),
('JOBB', '2015-07-13 03:48:47', '2015-07-13 03:49:48'),
('JOBB', '2015-07-12 03:49:04', '2015-07-12 03:49:49'),
('JOBB', '2015-07-11 03:49:15', '2015-07-11 03:50:14'),
('JOBB', '2015-07-10 03:54:39', '2015-07-10 03:55:27'),
('JOBB', '2015-07-09 03:50:14', '2015-07-09 03:50:53'),
('JOBB', '2015-07-07 13:05:56', '2015-07-07 13:06:36');


>> When I calculate last_5days_ avg, I want to consider only last 5 days duration for the average. <<

Average of what? Job duration? You never said! 

WITH X
 AS
(SELECT job_name, job_start_date, job_end_date,
       DATEIFF(seconds, job_start_date, job_end_date) 
       AS job_duration
  FROM Jobs
 WHERE job_start_date BETWEEN CURRENT_TIMESTAMP 
                      AND (DATEADD(DAY, -5, CURRENT_TIMESTAMP)
    OR job_end_date BETWEEN CURRENT_TIMESTAMP
                      AND (DATEADE(DAY, -5, CURRENT_TIMESTAMP)
SELECT job_name, AVG(job_duration) AS last_5_days_ avg
  FROM X 
 GROUP BY job_name;

You need to reverse your BETWEEN statements otherwise it will produce 0 rows.

Thanks Celko. If i want to add Last_10_days_avg as well to this output, can i do this in same query ?? or i need another CTE for this ?

Hi SSDL,

You have to write one more CTE and then join the two CTE's based on  job_name. It looks something like this

WITH X
 AS
(SELECT job_name, job_start_date, job_end_date,
       DATEIFF(seconds, job_start_date, job_end_date) 
       AS job_duration
  FROM Jobs
 WHERE job_start_date BETWEEN CURRENT_TIMESTAMP 
                      AND (DATEADD(DAY, -5, CURRENT_TIMESTAMP)
    OR job_end_date BETWEEN CURRENT_TIMESTAMP
                      AND (DATEADE(DAY, -5, CURRENT_TIMESTAMP),
  Y
 AS
(SELECT job_name, job_start_date, job_end_date,
       DATEIFF(seconds, job_start_date, job_end_date) 
       AS job_duration
  FROM Jobs
 WHERE job_start_date BETWEEN CURRENT_TIMESTAMP 
                      AND (DATEADD(DAY, -10, CURRENT_TIMESTAMP)
    OR job_end_date BETWEEN CURRENT_TIMESTAMP
                      AND (DATEADE(DAY, -10, CURRENT_TIMESTAMP)
SELECT X.job_name, AVG(X.job_duration) AS last_5_days_avg,AVG(Y.job_duration) AS last_10_days_avg
  FROM X   INNER JOIN Y ON X.job_name = Y.job_name
 GROUP BY X.job_name;

Thanks Milan.