Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max Function Output Mapping to get Description


Max Function Output Mapping to get Description

Author
Message
waheed71
waheed71
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 48
Dear All,

I have below mentioned query based on few MS SQL functions. I am able to get last vendor for the particular stock based on SQL Max and Over function but when I try to add description for the last vendor it is not appropriate.

Your prompt help will be highly appreciated!

Select TR_STOCKNO,CT_STOCKSUMMARY,PH_SUPPLIERCODE,SU_SUPPLIERNAME,PH_RAISEDDATE,TR_DATE,
MAX(PH_RAISEDDATE) OVER (PARTITION BY TR_STOCKNO) AS LASTPO,
MAX(PH_SUPPLIERCODE) OVER (PARTITION BY TR_STOCKNO) AS LASTVENDOR,
MAX(TR_DATE) OVER (PARTITION BY TR_STOCKNO) AS LASTGRN,
DATEDIFF(DAY,(MAX(PH_RAISEDDATE) OVER (PARTITION BY TR_STOCKNO)),(MAX(TR_DATE) OVER (PARTITION BY TR_STOCKNO)))As Age
From ST_TRANSACTIONS
Inner Join PO_PURCHASEHEADER on PO_PURCHASEHEADER.PH_ORDERNO=ST_TRANSACTIONS.TR_PONO
Inner Join ST_SUPPLIER on PO_PURCHASEHEADER.PH_SUPPLIERCODE=ST_SUPPLIER.SU_SUPPLIERCODE_P
Inner Join ST_COMMODITYTYPE ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
Where TR_TYPECODE = 'PREC'
Order by TR_STOCKNO



As per Query last supplier is '30-11-710' (General Supplier NOMAC) for the '01-01-01-001' stock code as per SQL Max function, which is correct but when I run the query by adding Supplier Description field (SU_SUPPLIERNAME), it is mixing with different suppliers and not getting proper result.

Please be noted that when I run this Query WITHOUT adding Supplier Description field, it gives me correct results.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14969 Visits: 38985
since you want date elements that are related to the same row where your max() is found, i think you want to use row_number with partition to organize the data, and treat that data as a subquery to get the specifics:
something like this?

SELECT
*
FROM (SELECT
TR_STOCKNO,
CT_STOCKSUMMARY,
PH_SUPPLIERCODE,
SU_SUPPLIERNAME,
PH_RAISEDDATE,
TR_DATE,
ROW_NUMBER()
OVER (
PARTITION BY TR_STOCKNO
ORDER BY TR_STOCKNO) AS RW,
PH_RAISEDDATE,
PH_SUPPLIERCODE,
SU_SUPPLIERNAME,
DATEDIFF(DAY, PH_RAISEDDATE, TR_DATE)AS Age
FROM ST_TRANSACTIONS
INNER JOIN PO_PURCHASEHEADER
ON PO_PURCHASEHEADER.PH_ORDERNO = ST_TRANSACTIONS.TR_PONO
INNER JOIN ST_SUPPLIER
ON PO_PURCHASEHEADER.PH_SUPPLIERCODE = ST_SUPPLIER.SU_SUPPLIERCODE_P
INNER JOIN ST_COMMODITYTYPE
ON ST_TRANSACTIONS.TR_STOCKNO = ST_COMMODITYTYPE.CT_STOCKNO_P
WHERE TR_TYPECODE = 'PREC'
) MySubQuery
WHERE RW = 1
ORDER BY
TR_STOCKNO



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
Slightly different to Lowell's code and I'm not sure which is correct:
SELECT 
d.TR_STOCKNO,
d.CT_STOCKSUMMARY,
d.PH_SUPPLIERCODE,
su.SU_SUPPLIERNAME,
d.PH_RAISEDDATE,
d.TR_DATE,
d.LASTPO,
d.LASTVENDOR,
d.LASTGRN,
d.Age
FROM (
SELECT
tr.TR_STOCKNO,
ct.CT_STOCKSUMMARY,
ph.PH_SUPPLIERCODE,
--SU_SUPPLIERNAME,
ph.PH_RAISEDDATE,
tr.TR_DATE,
MAX(ph.PH_RAISEDDATE) OVER (PARTITION BY tr.TR_STOCKNO) AS LASTPO,
MAX(ph.PH_SUPPLIERCODE) OVER (PARTITION BY tr.TR_STOCKNO) AS LASTVENDOR,
MAX(TR_DATE) OVER (PARTITION BY tr.TR_STOCKNO) AS LASTGRN,
DATEDIFF(DAY,
(MAX(ph.PH_RAISEDDATE) OVER (PARTITION BY tr.TR_STOCKNO)),
(MAX(tr.TR_DATE) OVER (PARTITION BY tr.TR_STOCKNO)))
As Age
FROM ST_TRANSACTIONS tr
INNER JOIN PO_PURCHASEHEADER ph
ON ph.PH_ORDERNO = tr.TR_PONO
INNER JOIN ST_COMMODITYTYPE ct
ON tr.TR_STOCKNO = ct.CT_STOCKNO_P
WHERE tr.TR_TYPECODE = 'PREC'
) d
INNER JOIN ST_SUPPLIER su
ON su.SU_SUPPLIERCODE_P = d.PH_SUPPLIERCODE



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
waheed71
waheed71
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 48
Dear ChrisM / Dear Lowell,

Thanks for the prompt reply. I am really sorry about not able to communicate properly. So far I am unable to get required result.

I just need Supplier Name & Supplier Code by MAX Statement (Rather than Supplier Code only). So far using ChrisM Query, results are same like mine query.

Could you please provide tip how to get exact name from SU_SUPPLIERNAME field which shall be equal to output from SQL MAX Function as LASTVENDOR (Which is Numeric field).

For example if LASTVENDOR = '30-11-710' and that record belongs to General Supplier NOMAC (Description or Supplier Name of '30-11-710')
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
waheed71 (1/6/2014)
Dear ChrisM / Dear Lowell,

Thanks for the prompt reply. I am really sorry about not able to communicate properly. So far I am unable to get required result.

I just need Supplier Name & Supplier Code by MAX Statement (Rather than Supplier Code only). So far using ChrisM Query, results are same like mine query.

Could you please provide tip how to get exact name from SU_SUPPLIERNAME field which shall be equal to output from SQL MAX Function as LASTVENDOR (Which is Numeric field).

For example if LASTVENDOR = '30-11-710' and that record belongs to General Supplier NOMAC (Description or Supplier Name of '30-11-710')


Change the join column on my query to whichever MAX column fits your requirement.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
waheed71
waheed71
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 48
Dear Chris,

Thank you. Can you please give me example of below text.

Change the join column on my query to whichever MAX column fits your requirement.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
waheed71 (1/6/2014)
Dear Chris,

Thank you. Can you please give me example of below text.

Change the join column on my query to whichever MAX column fits your requirement.



Sure.

Change this:
INNER JOIN ST_SUPPLIER su 
ON su.SU_SUPPLIERCODE_P = d.PH_SUPPLIERCODE



to this:
INNER JOIN ST_SUPPLIER su 
ON su.SU_SUPPLIERCODE_P = d.LASTVENDOR



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
waheed71
waheed71
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 48
Dear Chris,

Thanks for the prompt reply, apparently it seems to be perfect. Let me explore this in more details. Again Thank you very much.

Kind regards!
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
waheed71 (1/6/2014)
...Let me explore this in more details...


That's the ticket!

Thanks for the feedback.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search