Member Avatar for gedas

hey,

i ma creating a join of the same table the output i want is something like this:


|Ticket Ref Number|Ticked Opened On|Ticket Closed On|Time Spent In Minutes|TotalAverage|
|0000712 |2011-09-14 |2011-10-18 |20023 | 12hours|
|0000200 |2011-06-14 |2011-10-08 |40023 | (null)|
|0000001 |2010-06-14 |2011-10-02 |20023 | (null)|
|0000099 |2010-07-13 |2011-10-01 |10022 | (null)|

what i get is this :
|Ticket Ref Number|Ticked Opened On|Ticket Closed On|Time Spent In Minutes|TotalAverage|
|0000712 |2011-09-14 |2011-10-18 |20023 | (null)|
|0000200 |2011-06-14 |2011-10-08 |40023 | (null)|
|0000001 |2010-06-14 |2011-10-02 |20023 | 12hours|
|0000099 |2010-07-13 |2011-10-01 |10022 | (null)|


the problem with my output is that the total average time is presented where the ticket ref id is the lowest but what i need is just simply be presented in the top or in every field.

this is the quey i have in order to obtain these results

select
inc.field_11413 As TicketRefNumber,
inc.field_11425 As 'Ticket Opened on',
inc.field_11426 As 'Ticket Closed on',
TIMESTAMPDIFF(MINUTE, inc.field_11425,inc.field_11426) As 'Time Spent in Minutes',
avr.avrage

from 
tbl_bpm_data_objects_11411 as inc
Left Join 
(
	select 
		field_11426,
		AVG(TIMESTAMPDIFF(MINUTE, field_11425,field_11426))/60  as avrage
	from tbl_bpm_data_objects_11411 
	
) as avr
on avr.field_11426 = inc.field_11426





order by inc.field_11426 DESC

any help will be appreciated

Edited by gedas because: n/a
  • 2 Contributors
  • 1 Reply
  • 177 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Smeagel13
Member Avatar for Smeagel13

This sounds like you should be computing this outside the database, it's not being stored in the database ... or does it have to be in there?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.