Query to fetch multiple time range data in single query

Question

I have a table named Order which have transaction related information like

• order_id
• customer_id
• net_amount
• net_discount
• order_date

I want to fetch the details of a customer over different time periods through a single line result.

ex. If I need the total amount spend and total discount availed data for lifetime by each customer then I can query

SELECT customer_id,
       sum(net_amount)   AS total_spent,
       sum(net_discount) AS total_discount
FROM   Order
GROUP  BY customer_id; 

Similarly if I need for last 10 days then I can write

SELECT   customer_id,
         SUM(net_amount)   AS total_spent,
         SUM(net_discount) AS total_discount
FROM     ORDER
GROUP BY customer_id
WHERE    order_date BETWEEN FORMAT(GETDATE(), 'yyyy-MM-dd') AND FORMAT(DATEADD(dd,-10,GETDATE()), 'yyyy-MM-dd');

In the above example I have to write 2 separate queries and get results separately.

My requirement is how can I fetch the the values in single line grouping by customer ID i.e. I should get the lifetime spend, last 10 days spend, last 30 days spend in a single row for different time ranges for each customer.

Answer 1

The below query worked for me in dedicated SQL Pool. Thanks to @martin for the original response.

SELECT customer_id,
       SUM(net_amount)   AS total_spent,
       sum(net_discount) AS total_discount,
       SUM(CASE WHEN order_date BETWEEN DATEADD(dd,-10,GETDATE()) AND GETDATE() THEN net_amount ELSE 0 END)   AS total_spent10d,
       SUM(CASE WHEN order_date BETWEEN DATEADD(dd,-10,GETDATE()) AND GETDATE() THEN net_discount ELSE 0 END) AS total_discount10d,
       SUM(CASE WHEN order_date BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE() THEN net_amount ELSE 0 END)   AS total_spent30d,
       SUM(CASE WHEN order_date BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE() THEN net_discount ELSE 0 END) AS total_discount30d
FROM   [Order]
GROUP  BY customer_id;