how to select lowest value of column and corresponding date column

Question

how to select lowest value of column and corresponding date column

profoundhypnotic

SSC Enthusiast

Points: 188
More actions
May 11, 2022 at 4:46 pm

#4031253

I am trying to get the lowest cost for a part and the highest cost for a part. along with the dates that they occurred. I almost have what I want except that in my query I don't know how to associate the lowest cost with the correct date. I understand why (because that's what I told the query to do) but I am wondering how can I alter the query so that when the lowest cost is selected, it selects the corresponding date as well?
Data set example:
|Part |lowest_cost|date_received|
|--------- |--------------|-------------|
|846060| 28.373265 | 1/5/2022 |
|846060| 29.143835 | 2/28/2022 |
|846060| 27.588483 | 3/8/2022 |
|846060| 29.143835 | 4/25/2022 |
Desired output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference|
|---------|--------------|-----------------|---------------|-----------------------|------------|
|846060| 27.588483 | 3/8/2022 | 29.143835 | 4/25/2022 | 1.555405 |
current output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference|
|---------|--------------|-----------------|---------------|-----------------------|------------|
|846060| 27.588483 | 1/5/2022 | 29.143835 | 4/25/2022 | 1.555405 |
query I have currently:
select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost,
min(date_received) as First_date,
max(date_received) as Last_Date, (max(cost) - min(cost)) as Difference
from v_po_history
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS' and part = '846060'
group by part

Viewing 14 posts - 1 through 14 (of 14 total)

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

kaj SSCommitted Points: 1616 More actions · Answer 1

Something like this perhaps?

declare @v_po_history table (
    Part varchar(20),
    Lowest_Cost dec(9,6),
    Date_Received date
    )

insert into @v_po_history (Part, Lowest_Cost, Date_Received)
    values
        ('846060',28.373265,'20220105'),
        ('846060',29.143835,'20220228'),
        ('846060',27.588483,'20220308'),
        ('846060',29.143835,'20220425')

select distinct
    Part,
    lc.Lowest_Cost,
    lc.Date_Received,
    hc.Highest_Cost,
    hc.Last_Date_Received,
    hc.Highest_Cost - lc.Lowest_Cost as Difference

from @v_po_history vph
cross apply (
    select top(1) Date_Received, Lowest_Cost
    from @v_po_history 
    where Part = vph.Part
    order by Lowest_Cost ASC, Date_Received ASC
    ) lc
cross apply (
    select top(1) 
        Date_Received as Last_Date_Received, 
        Lowest_Cost as Highest_Cost
    from @v_po_history 
    where Part = vph.Part
    order by Lowest_Cost DESC, Date_Received DESC
    ) hc

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Here's another version.

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
    Part INT NOT NULL
   ,lowest_cost DECIMAL(19, 6) NOT NULL
   ,date_received DATE NOT NULL
);

INSERT #t
(
    Part
   ,lowest_cost
   ,date_received
)
VALUES
(846060, 28.373265, '20220105')
,(846060, 29.143835, '20220228')
,(846060, 27.588483, '20220308')
,(846060, 29.143835, '20220425');

WITH ordered
AS (SELECT t.Part
          ,t.lowest_cost
          ,t.date_received
          ,index1 = ROW_NUMBER() OVER (PARTITION BY t.Part ORDER BY t.lowest_cost)
          ,index2 = ROW_NUMBER() OVER (PARTITION BY t.Part ORDER BY t.lowest_cost DESC)
    FROM #t t)
SELECT o.Part
      ,lowest_cost = MAX(IIF(o.index1 = 1, o.lowest_cost, NULL))
      ,date_received = MAX(IIF(o.index1 = 1, o.date_received, NULL))
      ,highest_cost = MAX(IIF(o.index2 = 1, o.lowest_cost, NULL))
      ,last_date_received = MAX(o.date_received)
      ,diff = MAX(IIF(o.index2 = 1, o.lowest_cost, NULL)) - MAX(IIF(o.index1 = 1, o.lowest_cost, NULL))
FROM ordered o
--WHERE o.index1 = 1 OR o.index2 = 1
GROUP BY o.Part;

profoundhypnotic SSC Enthusiast Points: 188 More actions · Answer 3

@kaj I get a syntax error sql_err

This reply was modified 3 years, 4 months ago by profoundhypnotic.

profoundhypnotic SSC Enthusiast Points: 188 More actions · Answer 4

@phil parkin this doesn't even run. no errors or anything it just will not execute

ratbak Default port Points: 1423 More actions · Answer 5

PSQL? Are you using postgres? You posted in a SQL Server 2019 forum.

profoundhypnotic SSC Enthusiast Points: 188 More actions · Answer 6

@ratbak hello, it's not postgres. it's called pervasive. There are no pervasive forums and since this DB engine uses SQL I figured it would be alright to post. most of the syntax is the same but I didn't know where else to go

ratbak Default port Points: 1423 More actions · Answer 7

That's OK, but it's best to be clear about that so that volunteers know not to recommend platform-specific (TSQL) solutions.

Here's a tek-tips Pervasive forum: https://www.tek-tips.com/threadminder.cfm?pid=318

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

profoundhypnotic wrote:

@phil parkin this doesn't even run. no errors or anything it just will not execute

Runs fine for me. Maybe now you appreciate that different flavours of SQL have their own nuances and differences, once you go beyond the basics.

kaj SSCommitted Points: 1616 More actions · Answer 9

profoundhypnotic wrote:

@kaj I get a syntax error

Okay, since we now have learned that you're using Pervasive SQL (BTrieve based) that changes things.

I don't know anything about PSQL, but looking at the error message I'm guessing that it doesn't support locally declared table variables.

I used that as a means to provide test data for the select. The table variable name was derived from your post, so try to remove the @ in the table name, and see if the select can run on its own with your table.

The basic SELECT syntax of PSQL doesn't look too different from T-SQL at a glance, but I have no chance to test anything. That'll be up to you.

https://docs.actian.com/psql/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=sqlref/syntaxref.03.71.html

I don't think I see APPLY joins in there, so that will probably also cause problems with my query.

kaj SSCommitted Points: 1616 More actions · Answer 10

Instead of using the apply approach you can also try a reworked query, which works wth the minimal test data you provided:

declare @v_po_history table (
    Part varchar(20),
    Lowest_Cost dec(9,6),
    Date_Received date
    )

insert into @v_po_history (Part, Lowest_Cost, Date_Received)
    values
        ('846060',28.373265,'20220105'),
        ('846060',29.143835,'20220228'),
        ('846060',27.588483,'20220308'),
        ('846060',29.143835,'20220425');

with
    lc as (
        select top 1 
            Part, 
            Date_Received, 
            Lowest_Cost
        from @v_po_history 
        order by Lowest_Cost ASC, Date_Received ASC
    ),

    hc as (
        select top 1 
            Part,
            Date_Received as Last_Date_Received, 
            Lowest_Cost as Highest_Cost
        from @v_po_history 
        order by Lowest_Cost DESC, Date_Received DESC
    )

select distinct
    vph.Part,
    lc.Lowest_Cost,
    lc.Date_Received,
    hc.Highest_Cost,
    hc.Last_Date_Received,
    hc.Highest_Cost - lc.Lowest_Cost as Difference

from @v_po_history vph
inner join lc on lc.Part=vph.Part
inner join hc on hc.Part=vph.Part

When run on SQL Server (which is the only product I have access to), this yields the same result as my previous query that utilized the CROSS APPLY (which may not be available to you).

Part                 Lowest_Cost                    Date_Received Highest_Cost                   Last_Date_Received Difference
-------------------- ------------------------------ ------------- ------------------------------ ------------------ ------------------------------
846060               27.588483                      2022-03-08    29.143835                      2022-04-25         1.555352

(1 row affected)

I have left the declared table variable with testdata for my own convenience, but you'll probably have to remove the @ from the table name and only concern yourself with the query itself.

This reply was modified 3 years, 4 months ago by kaj. Reason: TYPO: prived --> provided

drew.allen SSC Guru Points: 77014 More actions · Answer 11

kaj wrote:

Instead of using the apply approach you can also try a reworked query, which works wth the minimal test data you provided:

declare @v_po_history table (
    Part varchar(20),
    Lowest_Cost dec(9,6),
    Date_Received date
    )

insert into @v_po_history (Part, Lowest_Cost, Date_Received)
    values
        ('846060',28.373265,'20220105'),
        ('846060',29.143835,'20220228'),
        ('846060',27.588483,'20220308'),
        ('846060',29.143835,'20220425');

with
    lc as (
        select top 1 
            Part, 
            Date_Received, 
            Lowest_Cost
        from @v_po_history 
        order by Lowest_Cost ASC, Date_Received ASC
    ),

    hc as (
        select top 1 
            Part,
            Date_Received as Last_Date_Received, 
            Lowest_Cost as Highest_Cost
        from @v_po_history 
        order by Lowest_Cost DESC, Date_Received DESC
    )

select distinct
    vph.Part,
    lc.Lowest_Cost,
    lc.Date_Received,
    hc.Highest_Cost,
    hc.Last_Date_Received,
    hc.Highest_Cost - lc.Lowest_Cost as Difference

from @v_po_history vph
inner join lc on lc.Part=vph.Part
inner join hc on hc.Part=vph.Part

When run on SQL Server (which is the only product I have access to), this yields the same result as my previous query that utilized the CROSS APPLY (which may not be available to you).

Part                 Lowest_Cost                    Date_Received Highest_Cost                   Last_Date_Received Difference
-------------------- ------------------------------ ------------- ------------------------------ ------------------ ------------------------------
846060               27.588483                      2022-03-08    29.143835                      2022-04-25         1.555352

(1 row affected)

I have left the declared table variable with testdata for my own convenience, but you'll probably have to remove the @ from the table name and only concern yourself with the query itself.

This query will only work if there is exactly one part. I added one more record for a different part, and now I am getting no records at all.

insert into @v_po_history (Part, Lowest_Cost, Date_Received)
    values
        ('846060',28.373265,'20220105'),
        ('846060',29.143835,'20220228'),
        ('846060',27.588483,'20220308'),
        ('846060',29.143835,'20220425'),
        ('847070',29.154827,'20220425');  -- new record

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kaj SSCommitted Points: 1616 More actions · Answer 12

drew.allen wrote:

This query will only work if there is exactly one part. I added one more record for a different part, and now I am getting no records at all.

insert into @v_po_history (Part, Lowest_Cost, Date_Received)
    values
        ('846060',28.373265,'20220105'),
        ('846060',29.143835,'20220228'),
        ('846060',27.588483,'20220308'),
        ('846060',29.143835,'20220425'),
        ('847070',29.154827,'20220425');  -- new record

Drew

Yes, that was sloppy of me. I should have checked that it would work with more data! 🙁

I guess that leaves us with the unwieldy subquery version:

with
    base as (
        select distinct
            Part
        from @v_po_history 
    ),

    base_LHC as (
        select
            base.Part,
            (select top 1 Lowest_Cost from @v_po_history where Part=base.Part order by Lowest_Cost ASC, Date_Received ASC) AS Lowest_Cost,
            (select top 1 Date_Received from @v_po_history where Part=base.Part order by Lowest_Cost ASC, Date_Received ASC) AS Date_Received,
            (select top 1 Lowest_Cost from @v_po_history where Part=base.Part order by Lowest_Cost DESC, Date_Received ASC) AS Highest_Cost,
            (select top 1 Date_Received from @v_po_history where Part=base.Part order by Lowest_Cost DESC, Date_Received ASC) AS Last_Date_Received
        from base
    )

select
    Part,
    Lowest_Cost,
    Date_Received,
    Highest_Cost,
    Last_Date_Received,
    Highest_Cost - Lowest_Cost as Difference
from base_LHC;

This at least does produce a better result:

Part                 Lowest_Cost                    Date_Received Highest_Cost                   Last_Date_Received Difference
-------------------- ------------------------------ ------------- ------------------------------ ------------------ ------------------------------
846060               27.588483                      2022-03-08    29.143835                      2022-02-28         1.555352
847070               29.154827                      2022-04-25    29.154827                      2022-04-25         0.000000

(2 rows affected)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 13

profoundhypnotic wrote:

I am trying to get the lowest cost for a part and the highest cost for a part. along with the dates that they occurred. I almost have what I want except that in my query I don't know how to associate the lowest cost with the correct date. I understand why (because that's what I told the query to do) but I am wondering how can I alter the query so that when the lowest cost is selected, it selects the corresponding date as well?
Data set example:
|Part |lowest_cost|date_received| |--------- |--------------|-------------| |846060| 28.373265 | 1/5/2022 | |846060| 29.143835 | 2/28/2022 | |846060| 27.588483 | 3/8/2022 | |846060| 29.143835 | 4/25/2022 |
Desired output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference| |---------|--------------|-----------------|---------------|-----------------------|------------| |846060| 27.588483 | 3/8/2022 | 29.143835 | 4/25/2022 | 1.555405 |
current output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference| |---------|--------------|-----------------|---------------|-----------------------|------------| |846060| 27.588483 | 1/5/2022 | 29.143835 | 4/25/2022 | 1.555405 |
query I have currently:
select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost, min(date_received) as First_date, max(date_received) as Last_Date, (max(cost) - min(cost)) as Difference from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS' and part = '846060' group by part

Please read the article at the first link in my signature line below for future posts. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)