Max Function Output Mapping to get Description

Question

Max Function Output Mapping to get Description

waheed71

Old Hand

Points: 362
More actions
January 6, 2014 at 6:06 am

#302400

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 SSC Guru Points: 186127 More actions · Answer 2

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

waheed71 Old Hand Points: 362 More actions · Answer 3

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 SSC Guru Points: 186127 More actions · Answer 4

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

waheed71 Old Hand Points: 362 More actions · Answer 5

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 SSC Guru Points: 186127 More actions · Answer 6

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

waheed71 Old Hand Points: 362 More actions · Answer 7

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 SSC Guru Points: 186127 More actions · Answer 8

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