none
t-sql 2012 max date and self join to other tables

    Question

  • In a sql server 2012 I would like to join a table to other tables by CustomerNumber.
    The table called 'Inventory' that I want to join to other tables has the following characteristics:
    1. The CustomerNumber is a foreign key reference to the other tables that need to be joined to.
    2. I need to pick the most current cust_date for records where the attributeID is either: 52,53,54, or 55.
    3. For each attribute value of 52, 53, 54, or 55, there usally between 0 to 50 rows in the inventory table.

    4.  The attributes id  values are 52, 53, 54, and 55 are related to each other by cust_date.
    Thus in the Inventory table, I need to correlate these 4 attributes values to each other by finding maximum (most current). I do know that if I can locate the most current cust_date for one attribute I can relate each attribute to each other.

    5. I can not join the inventory table to the other tables by Inventory_ID, this that relationship would not help with the query.

    Thus I have the following questions:

    1. Can you tell me if I should use the query listed below to find the most current customer date, and/or do you have a query that just can suggest? If so, what would the query be? (**Note I found I had to use distinct on the select since each attribute value occurs more than one time in the table. Also attribute value of 52 is always required so it can be related to the other attribute values.)

    SELECT distinct CustomerNumber, cust_date
    FROM  cust_data
    INNER JOIN
     (
      SELECT CustomerNumber, MAX(cust_date) AS cust_date
      FROM cust_data
                    where attributeID = 53
      GROUP BY CustomerNumber
     ) AS T
     ON cust_data.CustomerNumber = T.CustomerNumber
     AND cust_data.cust_date = T.cust_date
    2. How would you join the inventory table to the other tables using the customer number? There are times when there is no attribute values = 52, 53, 54, or 55? Would you show me somet-sql that would solve this issue for me?

    Saturday, March 01, 2014 10:44 PM

Answers

  • I think you need to digest the code from the answers you got:

    For example:

    When you want to retrieve most recent row for a particular user, you can use the row_number function and use partition by the user and Order by the datetime column sorted descendningly to get a list of ranking, most recent one as the number 1 and you can filter the resultset with rn=1 to get most recent row for each user.

    You can get more information:

    http://technet.microsoft.com/en-us/library/ms186734(v=sql.110).aspx

    Monday, March 03, 2014 4:36 PM

All replies

  • In t-sql 2008, I have a table that I need to join to itself server items based upon customer number, cust_date, and attribute id.
    The attrribute id values are 53, 54, and 55. There are the same attribute ids that can occur lots of times during the year so the cust_date can change.
    I need to join rows in the table to itself several times where the cust_date is the same and the most current date.

    Thus can you show me how to join the table to itself with selecting the maximum cust_date and the for attribute value = 53, 54, and 55?

    Friday, February 28, 2014 5:52 PM
  • Select customernumber, cust_date, attributeid  
    FROM (Select customernumber, cust_date, attributeid, Row_number() Over(Partition by customernumber Order by cust_Date DESC) rn) from yourtable
    WHERE attributeid IN (53,54,55))t 
    WHERE rn=1


    Friday, February 28, 2014 6:10 PM
  • Where is the table? Could you please post the DDL and sample data?

    • Edited by Taherul673 Friday, February 28, 2014 6:12 PM
    Friday, February 28, 2014 6:11 PM
  • what does the rn=1 stand for?
    Friday, February 28, 2014 7:14 PM
  • what does the rn=1 stand for?
    Thats to get latest record within each customernumber  group. rn is calculate column generated using row_number function inside the derived table

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, February 28, 2014 7:19 PM
  • how do you join this table to other tables that use customernumber?
    Friday, February 28, 2014 8:03 PM
  • what does the rn=1 stand for?

    If there are multiple records with same customer number, we are trying to pick the one with highest date (Since we are using order by date DESC, it will get the highest date to the top. so RN = 1, gives you only one value for that customer, which is the highest date for the customer alone.

    Read more about ranking functions here @ http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Friday, February 28, 2014 8:11 PM
  • SELECT  customernumber, * from anothertable a 
    INNER JOIN (
    Select customernumber, cust_date, attributeid  
    FROM (Select customernumber, cust_date, attributeid, Row_number() Over(Partition by customernumber Order by cust_Date DESC) rn) from yourtable
    WHERE attributeid IN (53,54,55))t 
    WHERE rn=1) tabalias
    On a.customernumber= t.customernumber


    Friday, February 28, 2014 8:14 PM
  • how do you join this table to other tables that use customernumber?

    I am pretty sure you are aware of the concept JOINS. so if you want the matching records, you will use a INNER JOIN statement to join the two tables like below..

    SELECT *
    FROM Customer a
    INNER JOIN CustomerOrder b ON a.CustomerID = b.CustomerID

    I am joining both the tables based on the CustomerID..

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Friday, February 28, 2014 8:14 PM
  • The best way to get help with questions like this is to post:

    1) CREATE TABLE statements for your tables. (Preferrably simplified to what's important for the problem.)
    2) INSERT statements with sample data, enough to demonstrate all angles of the problem.
    3) The desired result given the sample.

    To find the most recent row per attribute in cust_data, use this:

    WITH numbering AS (
       SELECT CustomerNumber, cust_date, attributeID,
              row_number() OVER (PARTITION BY attributeID ORDER BY cust_date DESC) AS rowno
       FROM   cust_data
       WHERE  attributeID IN (52, 53, 54, 55)
    )
    SELECT CustomerNumber, cust_date, attributeID
    FROM   cust_data
    WHERE  rowno = 1


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 01, 2014 10:57 PM
  • Here is the additional info you wanted to see:

    1. here is the Inventory table with some data.
    SELECT [InventoryID]
          ,[personID]    
          ,[attributeID]
          ,[value]
          ,[cust_date]
        
      FROM [dbo].[Inventory]
      where personID=77170
      and attributeID in (562,563,564,565)
      order by date desc

    InventoryID CustomerID attributeID value          [cust_date]
    262490684 77170         562          GA         2013-08-14 07:26:00
    262490683 77170         565          05/23/2014 2013-08-14 07:26:00
    262490682 77170         563           Acd         2013-08-14 07:26:00
    262490681 77170         564          08/14/2013 2013-08-14 07:26:00
    251784         77170         564          09/06/2007 2007-09-08 00:00:00
    250029         77170         562          MA         2007-09-08 00:00:00
    248287         77170         563          asp         2007-09-08 00:00:00
    251785         77170         564          09/07/2006 2006-09-08 00:00:00
    248286         77170         563          asp         2006-09-08 00:00:00
    250028         77170         562          MA         2006-09-08 00:00:00
    251783         77170         564          09/06/2006 2006-09-06 00:00:00
    249367         77170         562          LA         2006-09-06 00:00:00
    248285         77170         563          asp         2006-09-06 00:00:00

    2. here is the table definition:

    alter TABLE [dbo].[Inventory](
     [InventoryID] [int] IDENTITY(1,1) NOT NULL,
     [CustomerID] [int] NOT NULL,
     [attributeID] [int] NOT NULL,
     [value] [varchar](256) NULL,
     [cust_date] [smalldatetime] NULL,

     CONSTRAINT [PK_CustomerData] PRIMARY KEY NONCLUSTERED
    (
     [InventoryID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    ALTER TABLE [dbo].[Inventory]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerData_Person] FOREIGN KEY([CustomerID])
    REFERENCES [dbo].[Customer].([CustomerID])
    GO

    ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_CustomerData_Person]
    GO

    3. here is the join to the Customer table that I would like assistance on to also include the
       most current cust_date
    select * from Customer
    join dbo.Inventory cs575 on cs575.CustomerID = Customer.CustomerID and cs575.attributeID IN ('575','576','577')
    join dbo.Inventory cs562 on cs562.CustomerID = Customer.CustomerID and cs562.attributeID ='562'
    left join dbo.Inventory cs563 on cs563.CustomerID = Customer.CustomerID and cs563.[Date] = cs562.[Date] and cs563.attributeID ='563'
    left join dbo.Inventory cs564 on cs564.CustomerID = Customer.CustomerID and cs564.[Date] = cs562.[Date] and cs564.attributeID ='564'
    left join dbo.Inventory cs565 on cs565.CustomerID = Customer.CustomerID and cs565.[Date] = cs562.[Date] and cs565.attributeID ='565'

    Monday, March 03, 2014 3:42 AM
  • ;with mycte as (
     (Select  [InventoryID], [CustomerID], [attributeID], [value], [cust_date],
     Row_number() Over(Partition by [CustomerID] Order by cust_Date DESC) rn) 
     from  dbo.Inventory
    --WHERE attributeid IN (53,54,55)
    ) 
    ,myInventory as 
    (
    Select [InventoryID], [CustomerID], [attributeID], [value], [cust_date]
    FROM mycte
    WHERE rn=1
    )
    
    
    select * from Customer
    join myInventory cs575 on cs575.CustomerID = Customer.CustomerID and cs575.attributeID IN ('575','576','577')
    join myInventory cs562 on cs562.CustomerID = Customer.CustomerID and cs562.attributeID ='562'
    left join myInventory cs563 on cs563.CustomerID = Customer.CustomerID and cs563.[Date] = cs562.[Date] and cs563.attributeID ='563'
    left join myInventory cs564 on cs564.CustomerID = Customer.CustomerID and cs564.[Date] = cs562.[Date] and cs564.attributeID ='564'
    left join myInventory cs565 on cs565.CustomerID = Customer.CustomerID and cs565.[Date] = cs562.[Date] and cs565.attributeID ='565'

    Monday, March 03, 2014 4:36 AM
  • Thank you for your answer! I do have the following additional questions:

    1. When there is an error in the .net application, there could be times when a particular customer number does not have all 4 attribute values. There could be times when only one attribute value exists. When this occurs, how would I change the query?

    2. How should I join mycte to the join that uses the select * from Customer? If the 'mycte' is joined to the may select, can you tell me how they are joined together?

    3. What does the WHERE rn=1 mean? it is the first of something but I do not know what it means.



    Monday, March 03, 2014 3:16 PM
  • I think you need to digest the code from the answers you got:

    For example:

    When you want to retrieve most recent row for a particular user, you can use the row_number function and use partition by the user and Order by the datetime column sorted descendningly to get a list of ranking, most recent one as the number 1 and you can filter the resultset with rn=1 to get most recent row for each user.

    You can get more information:

    http://technet.microsoft.com/en-us/library/ms186734(v=sql.110).aspx

    Monday, March 03, 2014 4:36 PM
  • Here is the additional info you wanted to see:

    1. here is the Inventory table with some data.
    SELECT [InventoryID]
          ,[personID]    
          ,[attributeID]
          ,[value]
          ,[cust_date]
        
      FROM [dbo].[Inventory]
      where personID=77170
      and attributeID in (562,563,564,565)
      order by date desc

    InventoryID CustomerID attributeID value          [cust_date]
    262490684 77170         562          GA         2013-08-14 07:26:00
    262490683 77170         565          05/23/2014 2013-08-14 07:26:00
    262490682 77170         563           Acd         2013-08-14 07:26:00
    262490681 77170         564          08/14/2013 2013-08-14 07:26:00
    251784         77170         564          09/06/2007 2007-09-08 00:00:00
    250029         77170         562          MA         2007-09-08 00:00:00
    248287         77170         563          asp         2007-09-08 00:00:00
    251785         77170         564          09/07/2006 2006-09-08 00:00:00
    248286         77170         563          asp         2006-09-08 00:00:00
    250028         77170         562          MA         2006-09-08 00:00:00
    251783         77170         564          09/06/2006 2006-09-06 00:00:00
    249367         77170         562          LA         2006-09-06 00:00:00
    248285         77170         563          asp         2006-09-06 00:00:00

    2. here is the table definition:

    alter TABLE [dbo].[Inventory](
     [InventoryID] [int] IDENTITY(1,1) NOT NULL,
     [CustomerID] [int] NOT NULL,
     [attributeID] [int] NOT NULL,
     [value] [varchar](256) NULL,
     [cust_date] [smalldatetime] NULL,

     CONSTRAINT [PK_CustomerData] PRIMARY KEY NONCLUSTERED
    (
     [InventoryID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    ALTER TABLE [dbo].[Inventory]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerData_Person] FOREIGN KEY([CustomerID])
    REFERENCES [dbo].[Customer].([CustomerID])
    GO

    ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_CustomerData_Person]
    GO

    3. here is the join to the Customer table that I would like assistance on to also include the
       most current cust_date
    select * from Customer
    join dbo.Inventory cs575 on cs575.CustomerID = Customer.CustomerID and cs575.attributeID IN ('575','576','577')
    join dbo.Inventory cs562 on cs562.CustomerID = Customer.CustomerID and cs562.attributeID ='562'
    left join dbo.Inventory cs563 on cs563.CustomerID = Customer.CustomerID and cs563.[Date] = cs562.[Date] and cs563.attributeID ='563'
    left join dbo.Inventory cs564 on cs564.CustomerID = Customer.CustomerID and cs564.[Date] = cs562.[Date] and cs564.attributeID ='564'
    left join dbo.Inventory cs565 on cs565.CustomerID = Customer.CustomerID and cs565.[Date] = cs562.[Date] and cs565.attributeID ='565'

    Didnt i give answer to this already?

    see

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=192601


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, March 03, 2014 4:46 PM