t-sql joins

Question

Post reply

t-sql joins

rpatil22

Ten Centuries

Points: 1371
More actions
April 17, 2009 at 8:57 am

#208519

Table1 contains daily ( today's status only)
TodayDate Machine Part Location Mfg. Date
2009-04-16 Spindle KS 2009-04-13
2009-04-16 Spring CA 2009-04-14
2009-04-16 Spring NJ 2009-04-14
Table2 contains old record
TodayDate Machine Part Location Mfg. Date
2009-04-15 Spindle KS 2009-04-11
2009-04-15 Spring CA 2009-04-12
2009-04-14 Spindle KS 2009-04-10
2009-04-14 Spring CA 2009-04-09
We need ouput like show all records of today's date + mataching max previous date
TodayDate Machine Part Location Mfg. Date PreviousMfg.Date
2009-04-16 Spindle KS 2009-04-13 2009-04-11
2009-04-16 Spring CA 2009-04-14 2009-04-12
2009-04-16 Spring NJ 2009-04-14 Null
I did some joins but not got the correct results
please help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

rpatil22 Ten Centuries Points: 1371 More actions · Answer 1

I used below query but it is giving multiple records of matching rows.

select

T.TodayDate,

T.MachinePart ,

T.Location,

T.MfgDate,

X.MfgDate as PreviousMfgDate

from Table1 T

LEFT join

Table2 X

on

T.MachinePart = X.MachinePart

AND T.Location = X.Location

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hello

Here's your daily table create statement, and some sample data. If you can do the same with the other table, then it will make it much easier for someone to help you:

CREATE TABLE #Table1 (TodayDate DATETIME, [Machine Part] VARCHAR(10), Location VARCHAR(2), [Mfg. Date] DATETIME)

INSERT INTO #Table1 (TodayDate, [Machine Part], Location, [Mfg. Date])

SELECT '2009-04-16', 'Spindle', 'KS', '2009-04-13' UNION ALL

SELECT '2009-04-16', 'Spring', 'CA', '2009-04-14' UNION ALL

SELECT '2009-04-16', 'Spring', 'NJ', '2009-04-14'

SELECT * FROM #Table1

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden