SQLTeam.com | Weblogs | Forums

Sql query to track parcels

Hi,

Many thanks for taking the time to read and hopefully provide me with some help and understanding.
so my database is as below - there is alot more to it but to make it simpler to explain i will only show relevant info.

database name - parcel_tracking

ID | parcel_ID | Parcel_Status | Date
1 | 1234 | Picked | 2022/01/01
2 | 2345 | Picked | 2022/01/01
3 | 1234 | Packed | 2022/01/08
4 | 2345 | Packed | 2022/01/09
5 | 1234 | Delivered | 2022/01/09

So i need to find each parcel that meets 2 status, so lets say every parcel thats been picked and packed for this example and also the number of days between each event as below

Parcel_ID | Pick_Date | Pack_Date | Delay
1234 | 2022/01/01 | 2022/01/08 | 7
2345 | 2022/01/01 | 2022/01/09 | 8

All info is in the same table, i will also be pulling data within a date range so say the 1st of a month to the last of the month to check delivery performance.

Any help would be massively appreciated.

Thank you.

Mike

Welcome! For future posts, it's best if you post sample data in a usable format, like this:

DROP TABLE IF EXISTS #parcels
CREATE TABLE #parcels (
    ID int NOT NULL,
    parcel_ID int NOT NULL,
    Parcel_Status varchar(20) NOT NULL,
    Date date NOT NULL
    )
INSERT INTO #parcels VALUES
    (1, 1234, 'Picked', '2022/01/01'),
    (2, 2345, 'Picked', '2022/01/01'),
    (3, 1234, 'Packed', '2022/01/08'),
    (4, 2345, 'Packed', '2022/01/09'),
    (5, 1234, 'Delivered', '2022/01/09')

With that out of the way, the query you need is this:


DECLARE @end_date date
DECLARE @start_date date

SET @start_date = '20220101'
SET @end_date = '20220131'

SELECT 
    parcel_ID,
    MAX(CASE WHEN Parcel_Status = 'Picked' THEN Date END) AS [Pick Date],
    MAX(CASE WHEN Parcel_Status = 'Packed' THEN Date END) AS [Pack Date],
    DATEDIFF(DAY, MAX(CASE WHEN Parcel_Status = 'Picked' THEN Date END), 
        MAX(CASE WHEN Parcel_Status = 'Packed' THEN Date END)) AS Delay
FROM #parcels
WHERE
    Date >= @start_date AND 
    Date < DATEADD(DAY, 1, @end_date)
GROUP BY
    parcel_ID
--<<-- HAVING insures parcel is SELECTed only if it has *both* Picked & Packed status
HAVING  
    MAX(CASE WHEN Parcel_Status = 'Picked' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Parcel_Status = 'Packed' THEN 1 ELSE 0 END) = 1
ORDER BY
    parcel_ID
2 Likes

My apologies for the layout, my first post anywhere like this :slight_smile:

That query is amazing and works perfectly. thank you so much!.

Is there anyway that i could pull 1 extra column which is a location column? The locations I'm interested in are the packed location if this helps.

Kind regards

Mike

You're welcome.

Sure, we can add a column matching either Picked or Packed status using a similar technique:

SELECT
...
MAX(CASE WHEN Parcel_Status = 'Packed' THEN location END) AS [Pack Location],
...

1 Like