I'm having trouble adjusting an SQL query where I need to take two rows that match similar criteria and give them each a column. Here is an example of the table I am querying:

And the result I would like:

I am trying query and return both the datetimes of picking slip creation and order delivery in two separate columns on the same row for each pickingslipno between a certain date range. I've tried the following code which worked perfectly when I only query one PickslipNo. However, as soon as I try querying a wider range I get an error that my subqueries are returning multiple results.

SELECT (
    SELECT DATETIME
    FROM SALESORDHIST
    WHERE SUBJECT LIKE 'Picking Slip Created'
    AND DATETIME >= GETDATE()-5
    AND DATETIME <= GETDATE()
) AS PickingDate,
(
    SELECT DATETIME
    FROM SALESORDHIST
    WHERE SUBJECT LIKE 'Order delivered'
    AND DATETIME >= GETDATE()-5
    AND DATETIME <= GETDATE()
) AS DeliveryDate
FROM SALESORDHIST 

Apologies if this doesn't make sense and I appreciate you taking the time to read this/help out.

CodePudding user response：

First select the data for the order slip create and then left join to the delivery -- like this:

SELECT SLIP.PICKSLIPNO , SLIP.DATETIME AS PickingDate, DEL.DATETIME as DeliveryDate
FROM SALESORDHIST SLIP
LEFT JOIN SALESORDHIST DEL ON SLIP.PICKSLIPNO = DEL.PICKSLIPNO 
                           AND DEL.SUBJECT LIKE 'Order delivered'
WHERE SLIP.SUBJECT LIKE 'Picking Slip Created'