Logic that looks at min value of one field and populates value of another field
-
Hello everyone,
I am trying to understand how to build a logic in SQL which will create a field "ACTUAL_SHIPPER_CODE_LOAD" that will be populating the value of S1SCDA field but only the first row of it.
Like in this example, all fields for this MBOL_JN should equal to 15 because it is the first row appearing.
I believe in SQL it should be some kind of logic that will look at the first row taking into consideration the 1st row of MBOL_JN and the min value of LD_JN.
I have filtered down to only one BOL but obviously there will be many more so it should look 1) new MBOL_JN appearing and then 2) first min value of LD_JN.
Any ideas how it can be done?
SELECT distinct REPLACE(MASTER_BOL_NUMBER_WK_DC, ' ', '') AS 'MBOL_JN',
LTRIM(RTRIM(AR_LOAD_WK_DC)) AS 'LD_JN',
S1SCDA,
ACT_SHIPPER_NAME
FROM WF_AR_MBOL
WHERE SORT_ORDER=1
GROUP BY MASTER_BOL_NUMBER_WK_DC, AR_LOAD_WK_DC, S1SCDA, ACT_SHIPPER_NAME, MASTER_BOL_NUMBER
-
If you are hoping for a coded solution, please provide some sample data in the form of consumable CREATE TABLE and INSERT statements, along with desired results based on that sample data.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
@Phil Parkin I cannot provide such information. I don't know how and I am not sure I am allowed.
I managed to do something similar to what I need but it doesn't work completely correctly.
For some reason MIN window function () doesn't return the value that I need.
It should be all Old Dominion - Ogden for all of them.
SELECT distinct REPLACE(MASTER_BOL_NUMBER_WK_DC, ' ', '') AS 'MBOL_JN',
LTRIM(RTRIM(AR_LOAD_WK_DC)) AS 'LD_JN',
S1SCDA,
ACT_SHIPPER_NAME,
MIN(S1SCDA) OVER (PARTITION BY REPLACE(MASTER_BOL_NUMBER_WK_DC, ' ', '') ORDER BY LTRIM(RTRIM(AR_LOAD_WK_DC)) ) AS 'ACTUAL_SHIPPER_CODE_LOAD',
MIN(ACT_SHIPPER_NAME) OVER (PARTITION BY REPLACE(MASTER_BOL_NUMBER_WK_DC, ' ', '') ORDER BY LTRIM(RTRIM(AR_LOAD_WK_DC)) ) AS 'ACTUAL_SHIPPER_LOAD'
FROM WF_AR_MBOL
WHERE SORT_ORDER=1
GROUP BY MASTER_BOL_NUMBER_WK_DC, AR_LOAD_WK_DC, S1SCDA, ACT_SHIPPER_NAME, MASTER_BOL_NUMBER -
Please read this article for details showing how to post sample data.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
As Phil said, provide an easy digestable testcase. So it can run on a test system to reproduce the solution. You've already shared a screenshot
You can mock the tablenames/values as long this issue crops up.
-
I think you are probably looking for RANK() OVER(PARTITION BY MBOL_LN ORDER BY LD_JN) AS [SEQ_NO]
This will give you a sequential RANK value which resets each time MOBL_LN changes. If you set this up in a CTE you can then filter by SEQ_NO = 1. This gives you the FIRST line for each MOBL_LN. If you then need to get MIN(S1SCDA) you can do that separately.
Break the problen down into its component parts using CTEs and then put all the CTEs together. most of the time the query engine will still produce a close-to-optimal plan, it just makes it easier to test and read.
-
I think you need the value from the row with the min AR_LOAD_WK_DC, not the min values of the shipper code and name. If you change your code to use FIRST_VALUE instead of MIN, you may get what you need. If trimming the columns and replacing spaces doesn't change the order or uniqueness, then you don't need to do it in the windowed functions.
FIRST_VALUE(S1SCDA) OVER (PARTITION BY MASTER_BOL_NUMBER_WK_DC ORDER BY AR_LOAD_WK_DC) AS ACTUAL_SHIPPER_CODE_LOAD
As you need multiple columns from the first row, I would probably join to a row_number query, rather than use two window functions. Rank would also work in this case, and if you like cte's you can put the row_number query in a cte. I assume you need the sort_order filter as it was in your original query.
SELECT REPLACE(a.MASTER_BOL_NUMBER_WK_DC, ' ', '') AS 'MBOL_JN',
LTRIM(RTRIM(a.AR_LOAD_WK_DC)) AS 'LD_JN',
a.S1SCDA,
a.ACT_SHIPPER_NAME,
b.S1SCDA AS ACTUAL_SHIPPER_CODE_LOAD,
b.ACT_SHIPPER_NAME AS ACTUAL_SHIPPER_LOAD,
FROM WF_AR_MBOL AS a
JOIN ( SELECT MASTER_BOL_NUMBER_WK_DC, S1SCDA, ACT_SHIPPER_NAME,
ROW_NUMBER() OVER (PARTITION BY MASTER_BOL_NUMBER_WK_DC
ORDER BY AR_LOAD_WK_DC) AS rn
FROM WF_AR_MBOL
WHERE SORT_ORDER=1
) AS b ON a.MASTER_BOL_NUMBER_WK_DC = b.MASTER_BOL_NUMBER_WK_DC
AND b.rn = 1
WHERE a.SORT_ORDER = 1 -
Ive been writing t-SQL code for 25 years and NEVER come across FIRST_VALUE, LAST_VALUE. I have always written a RANK() OVER() or a MIN() OVER() in a CTE and then joined back to to the CTE record. You just made future code MUCH easier to read,
#AlwaysLearning
- aaron.reese wrote:
Ive been writing t-SQL code for 25 years and NEVER come across FIRST_VALUE, LAST_VALUE. I have always written a RANK() OVER() or a MIN() OVER() in a CTE and then joined back to to the CTE record. You just made future code MUCH easier to read,
#AlwaysLearning
You have to be careful with
LAST_VALUE(). The default window for bothFIRST_VALUE()andLAST_VALUE()isROWS UNBOUNDED PRECEDING. This is fine forFIRST_VALUE(), but returns the current row's value forLAST_VALUE(), which is generally not what is expected.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply