t-sql self join and use dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql self join and use dates

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-03-01 : 17:46:27

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-02 : 10:12:41

unless you show us sample data from your tables we will not be able to help you much. when you say
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.
do you mean you'll have all attribute records for all dates? what will happen if max date value has only 2 or 3 attributes? do you just return the alone? Or is it like you need all attribute details as on latest date available?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-03-02 : 18:37:34

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'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 04:25:17

i think what you need is just this


select * 
from Customer c
inner join (SELECT CustomerID,
MAX(CASE WHEN attributeID IN ('575','576','577') THEN value END) AS cs575Val,
MAX(CASE WHEN attributeID IN ('575','576','577') THEN [cust_date] END) AS cs575CustDate,
MAX(CASE WHEN attributeID ='562' THEN value END) AS cs562Val,
MAX(CASE WHEN attributeID ='562' THEN [cust_date] END) AS cs562CustDate
FROM dbo.Inventory 
GROUP BY CustomerID)i1
ON i1.CustomerID = c.CustomerID
LEFT JOIN (SELECT CustomerID,[cust_date],
MAX(CASE WHEN attributeID ='563' THEN value END) AS cs563Val,
MAX(CASE WHEN attributeID ='564' THEN value END) AS cs564Val,
MAX(CASE WHEN attributeID ='565' THEN value END) AS cs565Val
FROM dbo.Inventory 
GROUP BY CustomerID,[cust_date]i2
ON i2.CustomerID = c.CustomerID
AND i2.[cust_date] = i1.cs562CustDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-03-03 : 10:45:44

Thank you for your answer!

However I have the following questions about the sql you just provided:
1. How is the most current date selected?
2. What happens when only 1 of the attribute values exist? Will the join just show null
values for the attributes that do not exist?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 11:45:54

quote:
Originally posted by jassie

Thank you for your answer!

However I have the following questions about the sql you just provided:
1. How is the most current date selected?
2. What happens when only 1 of the attribute values exist? Will the join just show null
values for the attributes that do not exist?

1. using
MAX(CASE WHEN attributeID ='562' THEN [cust_date] END) AS cs562CustDate

this gives most recent date for 562 attribute
2. yep..you'll get NULL for attributes that are not present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources