Iterative Query on the Same Table

sql recursive query on self referencing table
sql query to get hierarchical tree
sql recursive query parent child
sql iterative query
recursive query in sql server without cte
recursive queries in sql w3schools
sql get all parents of a child
sql query parent child same table

I have a table which includes customer's sales data as follows;

|CustomerCode|SalesDate|Profit
|123455666   |2018-06  |120
|123455666   |2018-06  |100
|123455666   |2018-05  |10
|123455666   |2018-04  |60
|666452342   |2018-06  |900
|666452342   |2018-05  |1000
|666452342   |2018-05  |900
|666452342   |2018-06  |800

I want a table that shows company profits for 3 months period. Expected table;

|CustomerCode|P_This_Month|P_1_Month_Ago|P_2_Month_Ago
|123455666   |220         |10           |60
|666452342   |900         |1900         |800

What is the best way of doing that? At the moment I am using JOIN operator. However, even though it works with first join, doesn't work with the second join (got unexpected high values).

How can I do that?

SELECT 
  This_Month.*, 
  SUM(_1_Month_Ago.UCSALES) sales_1_month_ago,
  SUM(_2_Months_Ago.UCSALES) sales_2_months_ago
FROM 
  SalesTable This_Month
LEFT JOIN
  SalesTable _1_Month_Ago ON This_Month.CustomerCode = _1_Month_Ago AND DATE_SUB(This_Month.SalesDate, INTERVAL 1 MONTH) = _1_Month_Ago.SalesDate
LEFT JOIN
  SalesTable _2_Months_Ago ON This_Month.CustomerCode = _2_Months_Ago AND DATE_SUB(This_Month.SalesDate, INTERVAL 1 MONTH) = _2_Months_Ago.SalesDate
GROUP BY 
  This_Month.CustomerCode

You can do it with conditional aggregation.

select
CustomerCode,
sum(case when sales_date=current_date then profit else 0 end) P_This_Month,
sum(case when sales_date=date_sub(current_date, INTERVAL 1 month) then profit else 0 end) P_1_Month,
sum(case when sales_date=date_sub(current_date, INTERVAL 2 month) then profit else 0 end) P_2_Month,
from <tableName>
group by CustomerCode

Simplifying Recursive SQL Queries, Recursive Common Table Expressions can be a great tool. When the recursive CTE query runs, the first SELECT generates one or more� Enabling iterative calculations will bring up two additional inputs in the same menu: Maximum Iterations determines how many times Excel is to recalculate the workbook, Maximum Change determines the maximum difference between values of iterative formulas. Note that entering a smaller number here means more accurate results.

you can use case when and by extracting month from date

    with cte as
    (
     select EXTRACT(month FROM SalesDate) as monthofday,CustomerCode,sum(Profit) as total
     from tbale

    ) select CustomerCode,
 sum(case when monthofday=4 then total else 0 end) as Forththmonthtotal,
 sum(case when monthofday=5 then total else 0 end) as fifththmonthtotal,
 --you can apply for 12 month this logic 
 from cte 
group by CustomerCode

SQL Server Self Join By Practical Examples, Note that referencing the same table more than one in a query without using table aliases will result in an error. The following shows the syntax of joining the table� A second method is to use the min function to "walk" a table one row at a time. This method catches new rows that were added after the stored procedure begins execution, provided that the new row has a unique identifier greater than the current row that is being processed in the query.

Below is for BigQuery Standard SQL

This solution avoids making self joins and generic enough to cover data for multiple months

#standardSQL
WITH months AS (
  SELECT FORMAT_DATE('%Y-%m', month) month
  FROM (
    SELECT 
      MIN(PARSE_DATE('%Y-%m', SalesDate)) min_month, 
      MAX(PARSE_DATE('%Y-%m', SalesDate)) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month  
)
SELECT CustomerCode, month, 
  SUM(IF(diff = 0, P_This_Month, 0)) P_This_Month,
  SUM(IF(diff = 1, P_This_Month, 0)) P_1_Month_Ago,
  SUM(IF(diff = 2, P_This_Month, 0)) P_2_Month_Ago
FROM (
  SELECT CustomerCode, P_This_Month, PARSE_DATE('%Y-%m', month) month, 
    DATE_DIFF(PARSE_DATE('%Y-%m', month), PARSE_DATE('%Y-%m', SalesDate), MONTH) diff
  FROM months m CROSS JOIN (
    SELECT CustomerCode, SalesDate, SUM(Profit) P_This_Month
    FROM `project.dataset.table`
    GROUP BY CustomerCode, SalesDate
  ) t
)
WHERE diff BETWEEN 0 AND 2
GROUP BY CustomerCode, month

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 123455666 CustomerCode, '2018-06' SalesDate, 120 Profit UNION ALL
  SELECT 123455666, '2018-06', 100 UNION ALL
  SELECT 123455666, '2018-05', 10 UNION ALL
  SELECT 123455666, '2018-04', 60 UNION ALL
  SELECT 666452342, '2018-06', 900 UNION ALL
  SELECT 666452342, '2018-05', 1000 UNION ALL
  SELECT 666452342, '2018-05', 900 UNION ALL
  SELECT 666452342, '2018-04', 800 
), months AS (
  SELECT FORMAT_DATE('%Y-%m', month) month
  FROM (
    SELECT 
      MIN(PARSE_DATE('%Y-%m', SalesDate)) min_month, 
      MAX(PARSE_DATE('%Y-%m', SalesDate)) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month  
)
SELECT CustomerCode, month, 
  SUM(IF(diff = 0, P_This_Month, 0)) P_This_Month,
  SUM(IF(diff = 1, P_This_Month, 0)) P_1_Month_Ago,
  SUM(IF(diff = 2, P_This_Month, 0)) P_2_Month_Ago
FROM (
  SELECT CustomerCode, P_This_Month, PARSE_DATE('%Y-%m', month) month, 
    DATE_DIFF(PARSE_DATE('%Y-%m', month), PARSE_DATE('%Y-%m', SalesDate), MONTH) diff
  FROM months m CROSS JOIN (
    SELECT CustomerCode, SalesDate, SUM(Profit) P_This_Month
    FROM `project.dataset.table`
    GROUP BY CustomerCode, SalesDate
  ) t
)
WHERE diff BETWEEN 0 AND 2
GROUP BY CustomerCode, month
-- ORDER BY CustomerCode, month

with result

Row CustomerCode    month       P_This_Month    P_1_Month_Ago   P_2_Month_Ago    
1   123455666       2018-04-01  60              0               0    
2   123455666       2018-05-01  10              60              0    
3   123455666       2018-06-01  220             10              60   
4   666452342       2018-04-01  800             0               0    
5   666452342       2018-05-01  1900            800             0    
6   666452342       2018-06-01  900             1900            800     

Parent Child Relation in same Table, How can I structure the query to return that type of result set for all parent child If you have only one level of children, you could join the tables If you want to keep your original result table shape, use an additional column for sorting A common way to do this in SQL Server is to use a Recursive CTE:. In an iterative query, the name server, will not go and fetch the complete answer for your query, but will give back a referral to other DNS server's, which might have the answer. In our previous example our DNS server 172.16.200.30, went to fetch the answer on behalf of our resolver, and provided us with the final answer.

Recursive Queries using Common Table Expressions (CTE) in SQL , One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work� Iterative DNS Query: In Iterative DNS Query, when a DNS Client asks the DNS server for name resolution, the DNS Server provides the best answer it has. If the DNS Server doesn't know the answer to the DNS Query from Client, the answer can be a reference to another lower level DNS Server also.

Learn PostgreSQL Recursive Query By Example, Recursive term: the recursive term is one or more CTE query definitions joined with We will create a new table to demonstrate the PostgreSQL recursive query . Aliases allow you to reference the table name with an abbreviation. For example if you have a table called “products” then you reference that table by creating an alias of “p”. With MySQL you easily create an alias like this: SELECT p.id FROM products p. Multiple joins to the same table can quickly create a messy SQL statement.

Non Recursive CTEs Explained and Why to Use Them, In this article we explore non recursive CTEs (Common Table Expressions). This is a broad Here is the same query using CTEs instead of derived tables: Outer joins tell a query that although some of the rows on both sides of the join correspond exactly, the query should include all of the rows from one table, and also those rows from the other table that share a common value on both sides of the join. Outer joins can be left outer joins or can be right outer joins.

Comments
  • If your sample data correctly on |666452342 |2018-06 |800
  • Is SalesDate a text column?
  • @D-Shih I just made up the data. It is not actual data.
  • @TimBiegeleisen No, it is date_time type