March 1, 2014 at 3:48 pm
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?
March 1, 2014 at 5:26 pm
Are you using AdventureWorks as your model? Or if not, please post T-SQL to create tables and add some sample data and expected results. Apologies if I'm slow, but it's a lot easier to answer a well framed question.
March 2, 2014 at 8:47 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
InventoryIDCustomerIDattributeIDvalue [cust_date]
26249068477170 562 GA 2013-08-14 07:26:00
26249068377170 565 05/23/20142013-08-14 07:26:00
26249068277170 563 Acd 2013-08-14 07:26:00
26249068177170 564 08/14/20132013-08-14 07:26:00
251784 77170 564 09/06/20072007-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/20062006-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/20062006-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'
March 2, 2014 at 9:36 pm
Getting warmer, but it seems you should read this before posting:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I'm not trying to be difficult, but it's really hard to recreate something like your situation without it. Could you post the create table scripts and the inserts per the article? Off the top of my head, I would probably use a CTE or a windowing function to do this. But without readily consumable data, it's just a guess.
Sorry I can't be more help. (yet)
March 3, 2014 at 8:49 am
I read the article. I am not certain what else you are looking for. Can you point me to a post that is well written so I can understand what you are looking for?
March 3, 2014 at 9:38 am
wendy elizabeth (3/3/2014)
I read the article. I am not certain what else you are looking for. Can you point me to a post that is well written so I can understand what you are looking for?
We have the ddl for only one of the tables. It will work on a test database with just a couple minor tweaks. What about the Customer table?
We also don't have sample data in a consumable format (insert statements). In other words we should be able to run your script and have a couple of tables with some data so that the query you posted will work on our test databases.
The last part that we need to understand is what you expect for output based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply