CODEWITHSUNDEEP
sqloracle-database
Sumit Jain
Sumit Jain

Reputation: 377

sql difference of time between two status

I have two status of a package i.e. Dropped and Intransit, and both status are captured in different rows in database, so for Ex.

City1 Pkg1    Status    Change_Date
ABC   1234    Dropped   07-DEC-16 02.52.28.000000 PM
ABC   1234   Intransit  07-DEC-16 05.52.28.000000 PM

So, I need at City level what is the average of intransit minus Dropped timing for all package. In this example for Pkg 1 the difference is 3 hours, similarly want to get at a city level that 1000 packages have a difference of average 2.8 hours.

Upvotes: 0

Views: 133

Answers (3)

Piou
Piou

Reputation: 1066

You will need to nest requests and you will have poor performance on large amount of packages I think. It would have been easier to have all this info in one row like that:

CityID PkgID Status Intransit Dropped

With your data model I think one correct SQL syntax would be something like (not tested):

Select avg(time), city
From (
    Select (b.dropped - c.transit) as time, a.city as city, a.packageId 
    From myTable a, 
         (Select d.Change_Date as dropped
        From myTable d
        where a.packageId = d.packageId and d.Status = 'Dropped') b,
         (Select e.Change_Date as transit
        From myTable e
        where a.packageId = e.packageId and d.Status = 'Intransit') c
)
Group By city

Upvotes: -1

Anand thakkar
Anand thakkar

Reputation: 489

What you should actually do is first Try to create the View which will make this single Table Entity to list the Package in single row.

Considering table structure be something like this 

CREATE TABLE `packageList` (
  `cityId` int(10) NOT NULL,
  `packageId` int(10) NOT NULL,
  `status` varchar(256) NOT NULL,
  `changedate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

so your view shoult be created based on following Query 


    CREATE VIEW packageStatus AS 
    SELECT 
    pl1.cityId as cityId ,
    pl1.packageId as packageId ,
    pl1.changeDate as DropTIme,
    pl2.changeDate as Picktime 
    FROM 
    `packageList` pl1 , `packageList` pl2 
    WHERE 
    pl1.status = "Droped" AND pl2.status = "Picked" 
    and pl1.packageId = pl2.packageId

Afterwards it is going to be the simple query of Group by on that view only some thing like below query 


SELECT 
   tt.cityid,avg(DATEDIFF(tt.DropTIme,tt.Picktime))  
FROM 
   (SELECT 
    pl1.cityId as cityId ,
    pl1.packageId as packageId ,
    pl1.changeDate as DropTIme,
    pl2.changeDate as Picktime 
    FROM 
    `packageList` pl1 , `packageList` pl2 
    WHERE 
    pl1.status = "Droped" AND pl2.status = "Picked" 
    and pl1.packageId = pl2.packageId) as tmpTable tt Group BY cityId

Answer Using only Query with out creating view will be soome thing like as follows 

SELECT 
   cityid,avg(DATEDIFF(DropTIme,Picktime))

FROM 
   (SELECT 
    pl1.cityId as cityId ,
    pl1.packageId as packageId ,
    pl1.changeDate as DropTIme,
    pl2.changeDate as Picktime 
    FROM 
    packageList pl1 , packageList pl2 
    WHERE 
    pl1.status = "Droped" AND pl2.status = "Picked" 
    and pl1.packageId = pl2.packageId) as tmpTable 
    Group BY cityId

Upvotes: 2

user330315
user330315

Reputation:

Just subtract the change_date from the previous change_date:

select city, Pkg, status, Change_Date, 
       change_date - 
           lag(change_date, 1, change_date) over (partition by pkg order by case status when 'Dropped' then 1 else 2 end ) as diff
where status in ('Dropped', 'Intransit')
from the_table;

The result of subtracting one date from another is a number representing the (fractional) days between those two values. So 8 hours would result in 0.33

The order by case status when 'Dropped' then 1 else 2 end sorts the rows with status Dropped before those with a different status (assuming there are only two values)

You can wrap the above into a derived table to get the average duration from that:

select avg(diff)
from (
  select city, Pkg, status, Change_Date, 
         change_date - 
             lag(change_date, 1, change_date) over (partition by pkg order by case status when 'Dropped' then 1 else 2 end ) as diff
  where status in ('Dropped', 'Intransit')
  from the_table;
) t;

Upvotes: 0

Related Questions