Solved

sql statement Inner join with Max(item A)

Posted on 2013-01-23
33
699 Views
Last Modified: 2013-01-24
Using a following example below from 1 table (will redesign..), I need to produce a list (below) with the latest scanned date of an item and the latest import date for that item.

There will be only 1 UPC Code per day at 1 location, but may be imported several times throughout a period. So I need both for that UPC Code the Max(Scan_Date) and the Max(ImportDate) to achieve a list with the latest import date for that item on that day.
How can I use a join statement to get both for a UPC Code the latest date and latest Scan?

Table A
Scan_Date      Current_Location         UPCCode        ImportDate
1-10-2012       A                                  10                   1-11-2012 22:56
1-10-2012       A                                  10                   1-12-2012 13:56
1-10-2012       B                                   15                   1-10-2012  08:59
1-09-2012       A                                   15                  1-8-2012  08:56
1-09-2012       C                                   10                   1-9-2012 07:56
1-09-2012       B                                    15                   1-9-2012 08:26
1-09-2012       C                                    10                  1-8-2012  08:27
1-08-2012       B                                    15                  1-7-2012 09:30
1-07-2012       C                                    20                  1-7-2012 09:28
1-07-2012       C                                    20                  1-7-2012 10:30
etc..

I want to produce:
10        1-10-2012      A                   1-12-2012 13:56
15         1-10-2012     B                   1-10-2012  08:59
20          1-07-2012    C                    1-7-2012 10:30
ect...

In the example above, UPC 10 has a scan date of 1-10-2012, and 1-9-2012, but I only need  the latest scanned Date (1-10-2012) and the latest import time (1-12-2012 13:56). Then same down the list for each UPC Code.....
0
Comment
Question by:BKennedy2008
  • 15
  • 7
  • 5
  • +3
33 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38809793
SELECT Current_Location, UPC_Code, Max(Scan_Date) as scan_date_max, Max(ImportDate) as import_date_max
FROM [Table A]
GROUP BY  Current_Location, UPC_Code
0
 

Author Comment

by:BKennedy2008
ID: 38809848
That produces multiple entries for each UPC Code, giving me the latest import for that day, but for each day.
I just need the latest date from that list for each UPC Code , and the latest time that UPC Code was imported on that day....
I think I need an added inner join to that statement..

Produced:

UPC             Loc                     Scan Date                           Import Date

10                   A                    2012-12-01  0:00              2012-12-01 12:18
10                   A                     2012-12-28 9:20              2012-12-28  12:00
10                   A                     2012-12-27 9:10              2012-12-27  12:30
etc..
etc..
0
 

Author Comment

by:BKennedy2008
ID: 38809866
Got it.. Thanks

 SELECT Current_Location, a.upccode, Max(Scan_Date) as scan_date_max, Max(ImportDate) as import_date_max
FROM scans a
inner join
(Select MAX(ImportDate) as expr1, UPCCode  from Equipment.dbo.Scans group by UPCCode)c
on a.ImportDate = c.expr1 and a.UPCCode=c.UPCCode
GROUP BY  Current_Location, a.upccode
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38809873
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38809892
Spell out whether Current_Location participates in the grouping.

The below code returns five rows:  A-10, A-15, B-15, C-10, and C-20.

CREATE TABLE a (Scan_Date date, Current_Location char(1), UPCCode tinyint, ImportDate datetime)

INSERT INTO a  (Scan_Date, Current_Location, UPCCode, ImportDate)
VALUES 
('1-10-2012', 'A', 10, '1-11-2012 22:56'), 
('1-10-2012', 'A', 10, '1-12-2012 13:56'),
('1-10-2012', 'B',  15, ' 1-10-2012  08:59'),
('1-09-2012', 'A',  15, '1-8-2012  08:56'),
('1-09-2012', 'C',  10, '1-9-2012 07:56'),
('1-09-2012', 'B',   15, '1-9-2012 08:26'),
('1-09-2012', 'C',   10, '1-8-2012  08:27'),
('1-08-2012', 'B',   15, '1-7-2012 09:30'),
('1-07-2012', 'C',   20, '1-7-2012 09:28'),
('1-07-2012', 'C',   20, '1-7-2012 10:30')

SELECT Current_Location, UPCCode, Max(Scan_Date) as scan_date_max, Max(ImportDate) as import_date_max
FROM a
GROUP BY Current_Location, UPCCode 
ORDER BY Current_Location, UPCCode 

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 38809894
SELECT A1.Current_Location, A1.UPC_Code, Max(A1.Scan_Date) as scan_date_max, Max(A2.ImportDate) as import_date_max
FROM [Table A] A1
JOIN [Table A] A2 ON A1.CurrentLocation = A2.CurrentLocation
AND A1.UPC_Code = A2.UPC_Code
AND A1.Scan_Date = A2.Scan_Date

GROUP BY  A1.Current_Location, A1.UPC_Code
0
 

Author Comment

by:BKennedy2008
ID: 38809903
Close.....

It still gives me for items that were imported at the same time for 2 different days the following:
UPC                Loc                      Scan Date                          Import Date
10                   A                    2012-12-01  0:00              2012-12-01 12:18
10                   A                     2012-12-02 9:20              2012-12-01  12:18

on the list.
Do you see how I can teake care of that?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38809918
Using the data you posted in this question, and the T-SQL I provided above, the Current_Location and UPCCode columns are unique.  

What you have as two rows of 10-A I'm showing as a 10-A and 15-A.

I recommend checking your mockup data to make sure it accurately reflects your question.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38809932
>It still gives me for items that were imported at the same time for 2 different days the following:
Based on this statement, and not the sample data, you'll have to use a subquery and a JOIN, as deighton posted above.
0
 

Author Comment

by:BKennedy2008
ID: 38809935
the current location and UPC are unique for that day, but the UPC Code may be at another location the next day.
0
 
LVL 18

Expert Comment

by:deighton
ID: 38809940
SELECT Current_Location,UPC_Code,Scan_Date_max,MAX(A2.ImportDate) maxImport  FROM

(SELECT A1.Current_Location, A1.UPC_Code,  Max(A2.Scan_Date) as Scan_Date_max
FROM [Table A] A1 GROUP BY  A1.Current_Location, A1.UPC_Code) DQ
JOIN [Table A] A2 ON DQ.CurrentLocation = A2.CurrentLocation
AND DQ.UPC_Code = A2.UPC_Code
AND DQ.Scan_Date_max = A2.Scan_Date
GROUP BY DQ.Current_Location, DQ.UPC_Code
0
 

Author Comment

by:BKennedy2008
ID: 38809967
Deighton, from your code, I get The multi-part identifier "A2.Scan_Date" could not be bound.
in the 3rd line
0
 

Author Comment

by:BKennedy2008
ID: 38810012
Jim Horn You are correct, but I am looking for a query that produces only:

A-10      2012-01-10
B-15      2012-01-09
c-20       2012-01-07

etc..

out of your table that was created...
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38810037
Can you please explain what logic you want for Current_Location
0
 

Author Comment

by:BKennedy2008
ID: 38810069
I need the latest location and the latest date where the UPC Code was scanned .

With the file being updated multiple times within a day, each time it is updated, it gets re-imported into the database. Therefore I need the latest Import date-time from that day it was scanned.

The item may move to another location the next day and the import/scan process starts all over.

My goal here is to find the last known location and date of the UPC item.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 350 total points
ID: 38810096
Ok.  
(1)  Get the max scan date for each UPC code.
(2)  Given those two values, get the current location and the max import date.

Give this a whirl...

CREATE TABLE a (Scan_Date date, Current_Location char(1), UPC_Code tinyint, ImportDate datetime)

INSERT INTO a  (Scan_Date, Current_Location, UPC_Code, ImportDate)
VALUES 
('1-10-2012', 'A', 10, '1-11-2012 22:56'), 
('1-10-2012', 'A', 10, '1-12-2012 13:56'),
('1-10-2012', 'B',  15, ' 1-10-2012  08:59'),
('1-09-2012', 'A',  15, '1-8-2012  08:56'),
('1-09-2012', 'C',  10, '1-9-2012 07:56'),
('1-09-2012', 'B',   15, '1-9-2012 08:26'),
('1-09-2012', 'C',   10, '1-8-2012  08:27'),
('1-08-2012', 'B',   15, '1-7-2012 09:30'),
('1-07-2012', 'C',   20, '1-7-2012 09:28'),
('1-07-2012', 'C',   20, '1-7-2012 10:30')

SELECT a.Current_Location, a1.UPC_Code, a1.Scan_Date_max, MAX(a.ImportDate) maxImport  
FROM  (
	SELECT UPC_Code,  Max(Scan_Date) as Scan_Date_max
	FROM a  
	GROUP BY UPC_Code) a1 
JOIN a ON a1.UPC_Code = a.UPC_Code AND a1.Scan_Date_max = a.Scan_Date
GROUP BY a.Current_Location, a1.UPC_Code, a1.Scan_Date_max

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 150 total points
ID: 38810111
From the above discussion looks to me what you are after is something like
--------------------------------------------------
CREATE TABLE #a (Scan_Date DATE, Current_Location CHAR(1), UPCCode TINYINT, ImportDate DATETIME)

--------------------------------------------------
INSERT INTO #a  (Scan_Date, Current_Location, UPCCode, ImportDate)
VALUES 
('1-10-2012', 'A', 10, '1-11-2012 22:56'), 
('1-10-2012', 'A', 10, '1-12-2012 13:56'),
('1-10-2012', 'B',  15, ' 1-10-2012  08:59'),
('1-09-2012', 'A',  15, '1-8-2012  08:56'),
('1-09-2012', 'C',  10, '1-9-2012 07:56'),
('1-09-2012', 'B',   15, '1-9-2012 08:26'),
('1-09-2012', 'C',   10, '1-8-2012  08:27'),
('1-08-2012', 'B',   15, '1-7-2012 09:30'),
('1-07-2012', 'C',   20, '1-7-2012 09:28'),
('1-07-2012', 'C',   20, '1-7-2012 10:30')

--------------------------------------------------
;WITH cteUPCCode AS (
	SELECT 
		MAX(Scan_Date) AS Scan_Date, 
		UPCCode,
		MAX(ImportDate) AS ImportDate
	FROM
		#a
	GROUP BY
		UPCCode
)
SELECT 
	Scan_Date,
	(SELECT TOP 1 Current_Location FROM #a a2 WHERE a2.UPCCode = a1.UPCCode AND a2.ImportDate = a1.ImportDate) AS Current_Location,
	UPCCode,
	ImportDate
FROM 
	cteUPCCode a1

--------------------------------------------------
DROP TABLE #a

Open in new window

0
 

Author Comment

by:BKennedy2008
ID: 38810319
Rehan- Using your code, it will get
-The latest Import Date and the latest scan date for UPC 11 but combines the location from the latest import date instead of the latest date.

(Scan Date, Location, UPC code, Import Date)

2012-11-29 00:00, A, 11, 2012-12-20 10:10

When the record(s) for UPC 11 are:

2012-11-29 00:00, B, 11, 2012-11-30 07:41
2012-11-04 00:00, A, 11, 2012-12-20 10:10
0
 

Author Comment

by:BKennedy2008
ID: 38810326
Ok- I switched the import with the scan date, and it looks good
0
 

Author Comment

by:BKennedy2008
ID: 38810351
Thanks to both, but the query runs like a champ that Rehan submitted after switching out the

AND a2.ImportDate = a1.ImportDate)  to

AND a2.Scan_Date = a1.Scan_Date

Thanks again..
0
 
LVL 32

Expert Comment

by:awking00
ID: 38810371
select upc_code, scan_date, current_location, importdate from
(select upc_code, scan_date, current_location, importdate,
 row_number() over (partition by upc_code order by scan_date desc) srn,
 row_number() over (partition by upc_code order by importdate desc) irn
 from a) as x
where srn = 1 and irn = 1;
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38810382
BKennedy2008 - Let me know if my T-SQL answered your question, as it was posted before the one you ultimately accepted, which looks like it borrowed off of previous experts' comments.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38810406
I was too slow typing, but I would like to know if my query worked as well.
0
 

Author Comment

by:BKennedy2008
ID: 38810439
I found with yours that  i did not realize that if the upc code was scanned at 2 locations for the same day it listed both. I didnt realize these guys sometimes moved it on the same day. Using the other code it gave me only that day but the latest time for that day that item was scanned.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38810457
>> I found with yours that ...<<
Whose yours?
0
 

Author Comment

by:BKennedy2008
ID: 38810462
awking00 - No it only displays 2682 rows out of the 3100 top daily scans, so its dropping records
0
 

Author Comment

by:BKennedy2008
ID: 38810466
That was for jimhorn...
0
 
LVL 32

Expert Comment

by:awking00
ID: 38810485
Thanks, it worked with the sample data you posted, but I wasn't sure whether or not there might be other scenarios where it fails.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38810491
Out of curiosity, what happens if you change the last line to -
where srn = 1 OR irn = 1?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38810505
I have an issue with the way points were awarded in this question.

It's pretty clear that multiple experts helped you, either by helping you frame up the question, discover issues in data that prevented a successful answer, and ultimately giving a final answer.    It's also clear that the answer that was accepted borrowed code from other experts.  Not that there's anything wrong with that, but it is common practice to acknowledge it, and it's not good grading practice to not acknowledge the help of others that led to it.

It appears that two experts (if not more) gave a correct answer to the question as it was defined.

In cases like this a split is more appropriate than awarding all points to the comment that was closest to what was ultimately the answer.
0
 

Author Comment

by:BKennedy2008
ID: 38810526
I agree, you helped me alot here getting to a solution, and yes I should have. The question is that I cannot undo points now. I appreciate your help, and will award points properly next time.
0
 

Author Comment

by:BKennedy2008
ID: 38810540
or produces multiple scan dates and and import times
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now