SQLServerCentral Logo SQLServerCentral Logo
  • Articles
  • Editorials
  • Stairways
  • Forums
    • Forums home
    • Active threads
    • Latest topics
    • Most popular
    • Leaderboard
  • Scripts
  • QotD
  • Books
  • Blogs
  • Register
  • Login
  • Write for us
  • Menu
  • Articles
  • Editorials
  • Stairways
  • Forums
    • Forums home
    • Active threads
    • Latest topics
    • Most popular
    • Leaderboard
  • Scripts
  • QotD
  • Books
  • Blogs
  • Write for us
  • Register
  • Login
  • Home
  • Forums
  • SQL Server 2017
  • SQL Server 2017 - Development
  • INDEX MATCH IN SQL.... IS IT POSSIBLE??
Post reply
  • 1
  • 2
  • Next

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:

    SeqContCodeContSeqNoIndex match
    7425623CSI12241997425623
    7422342
    CSI
    1236163
    7422342
    7414996
    WCM
    1236163
    7422342
    6845684WCM11739266849972
    7473550CSI12361637422342
    6849972CSI11739266849972

    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. 

  • John Mitchell-245523

    SSC Guru

    Points: 148809

    More actions

    December 5, 2017 at 5:18 am

    #1970678

    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

    December 5, 2017 at 5:35 am

    #1970681

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

  • craig.jenkins

    SSCommitted

    Points: 1643

    More actions

    December 6, 2017 at 1:06 am

    #1970853

    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

    December 6, 2017 at 2:13 am

    #1970869

    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

    December 6, 2017 at 2:18 am

    #1970871

    -- 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

    December 6, 2017 at 2:27 am

    #1970872

    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

    December 6, 2017 at 2:29 am

    #1970874

    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

    December 6, 2017 at 2:35 am

    #1970876

    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

    December 7, 2017 at 6:22 am

    #1971151

    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

    December 7, 2017 at 9:44 am

    #1971202

    sample data coming soon, apologies

  • sgmunson

    SSC Guru

    Points: 110644

    More actions

    December 7, 2017 at 2:04 pm

    #1971280

    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

    December 8, 2017 at 1:09 am

    #1971310

    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

    December 13, 2017 at 9:19 am

    #1971975

    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

    December 14, 2017 at 1:36 am

    #1972078

    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

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

  • 1
  • 2
  • Next

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

  • About SQLServerCentral
  • Contact Us
  • Terms of Use
  • Privacy Policy
  • Contribute
  • Contributors
  • Authors
  • Newsletters
Redgate logo

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy

  • Customize additional cookies