0

Hope you're doing well

I have a table with the structure below :

Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date  datetime)

Some Example Data :

                  "Customer_Channel_Branch"
---------------------------------------------------------
Customer_Num     Channel_Num    Branch_Code    Issue_date  
    x                 1             1000         1990
    x                 2             2000         1991
    x                 3             3000         2000

There is another table with this structure below:

 Channel_general_Code:(Channel_Num  int , Channel_gnrl_Code  int)

some example data for this table:

            "Channel_general_Code"
-------------------------------------------
     Channel_Num     Channel_gnrl_Code
        1                 1
        2                 1
        3                 1

As you can see in the second table , all three Channel_Num --> (1,2,3) are grouped as one single channel . So what I want in the result set is this :

                    "result"
----------------------------------------------
Customer_Num     Channel_Num     Branch_Code
    x               1               1000           
    x               2               1000
    x               3               1000

for Channel_Nums that are considered as one , (have the same Channel_gnrl_Code in the second table), I need the Branch_Code of the Channel_Num with lowest Issue_date. I thought that I could use window functions

(`row_number() over(partition by Channel_gnrl_Code order  by Issue_date )`)

to write this query but it gave me a false result. I was wondering if you could help me with this.

Thanks in advance

  • No one knows any solutions for this? – Pantea May 3 at 9:07
1

Avoid Row_Number because first it process whole table then again you select row where rn=1 for each group

DECLARE @Customer_Channel_Branch TABLE (
    Customer_Num VARCHAR(10)
    ,Channel_Num INT
    ,Branch_Code INT
    ,Issue_date INT
    )

INSERT INTO @Customer_Channel_Branch
VALUES (
    'x'
    ,1
    ,1000
    ,1990
    )
    ,(
    'x'
    ,2
    ,2000
    ,1991
    )
    ,(
    'x'
    ,3
    ,3000
    ,2000
    )

DECLARE @Channel_general_Code TABLE (
    Channel_Num INT
    ,Channel_gnrl_Code INT
    )

INSERT INTO @Channel_general_Code
VALUES (
    1
    ,1
    )
    ,(
    2
    ,1
    )
    ,(
    3
    ,1
    );

WITH CTE
AS (
    SELECT a.*
        ,b.Channel_gnrl_Code
    FROM @Customer_Channel_Branch A
    INNER JOIN @Channel_general_Code B ON a.Channel_Num = b.Channel_Num
    )
SELECT c.Customer_Num
    ,c.Channel_Num
    ,ca.Branch_Code
FROM CTE C
CROSS APPLY (
    SELECT TOP 1 Branch_Code
    FROM CTE C1
    WHERE c.Customer_Num = c1.Customer_Num
        AND c.Channel_gnrl_Code = c1.Channel_gnrl_Code
    ORDER BY c.Issue_date
    ) ca
| improve this answer | |
  • Thanks for your answer . Are you saying that using row_number might may give a false result or something ? or does it just have performance issues? – Pantea May 9 at 8:01
0

I found a query to solve this : first of all I should say that I change the structure of the first table and addedChannel_gnrl_Code the first table Customer_Channel_Branch and then used the below query .

select t2.customer_number , 
       t2.Channel_Num,
       t1.Branch_Code,
       t1.Channel_gnrl_Code
from (
       select b.*
       from (select a.customer_number,
                    a.Channel_Num,
                    a.ch_name,
                    a.branch_code,
                    a.Channel_gnrl_Code,
                    a.issue_date,
                    row_number() over(partition by a.Channel_gnrl_Code order by  a.issue_date) r_num
             from Customer_Channel_Branch a) b
       where r_num = 1) t1 
inner join Customer_Channel_Branch t2 
 on t1.Channel_gnrl_Code = t2.Channel_gnrl_Code

Does anyone know any better solution? Is this query convenient in terms of performance?

Thanks in advance

| improve this answer | |
  • Do you think using the window function Row_Number might produce false result? – Pantea May 9 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.