Test query

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

CREATE TABLE #customers(
customer_id INT,
name VARCHAR(100));

CREATE TABLE #orders(
order_id INT,
quantity INT,
order_date DATETIME, customer_id INT);

INSERT INTO #customers(customer_id, name) VALUES (1, 'COMPANY_A')

INSERT INTO #customers(customer_id, name) VALUES (2, 'COMPANY_B')

INSERT INTO #orders(order_id, quantity, order_date, customer_id) VALUES (1, 5, '2019-01-01', 1)

INSERT INTO #orders(order_id, quantity, order_date, customer_id) VALUES (2, 2, '2019-01-05', 1)


INSERT INTO #orders(order_id, quantity, order_date, customer_id) VALUES (3, 4, '2019-01-05', 2)

INSERT INTO #orders(order_id, quantity, order_date, customer_id) VALUES (4, 9, '2019-01-10', 2)

INSERT INTO #orders(order_id, quantity, order_date, customer_id) VALUES (5, 4, '2019-01-05', 1)


SELECT c.name, o.order_date, 
    CASE WHEN 
    LAG(o.order_date,1)OVER(PARTITION BY c.name ORDER BY o.order_date) = o.order_date
    THEN
    o.quantity + LAG(o.quantity, 1) OVER(ORDER BY o.order_date)
    ELSE
    o.quantity - LAG(o.quantity, 1)
    OVER(PARTITION BY c.name ORDER BY o.order_date)
    END as absolute_change
    FROM #customers c
    INNER JOIN #orders o
    ON c.customer_id = o.customer_id
    GROUP BY c.name, o.order_date, o.quantity

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results