Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Max Function Output Mapping to get Description Expand / Collapse
Author
Message
Posted Monday, January 6, 2014 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:22 AM
Points: 18, Visits: 44
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.
Post #1528065
Posted Monday, January 6, 2014 6:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1528069
Posted Monday, January 6, 2014 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Post #1528080
Posted Monday, January 6, 2014 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:22 AM
Points: 18, Visits: 44
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')
Post #1528099
Posted Monday, January 6, 2014 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Post #1528107
Posted Monday, January 6, 2014 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:22 AM
Points: 18, Visits: 44
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.

Post #1528110
Posted Monday, January 6, 2014 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Post #1528116
Posted Monday, January 6, 2014 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:22 AM
Points: 18, Visits: 44
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!
Post #1528122
Posted Monday, January 6, 2014 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Post #1528125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse