Select Min Date from CASE statement for date

[tootergray34]

I have 3 tables where I want to grab a date from and I made a case statement for it. Is it possible for me to grab the Min date as I get multiple values for the date and I only want to grab that one? Here is my code so far. Thanks for anyone if they can help.

SELECT PURC_ORDER_LINE.PART_ID, PURC_ORDER_LINE.TOTAL_USR_RECD_QTY, PURC_ORDER_LINE.ORDER_QTY, PURCHASE_ORDER.STATUS, 
                  PURCHASE_ORDER.VENDOR_ID, CASE WHEN PURC_LINE_DEL.DESIRED_RECV_DATE IS NOT NULL 
                  THEN PURC_ORDER_LINE.DESIRED_RECV_DATE WHEN PURC_ORDER_LINE.DESIRED_RECV_DATE IS NOT NULL 
                  THEN PURC_ORDER_LINE.DESIRED_RECV_DATE ELSE PURCHASE_ORDER.DESIRED_RECV_DATE END AS NextPoReceiptDate, PURCHASE_ORDER.ID, 
                  PURC_ORDER_LINE.LINE_NO
FROM     PURC_ORDER_LINE INNER JOIN
                  PURCHASE_ORDER ON PURC_ORDER_LINE.PURC_ORDER_ID = PURCHASE_ORDER.ID LEFT OUTER JOIN
                  PURC_LINE_DEL ON PURC_ORDER_LINE.PURC_ORDER_ID = PURC_LINE_DEL.PURC_ORDER_ID AND 
                  PURC_ORDER_LINE.LINE_NO = PURC_LINE_DEL.PURC_ORDER_LINE_NO
WHERE  (PURC_ORDER_LINE.ORDER_QTY > PURC_ORDER_LINE.TOTAL_USR_RECD_QTY) AND (PURCHASE_ORDER.STATUS = 'R') AND 
                  (PURC_ORDER_LINE.PART_ID = 'FIRST ARTICLE')
ORDER BY NextPoReceiptDate DESC

 

[mdlueck]

Is what you are looking for....

SELECT TOP 1
...
ORDER BY NextPoReceiptDate DESC

????

 

[tootergray34]

I understand select TOP 1, and I know i'm only selecting one value for the Part ID but i'm just testing it for that one, so that would not work in this case.

 

[tootergray34]

There will be multiple values for the Part ID

 

[mdlueck]

The latest place I implemented something like this, where multiple entries could exist in a place which normally should be uniqueness required, I implemented a check in the INSERT Stored Procedure to first check for a key violation, and if an existing record is found then it skips performing the INSERT.

Then emphasis went to insuring the SELECT was ORDER BY'ed correctly to always have selected the preferred record first, and the duplicates may safely be skipped.

I do not think I have an example of how to perform this in the context of a single SELECT statement. Perhaps someone else will have a suggestion of how to accomplish that.

 

[mdlueck]

Oh... an idea just came to mind...

Perhaps you could join the one table to itself in the CTE virtual table instance perform the ORDER BY to have the preferred record sort to the top.

I have a report which was suppose to report on distinct records from one table. Records from that table may be associated with many records in another table. From that other table, needed to select one of the records... did not really matter which one... query was suppose to return "DISTINCT records and on by the way and this is the corresponding data field of one associated record in this other table." I will paste in the boiler plate of this CTE solution below:

-- Define the CTE expression name and column list.
WITH [tools_CTE] ([umetid],
                  [uassetnum])
AS
-- Define the CTE query.
(
    SELECT DISTINCT [met].[id] AS [umetid],
                    [met].[assetnum] AS [uassetnum]
    FROM [dbo].[projects] AS [proj]
    INNER JOIN [dbo].[products] AS [prod] ON [proj].[id] = [prod].[projectid]
    INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid]
    INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id]
    INNER JOIN [dbo].[partmetoolinglink] AS [metl] ON [p].[id] = [metl].[partid]
    INNER JOIN [dbo].[metooling] AS [met] ON [metl].[metoolingid] = [met].[id]
    WHERE [proj].[id] = @projid
)
-- Define the outer query referencing the CTE name.
SELECT [tools_CTE].[umetid] AS [metid],
       [tr].[tooltypesort],
       [tr].[tooltypetitle],
       [tr].[partnumber],
       [tr].[mettitle],
       [tools_CTE].[uassetnum] AS [assetnum],
       [tr].[toolbudget],
       [tr].[exptoolcost],
       [tr].[toolcost],
       [tr].[toolponumber],
       [tr].[besttoolcost],
       [tr].[toolbudget] - [tr].[besttoolcost] AS [besttoolcostdifference]
FROM [tools_CTE]
OUTER APPLY (
  SELECT TOP 1 [met].[id] AS [metid],
               [met].[tooltypeid],
               [mettt].[sort] AS [tooltypesort],
               [mettt].[title] AS [tooltypetitle],
               [p].[partnumber],
               [met].[title] AS [mettitle],
               [met].[toolbudget],
               [met].[exptoolcost],
               [met].[toolcost],
               [met].[toolponumber],
               CASE
                 WHEN [met].[toolcost] IS NOT NULL THEN [met].[toolcost]
                 WHEN [met].[exptoolcost] IS NOT NULL THEN [met].[exptoolcost]
                 WHEN [met].[toolbudget] IS NOT NULL THEN [met].[toolbudget]
                 ELSE 0
               END AS [besttoolcost]
  FROM [dbo].[projects] AS [proj]
  INNER JOIN [dbo].[products] AS [prod] ON [proj].[id] = [prod].[projectid]
  INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid]
  INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id]
  INNER JOIN [dbo].[partmetoolinglink] AS [metl] ON [p].[id] = [metl].[partid]
  INNER JOIN [dbo].[metooling] AS [met] ON [metl].[metoolingid] = [met].[id]
  INNER JOIN [dbo].[metoolingtooltype] AS [mettt] ON [met].[tooltypeid] = [mettt].[id]
  WHERE [proj].[id] = @projid
  AND [met].[id] = [tools_CTE].[umetid]
  ORDER BY [p].[partnumber]
) AS [tr]
ORDER BY [tooltypesort],
         [partnumber];

 

[tootergray34]

Going to try something else I thought of

 

[tootergray34]

Thank you Mr. L, I was thinking it would have to be something more like this. I'm decent at SQL but obviously not that great :/ Thank you for taking a look.

SELECT PURC_ORDER_LINE.PART_ID, PURC_ORDER_LINE.TOTAL_USR_RECD_QTY, PURC_ORDER_LINE.ORDER_QTY, PURCHASE_ORDER.STATUS, 
                  PURCHASE_ORDER.VENDOR_ID, CASE WHEN PURC_LINE_DEL.DESIRED_RECV_DATE IS NOT NULL 
                  THEN PURC_ORDER_LINE.DESIRED_RECV_DATE WHEN PURC_ORDER_LINE.DESIRED_RECV_DATE IS NOT NULL 
                  THEN PURC_ORDER_LINE.DESIRED_RECV_DATE ELSE PURCHASE_ORDER.DESIRED_RECV_DATE END AS NextPoReceiptDate, PURCHASE_ORDER.ID, 
                  PURC_ORDER_LINE.LINE_NO
FROM     PURC_ORDER_LINE INNER JOIN
                  PURCHASE_ORDER ON PURC_ORDER_LINE.PURC_ORDER_ID = PURCHASE_ORDER.ID LEFT OUTER JOIN
                  PURC_LINE_DEL ON PURC_ORDER_LINE.PURC_ORDER_ID = PURC_LINE_DEL.PURC_ORDER_ID AND 
                  PURC_ORDER_LINE.LINE_NO = PURC_LINE_DEL.PURC_ORDER_LINE_NO
WHERE  (PURC_ORDER_LINE.ORDER_QTY > PURC_ORDER_LINE.TOTAL_USR_RECD_QTY) AND (PURCHASE_ORDER.STATUS = 'R') AND 
                  (PURC_ORDER_LINE.PART_ID = 'FIRST ARTICLE') AND (NextPoReceiptDate IN
                      (SELECT MAX(NextPoReceiptDate) AS Expr1
                       FROM      PURC_ORDER_LINE AS PURC_ORDER_LINE_1 INNER JOIN
                                         PURCHASE_ORDER AS PURCHASE_ORDER_1 ON PURC_ORDER_LINE_1.PURC_ORDER_ID = PURCHASE_ORDER_1.ID LEFT OUTER JOIN
                                         PURC_LINE_DEL AS PURC_LINE_DEL_1 ON PURC_ORDER_LINE_1.PURC_ORDER_ID = PURC_LINE_DEL_1.PURC_ORDER_ID AND 
                                         PURC_ORDER_LINE_1.LINE_NO = PURC_LINE_DEL_1.PURC_ORDER_LINE_NO
                       WHERE   (PURC_ORDER_LINE_1.PART_ID = PURC_ORDER_LINE_1.PART_ID)))