Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

LATEST DATE IN A QUERY

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;
0
SOUTHAMERICA70
Asked:
SOUTHAMERICA70
  • 2
1 Solution
 
mbizupCommented:
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

0
 
als315Commented:
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;
0
 
aikimarkCommented:
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

0
 
mbizupCommented:
SOUTHAMERICA70,

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now