Sql-server – Query to find the ‘Branch_Code’ of the ‘Channel_Num’ with the lowest ‘Issue_Date’ using T-SQL

querysql server 2014t-sql

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

Best Answer

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

Related Solutions

SQL Server – Query to Subtract Each Column from the Following Column

You could use LAG() to subtract the value from the previous value, ordered by DateKey.

SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V;

But this will give you an extra NULL for the first value since we are doing 100 - NULL (Non existing row since the row with val 100 is the first one).

One way around this is by removing the NULL with a CTE

;WITH CTE
AS
(
SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V
)

SELECT * FROM CTE 
where val IS NOT NULL;

Result

ProjectCode val
A   20
A   20

Oracle 11g R2 – Alternatives to Full Outer Join

If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox.

When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both modern_transaction and card_transaction are children of. Then you'd just need to do a left outer join to both children from the parent.

A full outer join isn't going to produce false results. It will produce exactly the results you ask for. As with any code, however, that may not be the results you actually want. In your case, what is the primary key of each table? Eeffective_date is a date and dates always store time down to the second. Are you storing the actual date there? Or are you truncating the date and storing the time as midnight?

If the primary key is Eeffective_date, customer_num, branch_code and you are storing the time as midnight, that implies that each customer can only do one of each transaction at each branch every day. It seems unlikely that a customer couldn't do two transactions in one day.
If the primary key is Eeffective_date, customer_num, branch_code and you are storing the actual time, then customers can do multiple transactions every day but it would be likely that some transactions that should match wouldn't match in your join because one table was inserted into with a value just before the change of a second and the other was inserted into with a value just after the change.
If the primary key is something else, then a single row from t1 might match multiple rows from t2 and vice versa. The full outer join will do exactly what you're asking but it is extremely rare that this would be what you want to happen.

Best Answer

Related Solutions

SQL Server – Query to Subtract Each Column from the Following Column

Oracle 11g R2 – Alternatives to Full Outer Join

Related Question