INDEX MATCH IN SQL.... IS IT POSSIBLE??

Question

INDEX MATCH IN SQL.... IS IT POSSIBLE??

craig.jenkins

SSCommitted

Points: 1643
More actions
December 5, 2017 at 4:34 am

#341056

Mail – <a href="mailto:craig.jenkins@monmotors" rel="nofollow">craig.jenkins@monmotors</a>.com
Hi Guys,
Is there a way to re-create an index match from excel in SQL? An example below:
Seq ContCode ContSeqNo Index match
7425623 CSI 1224199 7425623
7422342
CSI
1236163
7422342
7414996
WCM
1236163
7422342
6845684 WCM 1173926 6849972
7473550 CSI 1236163 7422342
6849972 CSI 1173926 6849972
The index match formula in the Index match column looks like this and does the following:
{=INDEX(A:A,MATCH(1,(C2=C:C)*("CSI"=B:B),0))}
Bring back the number in the Seq column
Where
The ContSeqNo is in the Conseqno column
And
Contcode in the Contcode column is equal to "CSI"
Hope this makes sense.

Viewing 15 posts - 1 through 15 (of 22 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Looks like a job for the LAG or LEAD function, partitioned by ContSeqNo. Please post your table DDL in the form of a CREATE TABLE statement, your sample data in the form of INSERT statements and your expected results for more detailed help.

John

DimUser Ten Centuries Points: 1043 More actions · Answer 2

nested CASE statements? First level [ContCode] second level [ContSeqNo, returning [Seq]?

craig.jenkins SSCommitted Points: 1643 More actions · Answer 3

sorry guys this is a bit above my head. Can anyone point me to some examples ? Many thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

craig.jenkins - Wednesday, December 6, 2017 1:06 AM
sorry guys this is a bit above my head. Can anyone point me to some examples ? Many thanks

If you're not willing to go to the trouble of providing sample data and results in the way I requested before, then try typing "excel index formula sql equivalent" into your favourite search engine. Bear in mind, though, that even if this is a bit above your head, you're still going to have to support it, so you really do need to make sure you understand it.

John

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

-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData 
SELECT * INTO #SampleData FROM (VALUES 
(7425623, 'CSI', 1224199, 7425623),
(7422342, 'CSI', 1236163, 7422342),
(7414996, 'WCM', 1236163, 7422342),
(6845684, 'WCM', 1173926, 6849972),
(7473550, 'CSI', 1236163, 7422342),
(6849972, 'CSI', 1173926, 6849972) 
) d (Seq, ContCode, ContSeqNo, [Index match])

-- Solution using sample data
SELECT a.*, x.Seq
FROM #SampleData a
OUTER APPLY (
SELECT Seq
FROM #SampleData b
WHERE b.ContSeqNo = a.ContSeqNo
AND b.ContCode = 'CSI'
) x

^{“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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 6

John Mitchell-245523 - Wednesday, December 6, 2017 2:13 AM
craig.jenkins - Wednesday, December 6, 2017 1:06 AM
sorry guys this is a bit above my head. Can anyone point me to some examples ? Many thanks
If you're not willing to go to the trouble of providing sample data and results in the way I requested before, then try typing "excel index formula sql equivalent" into your favourite search engine. Bear in mind, though, that even if this is a bit above your head, you're still going to have to support it, so you really do need to make sure you understand it.
John

A bit harsh John. Sorry i just missed your post. I'm keen to learn but need help. Thanks for the advice i will ensure i double check replies next time.

craig.jenkins SSCommitted Points: 1643 More actions · Answer 7

Thank you Chris, appreciate the help. Will try and get me head around this and figure out what the code is doing. Thanks again

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

craig.jenkins - Wednesday, December 6, 2017 2:29 AM
Thank you Chris, appreciate the help. Will try and get me head around this and figure out what the code is doing. Thanks again

Sure no probs. Logically it means this:

For each row in the table, get the Seq ofany rows which match on ContSeqNo and have ContCode ='CSI'

^{“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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 9

Hi,

The code is great and does exactly what i want so thank you for that. Is there a way i can reference the #sampledata table x.seq column??? I have named x.seq as SEQ2.
Is there a way to join to the sampledata table based on [SEQ2] and also show a column based on 2 conditions from the #sampledata table. Then only show the final table (drop the sample table in the results) . I have highlighted in yellow below what i am trying to do. Sorry guys i know these may seem like silly questions but i am learning loads from these posts and its all starting to make a little sense.

-- temp TABLE
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM LogFile

--FIRST TO BRING IN SEQ2 IN A TEMP TABLE
SELECT a.Seq,A.Created,a.Dealer,a.OpNum,a.ContCode,A.Salute,A.REGNO,A.ContSeqNo, x.Seq as[SEQ2]
FROM #SampleData a
cross APPLY (
SELECT Seq
FROM #SampleData b
WHERE b.ContSeqNo = a.ContSeqNo
AND b.ContCode = 'CSI'
) x
--END OF FIRST TO BRING IN SEQ2 IN A TEMP TABLE

SELECT Q.Dealer, Q.CSISeqNo, Q2.Question, #Sampledata.Opnum if Contact code in #Sampledata is equal to WCM
,COUNT(*) AS TOTAL

FROM CSIQuestionsLog Q
JOIN CSIQuestions Q2 ON Q.CSISeqNo=Q2.CSISeqNo
JOIN #SampleData ON Q.LogSeqNo=#SampleData.[SEQ2]

WHERE Q.Created >= '2017-12-06' AND q.Created < '2017-12-07'
AND Q.Dealer in( 'BA','BAA', 'BVW','CA','CVW','MCH','NSK')
AND #SampleData.Tran2 NOT in ('OBE', 'SMS')

GROUP BY Q.Dealer, Q.CSISeqNo, Q2.Question, #Sampledata.Opnum if Contact code in #Sampledata is equal to WCM
ORDER BY Q.Dealer, Q2.Question

DROP TABLE #SampleData

craig.jenkins SSCommitted Points: 1643 More actions · Answer 10

craig.jenkins

SSCommitted

Points: 1643

December 7, 2017 at 9:44 am

#1971202

sample data coming soon, apologies

sgmunson SSC Guru Points: 110640 More actions · Answer 11

Try this:-- temp TABLE IF OBJECT_ID('tempdb..#SampleData', N'U') IS NOT NULL BEGIN DROP TABLE #SampleData; END;

SELECT * INTO #SampleData FROM LogFile;

--FIRST TO BRING IN SEQ2 IN A TEMP TABLE SELECT a.Seq, a.Created, a.Dealer, a.OpNum, a.ContCode, A.Salute, A.REGNO, A.ContSeqNo, x.Seq AS [SEQ2] FROM #SampleData AS a CROSS APPLY ( SELECT Seq FROM #SampleData AS b WHERE b.ContSeqNo = a.ContSeqNo AND b.ContCode = 'CSI' ) AS x; --END OF FIRST TO BRING IN SEQ2 IN A TEMP TABLE

SELECT Q.Dealer, Q.CSISeqNo, Q2.Question, SD.Opnum, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END AS ContCode, COUNT(*) AS TOTAL FROM CSIQuestionsLog AS Q INNER JOIN CSIQuestions AS Q2 ON Q.CSISeqNo = Q2.CSISeqNo INNER JOIN #SampleData AS SD ON Q.LogSeqNo = SD.[SEQ2] WHERE Q.Created >= '2017-12-06' AND Q.Created < '2017-12-07' AND Q.Dealer IN ('BA', 'BAA', 'BVW', 'CA', 'CVW', 'MCH', 'NSK') AND SD.Tran2 NOT IN ('OBE', 'SMS') GROUP BY Q.Dealer, Q.CSISeqNo, Q2.Question, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END ORDER BY Q.Dealer, Q2.Question;

DROP TABLE #SampleData;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

craig.jenkins SSCommitted Points: 1643 More actions · Answer 12

thank you for this steve, when I run the query it shows the following message

'

(6929585 rows affected)

(1573534 rows affected)

Msg 207, Level 16, State 1, Line 22

Invalid column name 'SEQ2'.

'

craig.jenkins SSCommitted Points: 1643 More actions · Answer 13

here is the sample data with the error message. Many thanks

IF OBJECT_ID('tempdb..#SampleData', N'U') IS NOT NULL
BEGIN
DROP TABLE #SampleData;
END;

SELECT * INTO #SampleData FROM (VALUES
('7425623', '2010-01-01', 'BA', '145', 'CSI','MR JONES', 'R123456', '7425623'),
('7422342', '2017-01-01', 'BA', '145', 'CSI','MR JONES2', 'R123457', '7422342'),
('7414996', '2015-01-01', 'CA', '146', 'WCM','MR JONES3', 'R123458', '7422342'),
('6845684', '2014-01-01', 'BAA', '143', 'WCM','MR JONES4', 'R123459', '6849972'),
('7473550', '2015-01-01', 'BAA', '145', 'WCM','MR JONES5', 'R1234510', '7422342'),
('7425629', '2016-01-01', 'BA', '144', 'CSI','MR JONES6', 'R1234511', '12241914'),
('7425610', '2014-01-01', 'BA', '141', 'CSI','MR JONES7', 'R1234512', '12241915'),
('7425615', '2011-01-01', 'BAA', '140', 'CSI','MR JONES8', 'R1234513', '12241916')

) d (Seq, CREATED, DEALER, OPNUM, ContCode, SALUTE, REGNO, ContSeqNo)

--FIRST TO BRING IN SEQ2 IN A TEMP TABLE
SELECT a.Seq, a.Created, a.Dealer, a.OpNum, a.ContCode, A.Salute, A.REGNO, A.ContSeqNo, x.Seq AS [SEQ2]
FROM #SampleData AS a
CROSS APPLY (
      SELECT Seq
      FROM #SampleData AS b
      WHERE b.ContSeqNo = a.ContSeqNo
       AND b.ContCode = 'CSI'
      ) AS x;
--END OF FIRST TO BRING IN SEQ2 IN A TEMP TABLE

SELECT Q.Dealer, Q.CSISeqNo, Q2.Question, SD.Opnum, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END AS ContCode,
COUNT(*) AS TOTAL
FROM CSIQuestionsLog AS Q
INNER JOIN CSIQuestions AS Q2
ON Q.CSISeqNo = Q2.CSISeqNo
INNER JOIN #SampleData AS SD
ON Q.LogSeqNo = SD.[SEQ2]
WHERE Q.Created >= '2017-12-06'
AND Q.Created < '2017-12-07'
AND Q.Dealer IN ('BA', 'BAA', 'BVW', 'CA', 'CVW', 'MCH', 'NSK')
AND SD.Tran2 NOT IN ('OBE', 'SMS')
GROUP BY Q.Dealer, Q.CSISeqNo, Q2.Question, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END
ORDER BY Q.Dealer, Q2.Question;

DROP TABLE #SampleData;

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

craig.jenkins - Wednesday, December 13, 2017 9:19 AM
here is the sample data with the error message. Many thanks
IF OBJECT_ID('tempdb..#SampleData', N'U') IS NOT NULL
BEGIN
DROP TABLE #SampleData;
END;
SELECT * INTO #SampleData FROM (VALUES
('7425623', '2010-01-01', 'BA', '145', 'CSI','MR JONES', 'R123456', '7425623'),
('7422342', '2017-01-01', 'BA', '145', 'CSI','MR JONES2', 'R123457', '7422342'),
('7414996', '2015-01-01', 'CA', '146', 'WCM','MR JONES3', 'R123458', '7422342'),
('6845684', '2014-01-01', 'BAA', '143', 'WCM','MR JONES4', 'R123459', '6849972'),
('7473550', '2015-01-01', 'BAA', '145', 'WCM','MR JONES5', 'R1234510', '7422342'),
('7425629', '2016-01-01', 'BA', '144', 'CSI','MR JONES6', 'R1234511', '12241914'),
('7425610', '2014-01-01', 'BA', '141', 'CSI','MR JONES7', 'R1234512', '12241915'),
('7425615', '2011-01-01', 'BAA', '140', 'CSI','MR JONES8', 'R1234513', '12241916')
) d (Seq, CREATED, DEALER, OPNUM, ContCode, SALUTE, REGNO, ContSeqNo)
--FIRST TO BRING IN SEQ2 IN A TEMP TABLE
SELECT a.Seq, a.Created, a.Dealer, a.OpNum, a.ContCode, A.Salute, A.REGNO, A.ContSeqNo, x.Seq AS [SEQ2]
FROM #SampleData AS a
CROSS APPLY (
      SELECT Seq
      FROM #SampleData AS b
      WHERE b.ContSeqNo = a.ContSeqNo
       AND b.ContCode = 'CSI'
      ) AS x;
--END OF FIRST TO BRING IN SEQ2 IN A TEMP TABLE
SELECT Q.Dealer, Q.CSISeqNo, Q2.Question, SD.Opnum, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END AS ContCode,
COUNT(*) AS TOTAL
FROM CSIQuestionsLog AS Q
INNER JOIN CSIQuestions AS Q2
   ON Q.CSISeqNo = Q2.CSISeqNo
INNER JOIN #SampleData AS SD
   ON Q.LogSeqNo = SD.[SEQ2]
WHERE Q.Created >= '2017-12-06'
AND Q.Created < '2017-12-07'
AND Q.Dealer IN ('BA', 'BAA', 'BVW', 'CA', 'CVW', 'MCH', 'NSK')
AND SD.Tran2 NOT IN ('OBE', 'SMS')
GROUP BY Q.Dealer, Q.CSISeqNo, Q2.Question, CASE SD.ContCode WHEN 'WCM' THEN SD.ContCode ELSE NULL END
ORDER BY Q.Dealer, Q2.Question;
DROP TABLE #SampleData;

Craig, your thread appears to have got somewhat tangled up. What began as one input table is now three and somehow the single sample data set I used to model a potential solution way up here ^^ appears to have been incorporated into an actual solution. Waving away some of the smoke, I suspect there's a very simple solution to all of this.
Can you tell us how the three tables are related to each other?

^{“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

Seq	ContCode	ContSeqNo	Index match
7425623	CSI	1224199	7425623
7422342	CSI	1236163	7422342
7414996	WCM	1236163	7422342
6845684	WCM	1173926	6849972
7473550	CSI	1236163	7422342
6849972	CSI	1173926	6849972