quote:

---

Originally posted by henryparsons10

Here is a sample return set for the above query. (I use readuncommited so I do not slow down the server....thats what I have been told to do)

Another small thing...I am 100% self taught here and I may be missing the simplest of stuff.

Here is the sample return set. I have abbreviated some headings so that it will read easier.

Cust StartDate EndDate Rte Stops JobCount FirstSched LastSched FirstActual LastActual FstDiff LstDiff
XX 5/5/2008 5/5/2008 1 9 1 DateTime DateTime DateTime DateTime Minutes Minutes
XX 5/6/2008 5/6/2008 1 9 1 DateTime DateTime DateTime DateTime Minutes Minutes

Here we have the same route (1) on different days. I need to have this info return on one line with an average for the FirstDiff and LastDiff columns. The job count should then read 2 and I will divide the Minutes different (firstdiff, lastdiff) by the job count to get the average.

I will be more than happy to supple all tables and examples if someone wants to contact me via email. jtaylor@networkexpressinc.com

---

Select
Customer.Name As CustName,
min(ordermain.orderdate) AS StartDate,
max(ordermain.orderdate) As EndDate,
Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End as RteName,
min(Js2.sequence) As Stops,
count(ordermain.OrderID) As Job_Count,
min(js1.scheduleddatetime) As First_Stop_SchedTime,
min(js2.scheduleddatetime) As Last_Stop_SchedTime,
Case When Sum(DateDiff(minute, Case when js1.poddatetime IS NULL then js1.arrivedatetime Else js1.poddatetime End,js1.scheduleddatetime)+0) Between -480 and 
480 Then sum(DateDiff(minute, Case when js1.poddatetime IS NULL then js1.arrivedatetime Else js1.poddatetime End,js1.scheduleddatetime)+0) End As First_Stop_Minutes_Late_Early,
Case When Sum(DateDiff(minute, Case when js2.poddatetime IS NULL then js2.arrivedatetime Else js2.poddatetime End,js2.scheduleddatetime)+0) Between -480 and 
480 Then sum(DateDiff(minute, Case when js2.poddatetime IS NULL then js2.arrivedatetime Else js2.poddatetime End,js2.scheduleddatetime)+0) End As Last_Stop_Minutes_Late_Early

From
Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Ordermain.CustomerID=Customer.CustomerID
Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join Jobstop JS2 with (readuncommitted, INDEX=JobIDSequence) on JS2.JobID=JOb.JobID and 
JS2.Sequence=(Select Max(Sequence) From Jobstop JS3 where JS3.JobID=Job.jobID)
Left Outer Join Jobstop JS1 with (readuncommitted, INDEX=JobIDSequence) On JS1.JobID=Job.Jobid

Where
Ordermain.OrderStatus IN ('N', 'A', 'I', 'P') and
Ordermain.OrderDate Between '05/04/08' and '05/10/08' and
Customer.CustomerCode='331' and
left(job.service,3)='Rou' and
js1.sequence=1

Group By
Customer.Name,Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End

Order By
Customer.Name, Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End

quote:

---

Originally posted by henryparsons10

See now...thats what gets me. I will study this for an hour to find out the differences. Either way...thanks a million. It works great.

---

Select *,--put field names here 
DATEDIFF(mi,DATEADD(mi,First_Stop_Minutes_Late_Early,First_Stop_SchedTime),
DATEADD(mi,Last_Stop_Minutes_Late_Early,Last_Stop_SchedTime)) AS AverageRoutTime
from
(
Select
Customer.Name As CustName,
min(ordermain.orderdate) AS StartDate,
max(ordermain.orderdate) As EndDate,
Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End as RteName,
min(Js2.sequence) As Stops,
count(ordermain.OrderID) As Job_Count,
min(js1.scheduleddatetime) As First_Stop_SchedTime,
min(js2.scheduleddatetime) As Last_Stop_SchedTime,
Case When Sum(DateDiff(minute, Case when js1.poddatetime IS NULL then js1.arrivedatetime Else js1.poddatetime End,js1.scheduleddatetime)+0) Between -480 and 
480 Then sum(DateDiff(minute, Case when js1.poddatetime IS NULL then js1.arrivedatetime Else js1.poddatetime End,js1.scheduleddatetime)+0) End As 

First_Stop_Minutes_Late_Early,
Case When Sum(DateDiff(minute, Case when js2.poddatetime IS NULL then js2.arrivedatetime Else js2.poddatetime End,js2.scheduleddatetime)+0) Between -480 and 
480 Then sum(DateDiff(minute, Case when js2.poddatetime IS NULL then js2.arrivedatetime Else js2.poddatetime End,js2.scheduleddatetime)+0) End As Last_Stop_Minutes_Late_Early

From
Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Ordermain.CustomerID=Customer.CustomerID
Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join Jobstop JS2 with (readuncommitted, INDEX=JobIDSequence) on JS2.JobID=JOb.JobID and 
JS2.Sequence=(Select Max(Sequence) From Jobstop JS3 where JS3.JobID=Job.jobID)
Left Outer Join Jobstop JS1 with (readuncommitted, INDEX=JobIDSequence) On JS1.JobID=Job.Jobid

Where
Ordermain.OrderStatus IN ('N', 'A', 'I', 'P') and
Ordermain.OrderDate Between '05/04/08' and '05/10/08' and
Customer.CustomerCode='331' and
left(job.service,3)='Rou' and
js1.sequence=1

Group By
Customer.Name,Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End)t

Order By
Customer.Name, Case When Job.RouteNumber IS NULL Then Ordermain.Auth Else Job.RouteNumber End

quote:

---

Originally posted by henryparsons10

Still giving me the internal SQL Server Error.

---

quote:

---

Originally posted by henryparsons10

Everything is working great with this query so far. Except, where it returns average route length as hours and minutes, they now want to see it as a decimal. I am getting the "cannot convert varchar to numberic" with everything I try.

Here it is:

left(convert(char,CAST(AVG(Cast(Case when js2.poddatetime IS NULL then js2.arrivedatetime Else case when ABS(datediff(mi,js2.scheduleddatetime,js2.poddatetime))<480 then
js2.poddatetime ELSE js2.scheduleddatetime END End As float)) As Datetime)-CAST(AVG(Cast(Case when js1.poddatetime IS Null then js1.arrivedatetime Else case when ABS(datediff(mi,js1.scheduleddatetime,js1.poddatetime))<480 then
js1.poddatetime ELSE js1.scheduleddatetime END End As float)) As DateTime),14),5) As Avg_Route_Length


How can I get a decimal returned here? I swear, last one and its good to go.

---

quote:

---

Originally posted by henryparsons10

Example
Avg_Route_Length
7:58
7:45
2:04
6:29
6:58

It returns hours and minutes, like above. I want the decimal.

---

quote:

---

Originally posted by henryparsons10

Because our route planning department uses 7.5 for 7:30, and they want the data in the same format. Also, I have a scheduled length column that is returning the data as a decimal and I need both to be the same. Here is my scheduled length;

round(DateDiff(minute,max(js1.latedatetime),max(js2.latedatetime)+0)/.6/100,2)

---

cast(replace(left(convert(char,CAST(AVG(Cast(Case when js2.poddatetime IS NULL then js2.arrivedatetime Else case when ABS(datediff(mi,js2.scheduleddatetime,js2.poddatetime))<480 then 
js2.poddatetime ELSE js2.scheduleddatetime END End As float)) As Datetime)-CAST(AVG(Cast(Case when js1.poddatetime IS Null then js1.arrivedatetime Else case when ABS(datediff(mi,js1.scheduleddatetime,js1.poddatetime))<480 then
js1.poddatetime ELSE js1.scheduleddatetime END End As float)) As DateTime),14),5),':','.') AS numeric(10,2)) As Avg_Route_Length