SQLTeam.com | Weblogs | Forums

Sql query to track parcels

Other SQL Server Topics
#1

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

#2

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
#3

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

#4

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
#5

So the max query misses all events after the said date, the same parcel may be sold prior and we end up with a minus number.

Is it possible to pull all first events into a temp table and use this date to search for the next event after the original date?

Any help appreciated

Kind regards