none
SQL Query Logic

    Question

  • Hello SQL Experts,

    I want to write a query to pull only the first row from the below result set. Having said that the below result set is for 3 unique rows for LocationIds 47,30 and 74 which are valid. Now I want join the 47 i.e location_id on the fly with another table that has LocationId in the join itself to fetch always the latest record location of a customer and display that latest address for each record with same latest address as highlighted below respectively in the second result set.

    location_id and last_update_date are available in Location1 table
    LocationId is available only in Location2 table

    location_id last_update_date Address1 Address2 City LocationId
    47 1/9/2014 22:13 118 East #8F NY 47
    30 9/18/2013 13:25 405 Taylor St   SF 30
    71 5/20/2013 16:50 1540 Broadway 17th Floor NY 71


    Expected Output
    location_id last_update_date Complete (Address1 +Address2) City LocationId
    47 1/9/2014 22:13 118 East #8F NY 47
    30 9/18/2013 13:25 118 East #8F SF 30
    71 5/20/2013 16:50 118 East #8F NY 71


    Requirement is to pull the latest address of a Customer based on column last_update_date available only in Location1 table and display the above 3 records with again the latest address 3 times as shown above for each record . That means every time when there are multiple addresses for a customer then I need to display latest address for all the records and I do not want display other available addresses. I will concatenate Address1 and Address2 and display as Complete

    Please help me and share the query that will always fetch latest address and display the same for each record.

    Thanks
    Mahesh




    samedi 14 avril 2018 14:50

Réponses

  • see illustration below

    SELECT * INTO #lOCATION1 FROM (
    SELECT 47 as location_id, 37 as Employee_id,'2014-01-09 22:13:10.000' as last_update_date,'NY' as county,101 as ChildrenID    UNION ALL
    SELECT 30 as location_id, 37 as Employee_id,'2013-09-18 13:25:53.000' as last_update_date,'SF' as county,102 as ChildrenID UNION ALL
    SELECT 71 as location_id, 37 as Employee_id,'2013-05-20 16:50:05.000' as last_update_date,'NY' as county,103 as ChildrenID )  L1
    
    SELECT * FROM  #LOCATION1
    
    --Location2 Table
    
    
    SELECT * INTO #lOCATION2 FROM (
    SELECT 47 as location_id,'118 East' as Address1,'#8F' as Address2   UNION ALL
    SELECT 30 as location_id,'405 Taylor St' as Address1,' ' as Address2 UNION ALL
    SELECT 71 as location_id, '1540 Broadway' as Address1,'17th Floor' as Address2) L2
    
    SELECT * FROM  #LOCATION2
    --Customer Table
    SELECT * INTO #Customer FROM (
      SELECT 37 as customer_id , 'abc' as First_name, 'def' as LastName) C3
    
      SELECT * FROM  #Customer
    
      SELECT *,LAST_VALUE(CONCAT(Address1,' ',Address2)) OVER (PARTITION BY customer_id ORDER BY last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress
      FROM #Customer c
      JOIN #LOCATION1 l1
      ON l1.Employee_id = c.customer_id
      JOIN #LOCATION2 l2
      On l2.location_id = l1.location_id
    
    
    
    
    /*
    Ouput
    --------------------------------------------------
    customer_id	First_name	LastName	location_id	Employee_id	last_update_date	county	ChildrenID	location_id	Address1	Address2	LatestAddress
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    37	abc	def	71	37	2013-05-20 16:50:05.000	NY	103	71	1540 Broadway	17th Floor	118 East #8F
    37	abc	def	30	37	2013-09-18 13:25:53.000	SF	102	30	405 Taylor St	 	118 East #8F
    37	abc	def	47	37	2014-01-09 22:13:10.000	NY	101	47	118 East	#8F	118 East #8F
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 09:24

Toutes les réponses

  • with the limited information you've given us, the only suggestion we can give is to use a PARTITION BY based query like this

    SELECT LAST_VALUE(CONCAT(Address1,' ',Address2)) OVER (PARTITION BY Customer_Id ORDER BY last_update_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress,
    ...
    FROM Location1 l1
    JOIN location2 l2
    ON l2.LocationId = l1.location_id
    ...

    if it doesnt help, you need to post some sample data from other involved tables (like customer etc) and give expected output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    samedi 14 avril 2018 17:26
  • Basically the requirement is say an Customer  A has  3 Children ..Child1 ,Child2 and Child3. This customer has multiple addresses say A1, A2 and A3.

    Address A1 has last_update_date of 5/20/2013 16:50, Address A2 has the last_update_date of 9/18/2013 13:25 and Address 3 has last_update_date 1/9/2014 22:13. Now I am interested only in  Address 3 because of latest date. and there are location ids as and when the new addresses get updated for a Customer.

    There are two tables Location1(last_update_date,location_id) and Location2(LocationId ). Now I will have display the latest address that A3 for all 3 children.So only 3 rows need to be displayed with latest Address A3. The same has been highlighted in my first post.

    The SQL query needs show 3 records with Address A3 repeated for all 3 children when I am joining  Location1 and  Location2 tables.

    Please let me the query that will bring the latest Address repeated for the other records as well.

    Thanks

    Mahesh

    5/20/2013 16:50
    5/20/2013 16:50
    5/20/2013 16:50
    samedi 14 avril 2018 18:06
  • Then my last suggestion itself will work for you. Only modification you need to do is to replace the columnnames given in my suggestion with your actual ones. You need to also add the other tables to join like from where you get customer information etc

    As I told, if you had given us sample data from them and specified how they're related then we could have given the full suggestion. Anyways, you shall adapt the given approach on actual tables to get your desired result.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    dimanche 15 avril 2018 05:21
  • I was not able produce the output using the above and am sharing both tables as below and expected output. I have pulled the Children values from the other joins 101,102 and 103

    Location1 Table

    last_update_date   location_id county Children
    1/9/2014 22:13 47 NY 101
    9/18/2013 13:25 30 SF 201
    5/20/2013 16:50 71 NY 301

    Location2 Table

    LocationId Address1 Address2 City
    47 118 East #8F NY
    30 405 Taylor St   SF
    71 1540 Broadway 17th Floor NY

    Expected Output

    location_id last_update_date Complete (Address1 +Address2) City Children county
    47 1/9/2014 22:13 118 East #8F NY 101 NY
    30 9/18/2013 13:25 118 East #8F SF 201 SF
    71 5/20/2013 16:50 118 East #8F NY 301 NY

    The below gives me only 1 row 

     

    LEFT JOIN  
     ( Select max(last_update_date) last_update_date ,locationid,Address1,Address2,City from 
    (Select top 1  locW.location_id, Lv.locationid,max(locW.last_update_date) last_update_date,lv.Address1,lv.Address2,lv.City
    from [dbo].Location1  locW
    join [dbo].Location1 lv on LocW.location_id = lv.LocationId  

    --where   Lv.locationid IN (47,71,30)
    group by locW.location_id,Lv.locationid,lv.Address1,lv.Address2,lv.City ) A
    group by locationid,Address1,Address2,City
    ) lv  

      ON lv.locationid =  locW.location_id 

    last_update_date locationid Address1 Address2 City
    1/9/2014 22:13 47 118 East 60th Street #8F NEW YORK

    Now I have to join the above locationid and then join with Location1 table and get the expected output

    Please help me with the right query.

    Thanks

    Mahesh

    dimanche 15 avril 2018 13:48
  • With these two tables alone you cant get the desired output. As suggested earlier you need to have a common column (customer related one) so as to determine the max address for the customer. Thats what I asked you to add the other tables using join and then use its common column(customerid) for partition by

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    dimanche 15 avril 2018 13:52
  • SELECT LAST_VALUE(CONCAT(lv.Address1,' ',lv.Address2)) OVER (PARTITION BY pemp.person_id ORDER BY l.last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress,
      lv.City,L.location_id 

    FROM Location1 L
     JOIN [Location2 lv on L.location_id = lv.LocationId 
     JOIN Customer  pEmp on pemp.customer_id = L.employee_id
    WHERE L.location_id IN (71,30,47)

    The above gives me 

                                          

    LatestAddress    City              location_id
    118 East  #8F   NEW YORK         71
    118 East  #8F  SAN FRANCISCO 30
    118 East  #8F  NEW YORK        47

    Next how will I join ON lv.location_id =  locW.location_id ??  Still with this it does not give me the expected result.Please suggest the exact way of join

    dimanche 15 avril 2018 17:08
  • like this

    SELECT m.*,... other columns
    FROM
    (
    SELECT LAST_VALUE(CONCAT(lv.Address1,' ',lv.Address2)) OVER (PARTITION BY pemp.person_id ORDER BY l.last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress,
      lv.City,L.location_id 
    
    FROM Location1 L
     JOIN [Location2 lv on L.location_id = lv.LocationId 
     JOIN Customer  pEmp on pemp.customer_id = L.employee_id
    WHERE L.location_id IN (71,30,47)
    )m
    JOIN.... LocW
    ON m.location_id = LocW.location_id

    Again since you're not ready to give the full details, i can only give pseudo code

    For example I dont know which table is designated by LocW, what you have to get from it etc



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    dimanche 15 avril 2018 17:33
  • Yes. I tried with the same approach..and LocW alias  is nothing  but Location2 table. 
    Now If I use 
    WHERE L.location_id IN (71,30,47) the filter then data comes correctly with 3 rows but if I remove that where WHERE L.location_id IN (71,30,47) clause and put at the end of the query : does not work and brings other address as latest and getting 6 rows ,.. My thought was to join m.location_id and ?? 
    From M we get 47 as the latest id and next It has use the location_id 47 and join Location2 table and bring latest address but not working!!!!!

    The below not working 
    )

    m
    JOIN.... LocW
    ON m.location_id = LocW.location_id

    As requested am sharing the complete details for easy reference  Please let me know the query that pulls the expected

    ***************************************************************************************************************************************************

    Location1 Table      
    last_update_date   location_id county Children Employee_id
    1/9/2014 22:13 47 NY 101 37
    9/18/2013 13:25 30 SF 201  
    5/20/2013 16:50 71 NY 301  

           
    Location2 Table      
     
    LocationId Address1 Address2 City
    47 118 East #8F NY
    30 405 Taylor St SF
    71 1540 Broadway 17th Floor NY

         
    Customer Table
    customer_id  first Name Last Name 
    37 abc   def

    location_id last_update_date Complete (Address1 +Address2) City LocationId customer_id 
    47 1/9/2014 22:13 118 East #8F NY 47 37
    30 9/18/2013 13:25 118 East #8F SF 30 37
    71 5/20/2013 16:50 118 East #8F NY 71 37
     
    37

    ****************************************************************************************************************************************************

    Thanks in Advance

    Mahesh





    lundi 16 avril 2018 02:44
  • Again its not complete :)

    For example, you've not specified how customer is linked to other tables. There's no column in the posted model by which customer table can be linked to others

    Anyways here what you should use

    SELECT l2.Address1,c.CustomerId,
    LAST_VALUE(l2.Address1) OVER (PARTITION BY c.CustomerId ORDER BY l2.last_update_date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress
    FROM Location1 l1
    JOIN Location2 l2
    ON l2.LocationId = l1.location_id
    JOIN Customer c
    ON.....
    WHERE location_id IN (47,30,71)

    fill correct condition for customer table relationship

    Also i didnt understand what you mean by putting where condition getting different result etc. We dont have any idea on your system so unless you give clearly what all you've on the table and what you need to filter in your resultset etc we cant help. As suggested multiple times before, had you given the proper details, you would have got the complete solution long back

    see how to properly post a db related question here

    http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

    Repost with the correct details as specified in the link and then I'll take a look. Otherwise I'm not interested in wasting anymore of my time on this!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 05:29
  • Sorry Visakh it was my bad ..as I was only looking for the expected result and did not post the complete table info for customer id = 37.

    The Location1 table has a value employee_id = 37. so the relation is betwen Location1 and Customer Table..I will update the Location1 Table with that column employee_id in previous post as well.  Please let me know if some thing is missed and  will ensure I won't miss any thing going forward .

    Waiting for your query replies...

    Thanks


    lundi 16 avril 2018 07:23
  • Sorry Visakh it was my bad ..as I was only looking for the expected result and did not post the complete table info for customer id = 37.

    The Location1 table has a value employee_id = 37. so the relation is betwen Location1 and Customer Table..I will update the Location1 Table with that column employee_id in previous post as well.  Please let me know if some thing is missed and  will ensure I won't miss any thing going forward .


    again it makes no sense

    You've just added a Children column which in no way seems to have any relationship with customer

    As specified, I wont be contributing any more to this thread unless you post the data IN THE REQUESTED FORMAT AS IN THE LINK


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 07:36
  • no..added Employee_id column under Location1 Table
    lundi 16 avril 2018 07:43
  • no..added Employee_id column under Location1 Table

    post it as proper insert statements and then i'll take a look

    Even after repeated requests, You dont seem to refer to the link which clearly explains how to post a question properly!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 08:02
  • --Location1 Table

    SELECT * INTO #lOCATION1 FROM (
    SELECT 47 as location_id, 37 as Employee_id,'2014-01-09 22:13:10.000' as last_update_date,'NY' as county,101 as ChildrenID    UNION ALL
    SELECT 30 as location_id, 37 as Employee_id,'2013-09-18 13:25:53.000' as last_update_date,'SF' as county,102 as ChildrenID UNION ALL
    SELECT 71 as location_id, 37 as Employee_id,'2013-05-20 16:50:05.000' as last_update_date,'NY' as county,103 as ChildrenID )  L1

    SELECT * FROM  #LOCATION1

    --Location2 Table


    SELECT * INTO #lOCATION2 FROM (
    SELECT 47 as location_id,'118 East' as Address1,'#8F' as Address2   UNION ALL
    SELECT 30 as location_id,'405 Taylor St' as Address1,' ' as Address2 UNION ALL
    SELECT 71 as location_id, '1540 Broadway' as Address1,'17th Floor' as Address2) L2

    SELECT * FROM  #LOCATION2

    --Customer Table
    SELECT * INTO #Customer FROM (
      SELECT 37 as customer_id , 'abc' as First_name, 'def' as LastName) C3

      SELECT * FROM  #Customer

    Expected Output

    location_id last_update_date Complete (Address1 +Address2) City ChildrenID customer_id 
    47 1/9/2014 22:13 118 East #8F NY 101 37
    30 9/18/2013 13:25 118 East #8F SF 201 37
    71 5/20/2013 16:50 118 East #8F NY 301 37


    Please  


    lundi 16 avril 2018 09:03
  • see illustration below

    SELECT * INTO #lOCATION1 FROM (
    SELECT 47 as location_id, 37 as Employee_id,'2014-01-09 22:13:10.000' as last_update_date,'NY' as county,101 as ChildrenID    UNION ALL
    SELECT 30 as location_id, 37 as Employee_id,'2013-09-18 13:25:53.000' as last_update_date,'SF' as county,102 as ChildrenID UNION ALL
    SELECT 71 as location_id, 37 as Employee_id,'2013-05-20 16:50:05.000' as last_update_date,'NY' as county,103 as ChildrenID )  L1
    
    SELECT * FROM  #LOCATION1
    
    --Location2 Table
    
    
    SELECT * INTO #lOCATION2 FROM (
    SELECT 47 as location_id,'118 East' as Address1,'#8F' as Address2   UNION ALL
    SELECT 30 as location_id,'405 Taylor St' as Address1,' ' as Address2 UNION ALL
    SELECT 71 as location_id, '1540 Broadway' as Address1,'17th Floor' as Address2) L2
    
    SELECT * FROM  #LOCATION2
    --Customer Table
    SELECT * INTO #Customer FROM (
      SELECT 37 as customer_id , 'abc' as First_name, 'def' as LastName) C3
    
      SELECT * FROM  #Customer
    
      SELECT *,LAST_VALUE(CONCAT(Address1,' ',Address2)) OVER (PARTITION BY customer_id ORDER BY last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress
      FROM #Customer c
      JOIN #LOCATION1 l1
      ON l1.Employee_id = c.customer_id
      JOIN #LOCATION2 l2
      On l2.location_id = l1.location_id
    
    
    
    
    /*
    Ouput
    --------------------------------------------------
    customer_id	First_name	LastName	location_id	Employee_id	last_update_date	county	ChildrenID	location_id	Address1	Address2	LatestAddress
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    37	abc	def	71	37	2013-05-20 16:50:05.000	NY	103	71	1540 Broadway	17th Floor	118 East #8F
    37	abc	def	30	37	2013-09-18 13:25:53.000	SF	102	30	405 Taylor St	 	118 East #8F
    37	abc	def	47	37	2014-01-09 22:13:10.000	NY	101	47	118 East	#8F	118 East #8F
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 09:24
  • Thanks a ton Visakh..I had use the below column as is for the expected result.

    LAST_VALUE(CONCAT(Address1,' ',Address2)) OVER (PARTITION BY customer_id ORDER BY last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress

    lundi 16 avril 2018 15:00
  • Thanks a ton Visakh..I had use the below column as is for the expected result.

    LAST_VALUE(CONCAT(Address1,' ',Address2)) OVER (PARTITION BY customer_id ORDER BY last_update_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestAddress

    Glad that you got it sorted at last!

    At least for the future posts please ensure you give proper data beginning itself for making both of our lives easier


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    lundi 16 avril 2018 15:15