December 9, 2011 at 1:41 pm
I have two tables an Item Master table and an Item Transaction table (IMItmIdx_SQL and IMInvTrx_SQl respectively). There is a unique record in the Item Master table for each item number. There are many transaction records for each item in the Transaction table.
Is it possible to create a single query which returns the Item No and its description from the Item Master table and select fields from the most recent transaction for that item in the transaction table. This query will be run for a range of item numbers.
The query below returns a single piece of information, Trx_dt, from the transaction table. However, I need additional fields from the transaction table. I do not know how to modify this query to also return T.Doc_Ord_no, T.Vend_no, T.Qty, etc. which are part of the most recent transaction record.
SELECT M.item_no, T.Trx_Dt
FROM IMITMIDX_SQL M
INNER JOIN (SELECT Item_No, MAX(trx_dt) AS Trx_Dt
FROM IMINVTRX_SQL
WHERE (source = 'R') AND (doc_type = 'R')
GROUP BY Item_No) T
ON M.item_no = T.Item_No
WHERE (M.item_no = '10-803-00')
Below you will find the code to create two table and populate them with sample data. Be sure to change the database name to the appropriate value. At the bottom I have included the data set I would like to have returned.
Thank you for your help.
pat
USE [A]
GO
/****** Object: Table [dbo].[IMItmIdx_SQL] Script Date: 12/09/2011 08:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IMItmIdx_SQL](
[IDNo] [bigint] IDENTITY(1,1) NOT NULL,
[Item_No] [varchar](15) NULL,
[Item_Desc_1] [varchar](30) NULL,
[Item_Desc_2] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [A]
GO
/****** Object: Table [dbo].[IMInvTrx_SQL] Script Date: 12/09/2011 08:40:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IMInvTrx_SQL](
[IDNo] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [char](1) NULL,
[Doc_Type] [char](1) NULL,
[Item_no] [varchar](15) NULL,
[Trx_Dt] [bigint] NULL,
[Doc_Ord_No] [char](8) NULL,
[Vend_No] [varchar](12) NULL,
[Qty] [decimal](18, 5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into IMItmIdx_SQL (Item_No, Item_Desc_1,Item_Desc_2)
Select 'A','A Desc 1','A Desc 2' Union All
Select 'B','B Desc 1','B Desc 2' Union All
Select 'C','C Desc 1','C Desc 2'
Go
Insert into IMInvTrx_SQL (Source,Doc_Type,Item_no,Trx_Dt,Doc_Ord_No,Vend_No,Qty)
Select 'R','R','A',20111209,'00256800','748',66.00000 Union All
Select 'R','I','A',20111209,'00256800','748',1.00000 Union All
Select 'R','R','B',20111209,'00014700','98',3.00000 Union All
Select 'R','R','A',20111208,'00587400','4444',100.00000 Union All
Select 'R','R','A',20111207,'00874100','5788',3.00000 Union All
Select 'R','R','B',20111207,'00777700','65',7895.00000 Union All
Select 'R','R','C',20111207,'00211300','65',23.00000 Union All
Select 'R','R','C',20111206,'00471400','3217',156.00000
Go
The correct answer is
A,A Desc1,A Desc 2, 20111209,00256800,748,66
B,B Desc 1, B Desc 2, 20111209, 000147700,748,3
C,C Desc 1, C Desc 2, 20111207,00211300,65,23
December 9, 2011 at 2:00 pm
Use a CROSS APPLY. You can also use a CTE with ROW_NUMBER(), but I've found that the CROSS APPLY tends to perform better.
SELECT Item_No, Item_Desc_1, Item_Desc_2, Trx_Dt, Doc_Ord_No, Vend_No, Qty
FROM IMItmIdx_SQL AS i
CROSS APPLY (
SELECT TOP (1) Trx_Dt, Doc_Ord_No, Vend_No, Qty
FROM IMInvTrx_SQL AS t
WHERE i.Item_No = t.Item_No
ORDER BY Trx_Dt DESC, Qty DESC
) AS t
Your Trx_Dt is not stored in a date/time field. You're usually much better off if you store date/time data in date/time fields.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 9, 2011 at 2:04 pm
That worked perfectly. "Cross Apply", I had never encountered this before. I will go and read up on it.
Thank you so much,
pat
December 10, 2011 at 3:05 am
Ah so this explains why I got a republication email for my SSC article today 🙂
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 10, 2011 at 11:01 am
Paul,
Did I do something wrong? If so it was not intentional. I did a Search and found your two articles. I then emailed that link to two of my frineds. Sorry.
Thanks for the artilce though. They were insightful.
pat
December 10, 2011 at 11:15 am
mpdillon (12/10/2011)
Did I do something wrong? If so it was not intentional.
Hi Pat,
No! Not at all. Steve Jones (the editor) chooses to republish articles based on questions that are coming up in the forums. Your statement about not being familiar with APPLY might have prompted him to republish my article (and for me to get an email saying so). If anything, I should be thanking you. Sorry for not being clear before.
(The two links I posted in my earlier reply were to the articles in question.)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 11, 2011 at 5:26 pm
Celko,
Thank you for your comments. I was unfamiliar with ISO-11179 and 8601. I am sure this may seem odd but the table names do follow a standardized naming convention. It is not my naming convention rather it is one adopted by the ERP software publisher. And while these names may seem strange to someone just viewing two of the 500 tables in the database, I can assure you once you grasp the naming convention the file names make sense. I considered abstracting the table names but for such a simple data set I choose not to do it. And you were astute to observe that this mimics a 1950’s file system (actually this ERP product traces it linage to an ERP system developed on the WANG in the late 1970’s). Hopefully, this background will to some extent assuage your some of your concerns. I have to play the hand I am dealt.
Again thanks for the link, …smart-questions.htm. In creating my DDL I followed a SQL Server Central article published by Jeff Moden, http://www.sqlservercentral.com/articles/Best+Practices/61537/. I did my best to follow the procedure he outlined. I did not totally recreate the tables as they exist in the ERP product. I used only the fields, tables and small data set I needed to get an understanding of how to solve this problem. However, I did run what I posted. The tables were created and the data was inserted.
You will get a laugh out of this. There is an identity field in every table. It was one of those fields I chose not to include. Its name is A4GLIdentity. After 15 years I still do not know what that stands for!
I’ll bet your book on SQL puzzles is interesting and enlightening. Although I am sure it is over my head.
pat
December 12, 2011 at 8:36 am
CELKO (12/10/2011)
Please post usable DDL,
He did post usable DDL.
Code should be in Standard SQL as much as possible and not local dialect.
Why? This is a dialect specific forum and in this case the dialect provides a much more efficient way of accomplishing the specified task.
If this were a general SQL forum or the OP has specified that it needed to be portable, then you might have a point.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply