Query Assistance - Average Days Between Services

Hi,
I need some help writing a two queries to determine the average number of
days between services for 1. a specific machineid 2. for all specific
machineids.
The table contains many columns including a MachineID column (INT) and a
ServiceDate column (DATETIME) so sample data (excluding other columns) would
look like:
MachineID ServiceDate
123 2005-01-14 00:00:00
123 2005-02-10 00:00:00
123 2005-03-14 00:00:00
124 2005-02-18 00:00:00
123 2005-05-14 00:00:00
124 2005-03-14 00:00:00
124 2005-05-14 00:00:00
The is no IDENTITY column on the table.
So the resultsets would resemble:
1. For a specific machineid
MachineID Average Days Between Services
123 40
2. For all machineids
MachineID Average Days Between Services
123 40
124 42.5
It seems simple but I'm struggling with this one!
Please let me know if you need additional information.
Thanks
JerryJerry,
I think this will do what you want. If you want all MachineID values
listed, even if there is only one ServiceDate, it would help to have a
table of MachineID values, which you can LEFT JOIN so you get
them to appear with NULL average if they appear fewer than twice
in the service table.
If you want the dates to be interpreted correctly in all locales, add
the T between the date and time. The format you are using is not
independent of language and dateformat setting.
Steve Kass
Drew University
set nocount on
go
create table T (
MachineID int,
ServiceDate datetime
)
insert into T values (123,'2005-01-14T00:00:00')
insert into T values (123,'2005-02-10T00:00:00')
insert into T values (123,'2005-03-14T00:00:00')
insert into T values (124,'2005-02-18T00:00:00')
insert into T values (123,'2005-05-14T00:00:00')
insert into T values (124,'2005-03-14T00:00:00')
insert into T values (124,'2005-05-14T00:00:00')
go
select
MachineID, avg(Gap) as AvgGap
from (
select
T1.MachineID,
1.0*datediff(day,T1.ServiceDate,min(T2.ServiceDate)) as Gap
from T as T1
join T as T2
on T2.MachineID = T1.MachineID
where T2.MachineID = T1.MachineID
and T2.ServiceDate > T1.ServiceDate
group by T1.MachineID, T1.ServiceDate
) T
group by MachineID
go
drop table T
Jerry Spivey wrote:

>Hi,
>I need some help writing a two queries to determine the average number of
>days between services for 1. a specific machineid 2. for all specific
>machineids.
>The table contains many columns including a MachineID column (INT) and a
>ServiceDate column (DATETIME) so sample data (excluding other columns) woul
d
>look like:
>MachineID ServiceDate
>123 2005-01-14 00:00:00
>123 2005-02-10 00:00:00
>123 2005-03-14 00:00:00
>124 2005-02-18 00:00:00
>123 2005-05-14 00:00:00
>124 2005-03-14 00:00:00
>124 2005-05-14 00:00:00
>The is no IDENTITY column on the table.
>So the resultsets would resemble:
>1. For a specific machineid
>MachineID Average Days Between Services
>123 40
>2. For all machineids
>MachineID Average Days Between Services
>123 40
>124 42.5
>It seems simple but I'm struggling with this one!
>Please let me know if you need additional information.
>Thanks
>Jerry
>
>
>
>
>
>|||Try,
use northwind
go
create table t1 (
MachineID int not null,
ServiceDate datetime not null,
constraint pk_t1 primary key (MachineID, ServiceDate)
)
go
insert into t1 values(123, '2005-01-14 00:00:00')
insert into t1 values(123, '2005-02-10 00:00:00')
insert into t1 values(123, '2005-03-14 00:00:00')
insert into t1 values(124, '2005-02-18 00:00:00')
insert into t1 values(123, '2005-05-14 00:00:00')
insert into t1 values(124, '2005-03-14 00:00:00')
insert into t1 values(124, '2005-05-14 00:00:00')
go
create view v1
as
select
a.MachineID,
a.ServiceDate,
datediff(day, b.ServiceDate, a.ServiceDate) * 1.0 as days_since_last_serv
from
t1 as a
inner join
t1 as b
on a.MachineID = b.MachineID
and b.ServiceDate = (select max(c.ServiceDate) from t1 as c where
c.MachineID = a.MachineID and c.ServiceDate < a.ServiceDate)
where
datediff(day, b.ServiceDate, a.ServiceDate) is not null
go
select
*
from
v1
order by
MachineID,
ServiceDate
go
select
MachineID,
avg(days_since_last_serv) as [Average Days Between Services]
from
v1
group by
MachineID
order by
MachineID
go
select
MachineID,
avg(days_since_last_serv) as [Average Days Between Services]
from
v1
where
MachineID = 123
group by
MachineID
go
drop view v1
go
drop table t1
go
AMB
"Jerry Spivey" wrote:

> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns) wou
ld
> look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>
>|||SELECT
MachineID,
(datediff(d, min(ServiceDate), max(ServiceDate))/ (count(machineid)-1)) as
AverageDays FROM TABLE1
GROUP BY MachineId
ORDER BY Machineid
--
Programmer
"Jerry Spivey" wrote:

> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns) wou
ld
> look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>
>|||CREATE TABLE ServiceLog
(machine_id INTEGER NOT NULL,
service_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (service_date
= CAST(CEILING (CAST(service_date AS FLOAT)) AS DATETIME)), --drop
time
PRIMARY KEY (machine_id, service_date));
INSERT INTO ServiceLog VALUES (123, '2005-01-14 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-02-10 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-03-14 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-05-14 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-02-18 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-03-14 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-05-14 00:00:00');
SELECT machine_id,
DATEDIFF(DD, MIN(service_date), MAX(service_date))
/ (1.0 *COUNT(*)) AS avg_gap
FROM ServiceLog
GROUP BY machine_id;
This gives me:
macine_id avg_gap
===============
123 30.00
124 28.33
Which look more correct than your 40 days just by eyeballing it -- i.e.
you service things around the 15-th of each month. I did this problem
years ago and got caught in the "procedural mindset" trap like Steve
did. This where you compute each INDIVIDUAL gap between events and
then use an average function on them. Instead think of each machine as
a grouping (subset) that has properties as a whole -- duration range,
and count of events.|||Ok you three are absolutely brilliant!!!
Now just trying to figure out the logic that you used :-) May have a few
questions for you in a few.
Thanks again!!!!
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O5sExXgYFHA.3840@.tk2msftngp13.phx.gbl...
> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns)
> would look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>|||God, I get sloppy! I forgot to remove one of the days at the end of the
total duration.
SELECT machine_id,
DATEDIFF(DD, MIN(service_date), MAX(service_date))
/ (1.0 *COUNT(*) -1) AS avg_gap
FROM ServiceLog
GROUP BY machine_id;|||Sergey,
If I add only record for a machine I get a divide by zero error. How can I
fix that just in case the data contains only one record for a machineid?
Thanks again.
Jerry
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
> SELECT
> MachineID,
> (datediff(d, min(ServiceDate), max(ServiceDate))/ (count(machineid)-1))
> as
> AverageDays FROM TABLE1
> GROUP BY MachineId
> ORDER BY Machineid
> --
> Programmer
>
> "Jerry Spivey" wrote:
>|||I think I got it - added a HAVING COUNT(*) > 1 to the query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23t5HC4gYFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Sergey,
> If I add only record for a machine I get a divide by zero error. How can
> I fix that just in case the data contains only one record for a machineid?
> Thanks again.
> Jerry
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
>|||something like that, but im not sure that is the best approach
SELECT
MachineID,
(datediff(d, min(ServiceDate), max(ServiceDate))/ Case
(Count(MachineID)-1) WHEN 0 THEN 1 ELSE (Count(MachineID)-1) END) as
AverageDays FROM TABLE1
GROUP BY MachineId
ORDER BY Machineid
--
Programmer
"Jerry Spivey" wrote:

> Sergey,
> If I add only record for a machine I get a divide by zero error. How can
I
> fix that just in case the data contains only one record for a machineid?
> Thanks again.
> Jerry
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
>
>