We help IT Professionals succeed at work.

LATEST DATE IN A QUERY

SOUTHAMERICA70
on
I wanted to get the latest date in this query string.....and show only a single result.

Essentially get the latest date from SOURCE_START_TS first and then get the latest date from the LATEST field.

That would yield one row of data verses 300 for example.....below is my query string....

SELECT DISTINCT BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.ITEM AS PN, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER AS SUPPLIER_CODE, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.ERP_NAME AS SUPPLIER, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.SOURCE_START_TS, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.SOURCE_START_TS AS LATEST
FROM BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V INNER JOIN BCDW_PROD_V_ER_BUSINESS_PARTNER_V ON BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER = BCDW_PROD_V_ER_BUSINESS_PARTNER_V.BUSINESS_PARTNER;
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try this:
SELECT TOP 1 BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.ITEM AS PN, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER AS SUPPLIER_CODE, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.ERP_NAME AS SUPPLIER, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.SOURCE_START_TS, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.SOURCE_START_TS AS LATEST
FROM BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V INNER JOIN BCDW_PROD_V_ER_BUSINESS_PARTNER_V ON BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER = BCDW_PROD_V_ER_BUSINESS_PARTNER_V.BUSINESS_PARTNER
ORDER BY BCDW_PROD_V_ER_BUSINESS_PARTNER_V.SOURCE_START_TS  DESC 

Open in new window

BRONZE EXPERT
Commented:
Try:
SELECT TOP 1 BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.ITEM AS PN, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER AS SUPPLIER_CODE, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.ERP_NAME AS SUPPLIER, BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.SOURCE_START_TS, BCDW_PROD_V_ER_BUSINESS_PARTNER_V.SOURCE_START_TS AS LATEST
FROM BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V INNER JOIN BCDW_PROD_V_ER_BUSINESS_PARTNER_V ON BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V.BUY_FROM_BUS_PARTNER = BCDW_PROD_V_ER_BUSINESS_PARTNER_V.BUSINESS_PARTNER
ORDER BY BCDW_PROD_V_ER_BUSINESS_PARTNER_V.SOURCE_START_TS DESC;
SILVER EXPERT
Top Expert 2014

Commented:
I've aliased the table names in this example.
SELECT Top 1 Item.ITEM AS PN, Item.BUY_FROM_BUS_PARTNER AS SUPPLIER_CODE, 
Partner.ERP_NAME AS SUPPLIER, Item.SOURCE_START_TS, Partner.SOURCE_START_TS AS LATEST

FROM BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V As Item INNER JOIN BCDW_PROD_V_ER_BUSINESS_PARTNER_V As Partner 
ON Item.BUY_FROM_BUS_PARTNER = Partner.BUSINESS_PARTNER

Where Item.SOURCE_START_TS = (Select Max(SOURCE_START_TS) From BCDW_PROD_V_ER_ITEM_PURCHASE_BP_V) 
Order By Partner.SOURCE_START_TS Desc

Open in new window

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
SOUTHAMERICA70,

Can you explain why you picked the response you did?  As far as I can tell it is identical to my earlier post.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.