none
Help in query please

    Question

  • Hi, I am not getting the desired result from query. Criteria is in #Code_values table
    which should match with #Group_credit table but 'Group_code' should be the same for all
    matching code and cost_code.

    If all three code of #Code_values exactly not matches with #Group_credit's code and cost_code then result will be null. date1 will be max(date1).



    create table #Group_credit (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into #Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into #Group_credit values ('10','BBBB','1','2013-02-02',0.5)

    insert into #Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.5)
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    create table #Code_values (code char(10),cost_code decimal(5,2))
     insert into #Code_values values ('AAAA',0.65)
     insert into #Code_values values ('BBBB',0.5)
     insert into #Code_values values ('CCCC',0.5)

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

    --Result from the query
    Group_code    code    code1    date1        cost_code
    11            AAAA    1       2013-03-01     0.65
    10            BBBB    1       2013-02-02     0.50
    11            CCCC    1       2013-03-01     0.50

    --Desired result
    Group_code    code    code1    date1        cost_code
    08            AAAA    1       2013-03-01     0.65
    08            BBBB    1       2013-02-02     0.50
    08            CCCC    1       2013-03-01     0.50

    Wednesday, October 09, 2013 10:31 PM

Answers

  •  ;With cteGroupCredit As
     (Select Group_code,code, code1,date1, cost_code,
       Row_Number() Over (Partition By Group_code,code, code1 Order By date1 Desc) As rn
     From #Group_credit),
     cteGroupCode As
     (Select g.Group_code, Count(*) As GroupCodeCount
     From #Code_values c
     Inner Join cteGroupCredit g On c.code = g.code And c.cost_code = g.cost_code And rn = 1
     Group By g.Group_code
     Having Count(*) = (Select Count(*) From #Code_values))
     Select *
     From #Code_values c
     Inner Join cteGroupCredit g On c.code = g.code And c.cost_code = g.cost_code And rn = 1
     Where g.Group_code In (Select cte.Group_code From cteGroupCode cte);

    Tom
    Saturday, October 12, 2013 4:23 AM

All replies

  • Remove the DESC from the OVER clause in the ROW_NUMBER() function.

    SELECT Group_code, code, code1, date1, cost_code
     FROM
    (SELECT ROW_NUMBER()
          OVER(Partition By Main.Code ORDER BY Main.Group_code) AS Row,
    Main.Group_code,
    Main.code,
    Main.date1,
    Main.code1,
    Main.cost_code
    FROM #Group_credit as Main
     INNER JOIN
     (
     SELECT #Group_credit.code, MAX(date1) as date1
     FROM #Group_credit INNER JOIN
     #Code_values ON #Group_credit.code = #Code_values.code AND #Group_credit.cost_code = #Code_values.cost_code
     GROUP BY #Group_credit.code
     ) as MostRecent
     ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
     WHERE Row = 1

    Tom

    Wednesday, October 09, 2013 10:39 PM
  • SELECT Group_code, code, code1, date1, cost_code
      FROM
     (
     SELECT ROW_NUMBER()
           OVER(Partition By Main.Code ORDER BY Main.Group_code ASC) AS Row,
     Main.Group_code,
     Main.code,
     Main.date1,
     Main.code1,
     Main.cost_code
     FROM #Group_credit as Main
      INNER JOIN
      (
      SELECT #Group_credit.code, MAX(date1) as date1
      FROM #Group_credit INNER JOIN
      #Code_values ON #Group_credit.code = #Code_values.code AND #Group_credit.cost_code = #Code_values.cost_code
      GROUP BY #Group_credit.code
      ) as MostRecent
      ON Main.code = MostRecent.code AND Main.date1 = MostRecent.date1) as T1
      WHERE Row = 1

    Wednesday, October 09, 2013 10:41 PM
  • Hi,

    I just changed the following data in #Group_credit and run the query and  found that queryis not working correctly because code-'CCCC' is not matching with the code of #Code_values table. In this case

    result should be nothing. but getting the following result which is wrong

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.70)  ----Changed the cost_code
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    --Result when run the query which is wrong

    Group_code    code    code1    date1    cost_code
    08            AAAA    1       2013-03-01     0.65
    08            BBBB    1       2013-02-02     0.50
    08            CCCC    1       2013-03-01    0.70  --this is wrong. cost_code of 'CCCC' is 0.50 in #Code_values

    --Desired result (should be Nothing because there all three cost_code are not matching in one 'Group_code')

    Wednesday, October 09, 2013 11:09 PM
  • Hi,

    I just changed the following data in #Group_credit and run the query and  found that query is not working correctly because code-'CCCC' is not matching with the code of #Code_values table. In this case result should be nothing. but getting the following result which is wrong. Please check the query and fix.

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.70)  ----I Changed the cost_code to check
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    --Result from the query which is wrong (Result should have been nothing)

    Group_code    code    code1    date1    cost_code
    08            AAAA    1       2013-03-01     0.65
    08            BBBB    1       2013-02-02     0.50
    08            CCCC    1       2013-03-01    0.70  --this is wrong. cost_code of 'CCCC' is 0.50 in #Code_values

    --Desired result (should be Nothing because there all three cost_code are not matching in one 'Group_code')
    Thursday, October 10, 2013 1:56 AM
  • This is perhaps more easy solution:

    DECLARE @Group_credit table  (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into @Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into @Group_credit values ('10','BBBB','1','2013-02-02',0.5)
    
    insert into @Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into @Group_credit values ('11','CCCC','1','2013-03-01',0.5)
    
    insert into @Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into @Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into @Group_credit values ('08','CCCC','1','2013-03-01',0.5)
    insert into @Group_credit values ('08','CCCC','1','2010-01-01',0.65)
    
    DECLARE @Code_values table  (code char(10),cost_code decimal(5,2))
     insert into @Code_values values ('AAAA',0.65)
     insert into @Code_values values ('BBBB',0.5)
     insert into @Code_values values ('CCCC',0.5)
     
     SELECT * FROM @Group_credit
     SELECT * FROM @Code_values
     
     SELECT D.* FROM @Code_values AS CV
     CROSS APPLY (SELECT TOP 1 * FROM @Group_credit AS GC
    			  WHERE CV.code=GC.code AND CV.cost_code=GC.cost_code
    			  ORDER BY date1 DESC
    			  
    			  )AS D


    Thanks, hsbal

    Thursday, October 10, 2013 2:40 AM
  • Hi, Still query is not working correctly. I just changed the data in @Group_credit table to see the output.

    The rule is : The data of @Code_values table (code and code_cost should match with the data of @Group_credit (code and code_cost) and show the results if 'Group_code' of @Group_credit  is common and date1 should be max(date1) if records are same.


    DECLARE @Group_credit table  (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into @Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into @Group_credit values ('10','BBBB','1','2013-02-02',0.5)

    insert into @Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into @Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into @Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into @Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into @Group_credit values ('08','CCCC','1','2013-03-01',0.75) --I changed the cost_code here to check
    insert into @Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    DECLARE @Code_values table  (code char(10),cost_code decimal(5,2))
     insert into @Code_values values ('AAAA',0.65)
     insert into @Code_values values ('BBBB',0.5)
     insert into @Code_values values ('CCCC',0.5)
     
     SELECT * FROM @Group_credit
     SELECT * FROM @Code_values
     
     SELECT D.* FROM @Code_values AS CV
     CROSS APPLY (SELECT TOP 1 * FROM @Group_credit AS GC
                  WHERE CV.code=GC.code AND CV.cost_code=GC.cost_code
                  ORDER BY date1 DESC
                  
                  )AS D

    --Result from the above query which is wrong (It shouldn't display any result because Group_code are not common)

    Group_code    code    code1    date1    cost_code
    08            AAAA    1       2013-03-01 00:00:00.000    0.65
    08            BBBB    1       2013-02-02 00:00:00.000    0.50
    11            CCCC    1       2013-03-01 00:00:00.000    0.50

    Note: If results are matched and Group_code are common then we should get result.


    • Edited by Kenny_Gua Thursday, October 10, 2013 3:35 AM
    Thursday, October 10, 2013 3:14 AM
  • Hi, Sorry but query is not working correctly. Actually the condition is: The all three 'code' and 'code_cost' of #code_values should match with #group_credit's column (code and code_cost) and consider the record of max(date1). BUT GROUP_CODE SHOULD BE COMMON if matches. It not all three matches with common GROUP_CODE of #Group_credit then should not be any result. Thanks.


    Thursday, October 10, 2013 11:30 AM
  • In that case can you show the output you want? Just writing..
    Thursday, October 10, 2013 3:38 PM
  • Hi,The condition is: The all three 'code' and 'code_cost' of #code_values should match with #group_credit's column (code and code_cost) and consider the record of max(date1). BUT GROUP_CODE SHOULD BE COMMON if matches. It not all three matches with common GROUP_CODE of #Group_credit then should not be any result. Thanks.

    I am providing three different output to check the result of query. I believe after some modification in the query, the query will provide the desired output.

    -----1-If the data is the following then should be the following output.I am only changing the data in #Group_credit  table. I am not changing anything in #Code_values.
    create table #Group_credit (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into #Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into #Group_credit values ('10','BBBB','1','2013-02-02',0.5)

    insert into #Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.5)
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    create table #Code_values (code char(10),cost_code decimal(5,2))
     insert into #Code_values values ('AAAA',0.65)
     insert into #Code_values values ('BBBB',0.5)
     insert into #Code_values values ('CCCC',0.5)

    --Desired result (All 'code' and 'cost_code' of #Code_values table has matched with  'code' and cost_code of #Group_credit with common 'Group_code')

    Group_code    code    code1    date1        cost_code
    08            AAAA    1       2013-03-01     0.65
    08            BBBB    1       2013-02-02     0.50
    08            CCCC    1       2013-03-01     0.50

    -----2-If the data is the following then should be the following output.I am only changing the data in #Group_credit table. I am not changing anything in #Code_values.

    create table #Group_credit (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into #Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into #Group_credit values ('10','AAAA','1','2013-01-01',0.65)
    insert into #Group_credit values ('10','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('10','CCCC','1','2010-01-01',0.75)
    insert into #Group_credit values ('10','CCCC','1','2012-01-01',0.50)

    insert into #Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.5)
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    create table #Code_values (code char(10),cost_code decimal(5,2))
     insert into #Code_values values ('AAAA',0.65)
     insert into #Code_values values ('BBBB',0.5)
     insert into #Code_values values ('CCCC',0.5)

    --Desired result (All 'code' and 'cost_code' of #Code_values table has matched with  'code' and cost_code of #Group_credit with common 'Group_code')
    Group_code    code    code1    date1        cost_code
    10            AAAA    1       2013-01-01     0.65
    10            BBBB    1       2013-02-02     0.50
    10            CCCC    1       2012-01-01     0.50

    -----3-If the data is the following then should be the following output.I am only changing the data in #Group_credit table. I am not changing anything in #Code_values.

    create table #Group_credit (Group_code char(10),code char(4), code1 char(4),date1 datetime, cost_code decimal(5,2))
    insert into #Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into #Group_credit values ('10','AAAA','1','2013-01-01',0.65)
    insert into #Group_credit values ('10','BBBB','1','2013-02-02',0.5)

    insert into #Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.75)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.55)
    insert into #Group_credit values ('08','CCCC','1','2010-01-01',0.65)

    create table #Code_values (code char(10),cost_code decimal(5,2))
     insert into #Code_values values ('AAAA',0.65)
     insert into #Code_values values ('BBBB',0.5)
     insert into #Code_values values ('CCCC',0.5)

    --Desired result (All 'code' and 'cost_code' of #Code_values table has not matched with  'code' and cost_code of #Group_credit with common 'Group_code'). So result will be nothing.

    No Result

    Friday, October 11, 2013 2:51 AM
  • Hi, Please help on the query to get the desired result. Thanks.
    Friday, October 11, 2013 12:04 PM
  • In your second case, why do you choose Group_code '10' and not Group_code '08'?

    Tom

    Friday, October 11, 2013 3:03 PM
  • Hi, Sorry, there was data error in second case which i have fixed for group_code-'10'. 

    'Code' and 'cost_code' of Group_code-'08' for table #Group_credit is not completely matching with #Code_values's 'code' and 'code_cost. ONLY 'CCCC' is not matching because of date1. max(date1) has cost_code-0.65.

    --2--

    insert into #Group_credit values ('10','AAAA','1','1901-01-01',1)
    insert into #Group_credit values ('10','AAAA','1','2013-01-01',0.65)
    insert into #Group_credit values ('10','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('10','CCCC','1','2010-01-01',0.75)
    insert into #Group_credit values ('10','CCCC','1','2012-01-01',0.50)

    insert into #Group_credit values ('11','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('11','CCCC','1','2013-03-01',0.5)

    insert into #Group_credit values ('08','AAAA','1','2013-03-01',0.65)
    insert into #Group_credit values ('08','BBBB','1','2013-02-02',0.5)
    insert into #Group_credit values ('08','CCCC','1','2013-03-01',0.5)
    insert into #Group_credit values ('08','CCCC','1','2014-01-01',0.65)  --I  have corrected the date1.

    create table #Code_values (code char(10),cost_code decimal(5,2))
     insert into #Code_values values ('AAAA',0.65)
     insert into #Code_values values ('BBBB',0.5)
     insert into #Code_values values ('CCCC',0.5)

    --Desired result (All 'code' and 'cost_code' of #Code_values table has matched with  'code' and cost_code of #Group_credit with common 'Group_code')
    Group_code    code    code1    date1        cost_code
    10            AAAA    1       2013-01-01     0.65
    10            BBBB    1       2013-02-02     0.50
    10            CCCC    1       2012-01-01     0.50

    Saturday, October 12, 2013 12:36 AM
  •  ;With cteGroupCredit As
     (Select Group_code,code, code1,date1, cost_code,
       Row_Number() Over (Partition By Group_code,code, code1 Order By date1 Desc) As rn
     From #Group_credit),
     cteGroupCode As
     (Select g.Group_code, Count(*) As GroupCodeCount
     From #Code_values c
     Inner Join cteGroupCredit g On c.code = g.code And c.cost_code = g.cost_code And rn = 1
     Group By g.Group_code
     Having Count(*) = (Select Count(*) From #Code_values))
     Select *
     From #Code_values c
     Inner Join cteGroupCredit g On c.code = g.code And c.cost_code = g.cost_code And rn = 1
     Where g.Group_code In (Select cte.Group_code From cteGroupCode cte);

    Tom
    Saturday, October 12, 2013 4:23 AM
  • Hi, Thanks a lot for this help.

    Thanks again.

    Saturday, October 12, 2013 1:25 PM