none
Help in matching query

    Question

  • Hi, I am trying to get result as I declared in variable.
    These code and cost_code both should match with any set of main_code. If any code has different cost_code
    in same main_code then should pick max(effdate) record. Pl. help on this query.


    create table #credit (main_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))

    insert into #credit values ('AS','AAAA','1','2010-01-01',50.5)
    insert into #credit values ('AS','AAAA','1','2012-01-01',60.5)
    insert into #credit values ('AS','BBBB','2','2012-01-01',100)
    insert into #credit values ('AS','CCCC','2','2012-01-01',80)
    insert into #credit values ('AS','DDDD','3','2009-01-01',50)
    insert into #credit values ('AS','DDDD','3','2010-01-01',75)
    insert into #credit values ('AS','EEEE','2','2010-01-01',10)

    insert into #credit values ('AB','AAAA','1','2010-01-01',60.5)
    insert into #credit values ('AB','BBBB','2','2012-01-01',100)
    insert into #credit values ('AB','CCCC','2','2012-01-01',80)
    insert into #credit values ('AB','DDDD','3','2009-01-01',75)
    insert into #credit values ('AB','DDDD','3','2010-01-01',55)
    insert into #credit values ('AB','EEEE','2','2010-01-01',20)

    insert into #credit values ('AC','AAAA','1','2010-01-01',80.5)
    insert into #credit values ('AC','BBBB','2','2012-01-01',100)
    insert into #credit values ('AC','CCCC','2','2012-01-01',80)
    insert into #credit values ('AC','DDDD','3','2009-01-01',55)
    insert into #credit values ('AC','EEEE','2','2010-01-01',20)

    DECLARE @code varchar(4);
    Select @code='AAAA'
    DECLARE @cost_code_AAAA decimal(5,2);
    Select @cost_code_AAAA=60.5

    DECLARE @code varchar(4);
    Select @code='BBBB'
    DECLARE @cost_code_BBBB decimal(5,2);
    Select @cost_code_BBBB=100

    DECLARE @code varchar(4);
    Select @code='CCCC'
    DECLARE @cost_code_CCCC decimal(5,2);
    Select @cost_code_CCCC=80

    DECLARE @code varchar(4);
    Select @code='DDDD'
    DECLARE @cost_code_DDDD decimal(5,2);
    Select @cost_code_DDDD=75

    --Expected output as per above set criteria

    main_code    code    code1    date1        cost_code
    AS            AAAA    1       2012-01-01     60.50
    AS            BBBB    2       2012-01-01     100.00
    AS            CCCC    2       2012-01-01     80.00
    AS            DDDD    3       2010-01-01     75.00
    AS            EEEE    2       2010-01-01     10.00

    Thank you.

    Saturday, October 05, 2013 5:26 PM

Answers

  • This is the table join I was referring to.

    create table #values (code char(10),cost_code decimal(5,2))
     insert into #values values ('AAAA',60.5)
     insert into #values values ('BBBB',100)
     insert into #values values ('CCCC',80)
     insert into #values values ('DDDD',75)
    
     
    SELECT Main_Code, code, code1, date1, cost_code
     FROM
    (SELECT ROW_NUMBER() 
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row, 
    Main.Main_code, 
    Main.code, 
    Main.date1,
    Main.code1, 
    Main.cost_code 
    FROM #Credit as Main 
     INNER JOIN
     (
     SELECT #credit.code, MAX(date1) as date1
     FROM #credit INNER JOIN
     #values ON #credit.code = #values.code AND #credit.cost_code = #values.cost_code
     GROUP BY #credit.code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1


    Martina White


    • Edited by Martina White Sunday, October 06, 2013 1:56 PM
    • Marked as answer by Kenny_Gua Sunday, October 06, 2013 3:22 PM
    Sunday, October 06, 2013 1:56 PM

All replies

  • PFB code

     
     select main_code,code,code1,MAX(date1),MAX(cost_code) from #credit
     where main_code = 'AS'
     group by main_code,code,code1

    Thanks

    Sarvana kumar C

    Saturday, October 05, 2013 6:01 PM
  •  select main_code,code,code1,MAX(date1),MAX(cost_code) from #credit
     where main_code = 'AS'  
     group by main_code,code,code1

    Hi, The above query is wrong. Query should find the main_code with correct criteria which I mentioned.   There are many main_code in my data and need to  match the criteira which I mentioned in the DECLARE statements. It's means code-AAAA should have cost_code=60.5, code-BBBB should have cost_code=100,code-CCCC should have cost_code=80 and code-DDDD should have cost_code=75 but all should match in any same main_code otherwise result will be nothing.


    Saturday, October 05, 2013 8:01 PM
  • Hi, The above query is wrong. Query should find the main_code with correct criteria which I mentioned.   There are many main_code in my data and need to  match the criteira which I mentioned in the DECLARE statements. It's means code-AAAA should have cost_code=60.5, code-BBBB should have cost_code=100,code-CCCC should have cost_code=80 and code-DDDD should have cost_code=75 but all should match in any same main_code otherwise result will be nothing.
    Sunday, October 06, 2013 12:26 AM
  • Here is a query that will get you the results you are looking for:

     SELECT 
     Credit.main_code, 
     Credit.code, 
     Credit.code1, 
     Credit.date1, 
     Credit.cost_code
     FROM #credit as Credit INNER JOIN
     (
     SELECT main_code, code, MAX(date1) as date1
     FROM #credit
     GROUP BY main_code, code
     ) as MostRecent
     ON Credit.main_code = MostRecent.main_code 
     AND Credit.code = MostRecent.code 
     AND Credit.date1 = MostRecent.date1
     ORDER BY Credit.main_code, Credit.code


    Martina White

    Sunday, October 06, 2013 12:42 AM
  • The result of the above query is wrong. I am getting all 15 rows from the above query.

    Please see the following @ variable/criteria which needs in the query to get the desired results.

    @code_AAAA should have @cost_code_AAAA=60.5, @code_BBBB should have @cost_code_BBB=100,@code_CCCC should have @cost_code_BBBB=80 and code_DDDD should have @cost_code_DDDD=75 but all should match in any same main_code otherwise result should be nothing.

    DECLARE @code_AAAA varchar(4);
    Select @code_AAAA ='AAAA'
    DECLARE @cost_code_AAAA decimal(5,2);
    Select @cost_code_AAAA=60.5

    DECLARE @code_BBBB varchar(4);
    Select @code_BBBB='BBBB'
    DECLARE @cost_code_BBBB decimal(5,2);
    Select @cost_code_BBBB=100

    DECLARE @code_CCCC varchar(4);
    Select @code_CCCC='CCCC'
    DECLARE @cost_code_CCCC decimal(5,2);
    Select @cost_code_CCCC=80

    DECLARE @code_DDDD varchar(4);
    Select @code_DDDD='DDDD'
    DECLARE @cost_code_DDDD decimal(5,2);
    Select @cost_code_DDDD=75

    --Expected output as per above set criteria
    main_code    code    code1    date1        cost_code
    AS            AAAA    1       2012-01-01     60.50
    AS            BBBB    2       2012-01-01     100.00
    AS            CCCC    2       2012-01-01     80.00
    AS            DDDD    3       2010-01-01     75.00
    AS            EEEE    2       2010-01-01     10.00


    • Edited by Kenny_Gua Sunday, October 06, 2013 1:01 AM
    Sunday, October 06, 2013 12:54 AM
  • Sorry, I misunderstood your requirements. This will get the results you specify.  But please note that you need to specify an ORDER BY in the Partition.  You cannot do this simply on the order in which items appear in the table.  If sorting by Main_Code descending is not acceptable, you will need to specify something in your dataset that gets you the desired results.  Perhaps adding a column which holds the sort value for Main_Code. 

    SELECT Main_Code, code, date1, cost_code FROM
    (SELECT ROW_NUMBER() 
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row, 
    Main.Main_code, 
    Main.code, 
    Main.date1, 
    Main.cost_code 
    FROM #Credit as Main 
     INNER JOIN
     (
     SELECT code, MAX(date1) as date1
     FROM #credit
     GROUP BY code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1
    


    Martina White

    Sunday, October 06, 2013 1:15 AM
  • Hi, The above query is not correct and not getting the desired output. I just changed the date to see that this query is working correctly or not. Please see the below INSERT into... where I changed the data to see the result and getting result wrong from the above query.

    --RESULT is wrong

    Main_Code    code    date1    cost_code
    AB            AAAA    2013-01-01 00:00:00.000    61.50
    AB            BBBB    2013-01-01 00:00:00.000    90.00
    AS            CCCC    2012-01-01 00:00:00.000    80.00
    AS            DDDD    2010-01-01 00:00:00.000    75.00
    AS            EEEE    2010-01-01 00:00:00.000    10.00

    Actually we should use the  @ variable/criteria in the query to get the desired results.

    @code_AAAA (colum is code) should have @cost_code_AAAA (column is cost_code)=60.5

    @code_BBBB (colum is code) should have @cost_code_BBBB (column is cost_code)=100

    @code_CCCC (colum is code) should have @cost_code_CCCC (column is cost_code)=80

    @code_DDDD (colum is code) should have @cost_code_DDDD (column is cost_code)=75
    but all should match in any same main_code column otherwise result should be nothing.

    insert into #credit values ('AS','AAAA','1','2010-01-01',50.5)

    insert into #credit values ('AS','AAAA','1','2012-01-01',60.5)
    insert into #credit values ('AS','BBBB','2','2012-01-01',100)
    insert into #credit values ('AS','CCCC','2','2012-01-01',80)
    insert into #credit values ('AS','DDDD','3','2009-01-01',50)
    insert into #credit values ('AS','DDDD','3','2010-01-01',75)
    insert into #credit values ('AS','EEEE','2','2010-01-01',10)

    insert into #credit values ('AB','AAAA','1','2013-01-01',61.5)
    insert into #credit values ('AB','BBBB','2','2013-01-01',90)
    insert into #credit values ('AB','CCCC','2','2012-01-01',80)
    insert into #credit values ('AB','DDDD','3','2009-01-01',75)
    insert into #credit values ('AB','DDDD','3','2010-01-01',55)
    insert into #credit values ('AB','EEEE','2','2010-01-01',20)

    insert into #credit values ('AC','AAAA','1','2010-01-01',80.5)
    insert into #credit values ('AC','BBBB','2','2012-01-01',100)
    insert into #credit values ('AC','CCCC','2','2012-01-01',80)
    insert into #credit values ('AC','DDDD','3','2009-01-01',55)
    insert into #credit values ('AC','EEEE','2','2010-01-01',20)

    --Expected output as per above set criteria
    main_code    code    code1    date1        cost_code
    AS            AAAA    1       2012-01-01     60.50
    AS            BBBB    2       2012-01-01     100.00
    AS            CCCC    2       2012-01-01     80.00
    AS            DDDD    3       2010-01-01     75.00
    AS            EEEE    2       2010-01-01     10.00


    Sunday, October 06, 2013 2:17 AM
  • I'm sorry, I don't understand what you are asking for. 

    You description: "These code and cost_code both should match with any set of main_code. If any code has different cost_code in same main_code then should pick max(effdate) record". This says to me that you want to choose the max(effdate) if there are multiple records for a code/cost code within a main_code the choose the one with the max date.  That is the first query I gave you.   

    Your code below doesn't do anything. 

    DECLARE @code varchar(4);
    Select @code='AAAA'
    DECLARE @cost_code_AAAA decimal(5,2);
    Select @cost_code_AAAA=60.5

    There is no select statement to return any results, so I have no idea what results you are trying to get.  The second query I wrote returns the results you asked for.  If you would like to write some pseudo code for a select statement trying to get the results you are looking for I can try to help you.   As it stands it is not clear what you are asking for.


    Martina White

    Sunday, October 06, 2013 4:20 AM
  • Hi, Sorry but I think if the following condition is included in your query in WHERE CLAUSE then can get desired result. but all should match in any same main_code column otherwise result should be nothing.

    /*The following is the condition which should include in query in WHERE CLAUSE---I also used the following condition in your query but syntax will not be correct. Please help on this query so that I can get desired result.Thanks.*/

    ------------

    @code_AAAA (colum is code) should have @cost_code_AAAA (column is cost_code)=60.5

    @code_BBBB (colum is code) should have @cost_code_BBBB (column is cost_code)=100

    @code_CCCC (colum is code) should have @cost_code_CCCC (column is cost_code)=80

    @code_DDDD (colum is code) should have @cost_code_DDDD (column is cost_code)=75

    --------------

    SELECT Main_Code, code, date1, cost_code FROM
    (SELECT ROW_NUMBER()
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row,
    Main.Main_code,
    Main.code,
    Main.date1,
    Main.cost_code
    FROM #Credit as Main
     INNER JOIN
     (
     SELECT code, MAX(date1) as date1
     FROM #credit
     GROUP BY code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1

    Where (@code_AAAA='AAAA' and @cost_code_AAAA=60.5)

    AND     (@code_BBBB='BBBB' and @cost_code_BBBB=100)

    AND     (@code_CCCC='CCCC' and @cost_code_CCCC=80)

    AND    (@code_DDDD='DDDD' and @cost_code_DDDD=75)

    --Expected output
    main_code    code    code1    date1        cost_code
    AS            AAAA    1       2012-01-01     60.50
    AS            BBBB    2       2012-01-01     100.00
    AS            CCCC    2       2012-01-01     80.00
    AS            DDDD    3       2010-01-01     75.00
    AS            EEEE    2       2010-01-01     10.00

    • Edited by Kenny_Gua Sunday, October 06, 2013 5:40 AM
    Sunday, October 06, 2013 5:09 AM
  • Hi, Sorry but I think if the following condition is included in your query in WHERE CLAUSE then can get desired result. but all should match in any same main_code column otherwise result should be nothing.

    /*The following is the condition which should include in query in WHERE CLAUSE---I also used the following condition in your query in the bottom but I know syntax are not correct. Please help on this query so that I can get desired result.Thanks.*/

    ------------

    @code_AAAA (colum is code) should have @cost_code_AAAA (column is cost_code)=60.5

    @code_BBBB (colum is code) should have @cost_code_BBBB (column is cost_code)=100

    @code_CCCC (colum is code) should have @cost_code_CCCC (column is cost_code)=80

    @code_DDDD (colum is code) should have @cost_code_DDDD (column is cost_code)=75

    --------------

    SELECT Main_Code, code, date1, cost_code FROM
    (SELECT ROW_NUMBER()
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row,
    Main.Main_code,
    Main.code,
    Main.date1,
    Main.cost_code
    FROM #Credit as Main
     INNER JOIN
     (
     SELECT code, MAX(date1) as date1
     FROM #credit
     GROUP BY code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1

    Where (@code_AAAA='AAAA' and @cost_code_AAAA=60.5)

    AND     (@code_BBBB='BBBB' and @cost_code_BBBB=100)

    AND     (@code_CCCC='CCCC' and @cost_code_CCCC=80)

    AND    (@code_DDDD='DDDD' and @cost_code_DDDD=75)

    --Expected output
    main_code    code    code1    date1        cost_code
    AS            AAAA    1       2012-01-01     60.50
    AS            BBBB    2       2012-01-01     100.00
    AS            CCCC    2       2012-01-01     80.00
    AS            DDDD    3       2010-01-01     75.00
    AS            EEEE    2       2010-01-01     10.00

    Sunday, October 06, 2013 11:05 AM
  • I'm still not totally clear on your description. You say, "all should match in any same main_code column otherwise result should be nothing.", and yet there is no variable for EEEE but is inlcuded in your output.  Below is my next attempt at understanding what you are looking for, but EEEE is not included in the output.  Please clarify further.

    SELECT Main_Code, code, date1, cost_code FROM
    (SELECT ROW_NUMBER() 
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row, 
    Main.Main_code, 
    Main.code, 
    Main.date1, 
    Main.cost_code 
    FROM #Credit as Main 
     INNER JOIN
     (
     SELECT code, MAX(date1) as date1
     FROM #credit
     WHERE
     ((code='AAAA' and cost_code=60.5) OR
         (code='BBBB' and cost_code=100) OR
         (code='CCCC' and cost_code=80) OR
         (code='DDDD' and cost_code=75))
     GROUP BY code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1

    Please note that rather than hard coding the values in the query I might recommend storing them in a table and doing an join to that new table in the above query.

    Martina White


    Sunday, October 06, 2013 12:25 PM
  • This is the table join I was referring to.

    create table #values (code char(10),cost_code decimal(5,2))
     insert into #values values ('AAAA',60.5)
     insert into #values values ('BBBB',100)
     insert into #values values ('CCCC',80)
     insert into #values values ('DDDD',75)
    
     
    SELECT Main_Code, code, code1, date1, cost_code
     FROM
    (SELECT ROW_NUMBER() 
          OVER(Partition By Main.Code ORDER BY Main.Main_Code DESC) AS Row, 
    Main.Main_code, 
    Main.code, 
    Main.date1,
    Main.code1, 
    Main.cost_code 
    FROM #Credit as Main 
     INNER JOIN
     (
     SELECT #credit.code, MAX(date1) as date1
     FROM #credit INNER JOIN
     #values ON #credit.code = #values.code AND #credit.cost_code = #values.cost_code
     GROUP BY #credit.code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1


    Martina White


    • Edited by Martina White Sunday, October 06, 2013 1:56 PM
    • Marked as answer by Kenny_Gua Sunday, October 06, 2013 3:22 PM
    Sunday, October 06, 2013 1:56 PM
  • Hi, It's working perfectly.

    Thank you so much for your help.

    Thank you gain.

    Sunday, October 06, 2013 3:23 PM