none
How to get MIN date from another table RRS feed

  • Question

  • I have two table, customer and sales_history.

    for example

    <customer>

    customer_id    first name   first_sales_time

    -----------------------------------------------

    1                       Sam                 ?

    2                       Tom                 ?

    3                       Dan                  ?

    4                       Jane                 ?

    <sales_history>

    sales_id        customer_id     item_code           sold_time

    ------------------------------------------------------------------

    1                       1                 A1000                1/21/2019

    2                       1                 B2010                 2/5/2019 

    3                       2                 A2221                3/27/2019  

    4                       2                 C3211                5/15/2018

    5                       3                 D1002                6/7/2019 

    6                       3                 E1302                3/5/2017

     

    The first_sales_time of [customer] table should be the earliest sold_time of [sales_history] table of which customer_id is the same.

    How to use JOIN and MIN function in sql statement?


    • Edited by Jeff0803 Thursday, November 28, 2019 2:43 AM
    Thursday, November 28, 2019 2:31 AM

Answers

  • select

    customer_id ,  [first name],  minsold_time as  first_sales_time

    from 

    customer c join

    (select  customer_id ,min(sold_time) minsold_time

    from sales_history

    group by customer_id ) s on s.customer_id =c,customer_id 

    Thursday, November 28, 2019 4:36 AM
    Moderator
  • Hi Jeff0803,

    Please check following two types script. 

    IF OBJECT_ID('customer') IS NOT NULL drop table  customer  
    IF OBJECT_ID('sales_history') IS NOT NULL drop table  sales_history    
    go 
    create table customer
    (customer_id int,
    [first name]  varchar(20),
    first_sales_time date )
    insert into customer(customer_id,[first name])  values 
    (1,'Sam'),(2,'Tom'),
    (3,'Dan'),(4,'Jane')
    create table sales_history
    (sales_id int,
    customer_id int, 
    item_code varchar(20),
    sold_time date )
    insert into sales_history values 
    (1,1,'A1000','1/21/2019'),(2,1,'B2010','2/5/2019'), 
    (3,2,'A2221','3/27/2019'),(4,2,'C3211','5/15/2018'),
    (5,3,'D1002','6/7/2019'),(6,3,'E1302','3/5/2017')
    
    ----if you would like to show the result 
    ;with cte as(
    select  customer_id ,min(sold_time) minsold_time
    from sales_history
    group by customer_id )
    select s.customer_id ,  [first name],  minsold_time as  first_sales_time
    from customer c 
    join cte s on s.customer_id =c.customer_id 
    /*
    customer_id first name           first_sales_time
    ----------- -------------------- ----------------
    1           Sam                  2019-01-21
    2           Tom                  2018-05-15
    3           Dan                  2017-03-05
    */
    
    ----if you would like to update table 'customer'
    ;with cte as(
    select  customer_id ,min(sold_time) minsold_time
    from sales_history
    group by customer_id )
    update c set c.first_sales_time=  minsold_time 
    from customer c 
    join cte s on s.customer_id =c.customer_id 
    
    select  * from customer
    /*
    customer_id first name           first_sales_time
    ----------- -------------------- ----------------
    1           Sam                  2019-01-21
    2           Tom                  2018-05-15
    3           Dan                  2017-03-05
    4           Jane                 NULL
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 28, 2019 6:39 AM

All replies

  • select

    customer_id ,  [first name],  minsold_time as  first_sales_time

    from 

    customer c join

    (select  customer_id ,min(sold_time) minsold_time

    from sales_history

    group by customer_id ) s on s.customer_id =c,customer_id 

    Thursday, November 28, 2019 4:36 AM
    Moderator
  • Hi Jeff0803,

    Please check following two types script. 

    IF OBJECT_ID('customer') IS NOT NULL drop table  customer  
    IF OBJECT_ID('sales_history') IS NOT NULL drop table  sales_history    
    go 
    create table customer
    (customer_id int,
    [first name]  varchar(20),
    first_sales_time date )
    insert into customer(customer_id,[first name])  values 
    (1,'Sam'),(2,'Tom'),
    (3,'Dan'),(4,'Jane')
    create table sales_history
    (sales_id int,
    customer_id int, 
    item_code varchar(20),
    sold_time date )
    insert into sales_history values 
    (1,1,'A1000','1/21/2019'),(2,1,'B2010','2/5/2019'), 
    (3,2,'A2221','3/27/2019'),(4,2,'C3211','5/15/2018'),
    (5,3,'D1002','6/7/2019'),(6,3,'E1302','3/5/2017')
    
    ----if you would like to show the result 
    ;with cte as(
    select  customer_id ,min(sold_time) minsold_time
    from sales_history
    group by customer_id )
    select s.customer_id ,  [first name],  minsold_time as  first_sales_time
    from customer c 
    join cte s on s.customer_id =c.customer_id 
    /*
    customer_id first name           first_sales_time
    ----------- -------------------- ----------------
    1           Sam                  2019-01-21
    2           Tom                  2018-05-15
    3           Dan                  2017-03-05
    */
    
    ----if you would like to update table 'customer'
    ;with cte as(
    select  customer_id ,min(sold_time) minsold_time
    from sales_history
    group by customer_id )
    update c set c.first_sales_time=  minsold_time 
    from customer c 
    join cte s on s.customer_id =c.customer_id 
    
    select  * from customer
    /*
    customer_id first name           first_sales_time
    ----------- -------------------- ----------------
    1           Sam                  2019-01-21
    2           Tom                  2018-05-15
    3           Dan                  2017-03-05
    4           Jane                 NULL
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 28, 2019 6:39 AM