quote:

---

Originally posted by flamblaster

Sorry...it's 2 fields actually:

(max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) -
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600 as 'Service Hours',
&
(sum(numspacespu))/((max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) -
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600) as 'Passengers per Service Hour'

---

SELECT *
FROM
(SELECT 
B.ldate,
es.run, 
(SELECT top 1 tal.loginname
FROM trackeractionlog TAL
WHERE tal.actiondate=[date,Date]
AND tal.Trackaction='PASS/SCHEDULE'
GROUP BY tal.loginname
Order by COUNT (case when tal.trackaction='PASS/SCHEDULE' then 1 else 0 end) desc) as 'Login', 
es.tripcount, 
(max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600 as 'Service Hours', sum(numspacespu) as Passengers,
(sum(numspacespu))/((max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600) as 'Passengers per Service Hour',0 as Seq
from booking b
JOIN events e
On e.bookingid=b.bookingid
JOIN bookingactivity ba
ON ba.legid=e.legid
JOIN run es
ON es.schid=e.schid
and es.evstrid=e.evstrid

WHERE b.ldate=[date,Date]
and b.schedstatus in (1,2,3)
and es.providerid=1
and ba.passtype<>'sa'

group by b.ldate, es.run, es.tripcount

UNION ALL

SELECT 
B.ldate,
'Total', 
(SELECT top 1 tal.loginname
FROM trackeractionlog TAL
WHERE tal.actiondate=[date,Date]
AND tal.Trackaction='PASS/SCHEDULE'
GROUP BY tal.loginname
Order by COUNT (case when tal.trackaction='PASS/SCHEDULE' then 1 else 0 end) desc) as 'Login', 
es.tripcount, 
(max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600 as 'Service Hours', sum(numspacespu) as Passengers,
(sum(numspacespu))/((max(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
min(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600) as 'Passengers per Service Hour',1
from booking b
JOIN events e
On e.bookingid=b.bookingid
JOIN bookingactivity ba
ON ba.legid=e.legid
JOIN run es
ON es.schid=e.schid
and es.evstrid=e.evstrid

WHERE b.ldate=[date,Date]
and b.schedstatus in (1,2,3)
and es.providerid=1
and ba.passtype<>'sa'

group by b.ldate)t

Order by t.ldate,t.seq, t.run

quote:

---

Originally posted by flamblaster

Thanks Vis,

That returned a duplicate row for each row that the first report was returning. The only difference was that the first row now has a value of 0 for the SEQ column and the second row has a value of 1 for the SEQ column.

Thanks!
Craig

---

quote:

---

Originally posted by flamblaster

Hi Vis,

Sorry, no....I understand a lot of this stuff but I sometimes have a hard time communicating the requirements in writing. I actually just want to see
the total for the day...For example, if it were possible to group by the date and leave out the run from the group, that would be my ultimate goal...the problem I have
is that if I group by the day and leave out the run, I don't know how to tally the individual runs into one SUM...the reason for this is that the MIN() and MAX() portions of the query are evaluating the earliest time of the day period...In a perfect world, I would alias the Run table to the individual runs so that I could get each individual MIN and MAX, but I can never forsee what the run names will be or which will be used. Hope this makes sense.

---

quote:

---

Originally posted by flamblaster

Hi Vis,

What I would like is to eliminate the individual run values from the day and only show the total value for all the runs for the day.

---

SELECT 
B.ldate,
'Total', 
(SELECT top 1 tal.loginname
FROM trackeractionlog TAL
WHERE tal.actiondate=[date,Date]
AND tal.Trackaction='PASS/SCHEDULE'
GROUP BY tal.loginname
Order by COUNT (case when tal.trackaction='PASS/SCHEDULE' then 1 else 0 end) desc) as 'Login', 
es.tripcount, 
(sum(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
sum(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600 as 'Service Hours', sum(numspacespu) as Passengers,
(sum(numspacespu))/((sum(Case when e.activity=1 and e.actualarrivetime>0 then e.actualdeparttime else null end) - 
sum(case when e.activity=0 and e.actualarrivetime>0 then e.actualarrivetime else null end))*1.0/3600) as 'Passengers per Service Hour'
from booking b
JOIN events e
On e.bookingid=b.bookingid
JOIN bookingactivity ba
ON ba.legid=e.legid
JOIN run es
ON es.schid=e.schid
and es.evstrid=e.evstrid

WHERE b.ldate=[date,Date]
and b.schedstatus in (1,2,3)
and es.providerid=1
and ba.passtype<>'sa'

group by b.ldate

Order by b.ldate, es.run

quote:

---

Originally posted by flamblaster

Hi Vis...thanks for all your help...it is VERY close...I'll play with the query a bit to tweak and see where I'm going wrong...there's about a 2% error rate with the returned results, but I'm sure it's something on my end. Thanks for sticking with me...not quite sure why I didn't try this the first time...I've used this format before...just didn't click. Thanks again! Craig

---

quote:

---

Originally posted by flamblaster

Hi Vis,

Sorry to rehash this, I was going over the query and realized that you took the min and max out. I'm pretty sure that's why I'm not getting the intended results.
Here's a simplified example of what I'm trying to achieve with corresponding data:

Data Set:

b.date es.run e.activity e.arrivetime e.departtime min(e.arrive) max(e.departtime) Service Hours
5/5/2008 101 0 800 805 800 ---- -----
" 1 900 905 ---- ---- ----
0 930 935 ---- ---- ----
1 1000 1005 1005 1005-0800

102 0 1000 1020 1000 ---- ----
1 1030 1035 ---- ---- ----
0 1300 1400 ---- ---- ----
1 1500 1520 ---- 1520 1520-1000



Select B.Date,
Max(case when e.activity=1 then e.departtime else null end) -
Min(case when e.activity=0 then e.arrivetime else null end) as 'Total Service Hours'

From Booking B
JOIN Events E
ON b.bookingid=e.bookingid
JOIN RUN ES
ON es.schid=e.schid
AND es.evstrid=e.evstrid
WHERE b.date=?

Group by b.date

Without the Min and Max, I could end up with 935-930 on Run 101 and 1400-1300 on Run 102. But putting Min for all the runs and Max for all the runs doesn't give me the result I want either if grouping by es.run because it will give me the latest departtime of the day and subtract the earliest arrivetime of the day from it.

Any other thoughts?

Thanks!
Craig

---

quote:

---

Originally posted by flamblaster

Yes, that's exactly it...what I'm hoping for is to be able to see the total of the day for large date ranges without seeing all the details of each day.
Hopefully my edits to my post will make sense...

---