CODEWITHSUNDEEP
sqljoinreportingself-joincognos-bi
ritsi19
ritsi19

Reputation: 11

Query to pull previous order date corresponding to a customer?

I have a schema with Customer table and Order table. A customer can place order in multiple dates. I need to have previous order_date for every order_date corresponding to a customer.

Say a customer placed 4 orders, then for newest order(4th order) - it must pull current order_date and previous order_date(3rd order). For 3rd order placed by customer, it must pull 3rd order_date as current order_date and previous order_date(2nd order) as so on.

I am using below query to get previous order_date and then joining with current_query to get result::

select customerid, orderid, order_date as previous_order_date
from (
    select c.customerid, o.orderid, o.order_date, 
        row_number() over (partition by c.customerid, o.orderid
                           order by o.order_date) rown
    from customers c join orders o on c.customerid = o.customerid
       ) a
where rown = 2

But the issue is, I am getting a single date corresponding to a customerid whereas the requirement is - just previous order_date corresponding to current order_date for a customer.

Any suggestion would help! Thanks

Upvotes: 1

Views: 2586

Answers (2)

ritsi19
ritsi19

Reputation: 11

Unfortunately, LAG() didn't work when used in SQL node for reporting purpose. I tried using below query and got desired result:

SELECT   c.customer_code, o.customer_sid, o.order_id, o.order_no,
         o.order_created_date,
         (SELECT MAX (o1.order_created_date)
            FROM d_customer c1 LEFT JOIN f_order o1
                 ON c1.customer_sid =
                                   o1.customer_sid
           WHERE c1.customer_sid = c.customer_sid
             AND o1.order_created_date < o.order_created_date
             AND EXISTS (SELECT 1
                           FROM f_invoice i
                          WHERE i.order_id = o1.order_id))
                                                   AS prev_order_created_date,
         t.financial_year, t.financial_month_no
    FROM d_customer c JOIN f_order o
         ON c.customer_sid = o.customer_sid
       AND c.customer_type = 'PATIENT'
       AND c.customer_country = 'UNITED STATES'
       AND o.customer_type = 'PATIENT'
       AND o.bill_to_country = 'UNITED STATES'
       AND o.order_status = 'SHIPPED'
       AND o.order_type = 'SALES'
       AND o.order_group = 'REVENUE'
     --  AND c.customer_code = '233379PT'
         LEFT JOIN d_time t ON t.time_sid = o.order_created_date_sid
ORDER BY order_created_date DESC

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Try with LAG() window function per customerid:

select 
  c.customerid, o.orderid, o.order_date, 
  lag(o.order_date) over (partition by c.customerid order by o.order_date) AS prev_order_date
from customers c
join orders o on c.customerid = o.customerid

For the earliest order of every customer prev_order_date will be null.

Sample result (don't mind orderid, it's just for the example):

 customerid | orderid | order_date | prev_order_date
------------+---------+------------+-----------------
          1 |       6 | 2015-02-08 |
          1 |       2 | 2016-02-05 | 2015-02-08
          1 |       3 | 2016-02-08 | 2016-02-05
          1 |       1 | 2016-03-05 | 2016-02-08
          2 |       5 | 2016-07-01 |
          2 |       4 | 2016-07-08 | 2016-07-01

If one customer can place the same order within different dates (weird, but this seems to be your case) add o.orderid to the PARTITION BY clause.

Upvotes: 4

Related Questions