SQL Server I Need Assistance with a MIN Function

ckx4rj1h 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(115)

I have a dataset that returns multiple rows of data for a customer on the same date of service. I want to only return the row with the lowest TX_ID. I have been trying to use the MIN() function in a subquery to get this result but my query returns no data. Here is the sample of what I am looking to do
| TX_ID | CUSTOMER_ID | SERVICE_DATE | SERV_TECH_ID | TOTAL_CHG |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 901772 | 12345 | 3/6/23 | 1125 | 250 |
| 901773 | 12345 | 3/6/23 | 1125 | 75 |
| 901774 | 12345 | 3/6/23 | 1125 | 50 |

Image of Current Results

Image of Desired Results

Desired result
| TX_ID | CUSTOMER_ID | SERVICE_DATE | SERV_TECH_ID | POS_ID | | LOC_ID REV_CODE | TOTAL_CHG |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 901772 | 12345 | 3/6/23 | 1125 | 3081 | 100 | 4425 | 250 |

This is the query I am using but as I said it is not returning any results:

SELECT TX_ID
    ,at.CUSTOMER_ID
    ,at.SERVICE_DATE
    ,SERV_TECH_ID
    ,TOTAL _CHG

FROM ARPB_TRANSACTIONS at
    INNER JOIN (
            SELECT CUSTOMER_ID, MIN(TX_ID) AS TX, SERVICE_DATE
            FROM ARPB_TRANSACTIONS
            GROUP BY CUSTOMER_ID, SERVICE_DATE
            ) oq
            ON at.CUSTOMER_ID = oq.CUSTOMER_ID AND TX_ID = oq.TX

WHERE at.CUSTOMER_ID = '12345' AND at.SERVICE_DATE = '3/6/23' AND SERV_TECH_ID = '1125' AND VOID_DATE IS NULL

GROUP BY at.CUSTOMER_ID
    ,at.TX_ID
    ,at.SERVICE_DATE
    ,SERV_TECH_ID
    ,TOTAL_TOTAL

sql-server

来源：https://stackoverflow.com/questions/76441353/i-need-assistance-with-a-min-function

2条答案

按热度按时间

raogr8fs1#

SELECT at.TX_ID
    ,at.CUSTOMER_ID
    ,at.SERVICE_DATE
    ,at.SERV_TECH_ID
    ,at.TOTAL _CHG
FROM ARPB_TRANSACTIONS at
    INNER JOIN (
            SELECT CUSTOMER_ID, MIN(TX_ID) AS TX
            FROM ARPB_TRANSACTIONS
    WHERE CUSTOMER_ID = '12345' 
AND SERVICE_DATE = '20230623' 
AND SERV_TECH_ID = '1125' 
AND VOID_DATE IS NULL
            GROUP BY CUSTOMER_ID
            ) oq
            ON at.CUSTOMER_ID = oq.CUSTOMER_ID AND at.TX_ID = oq.TX

赞(0）回复(0）举报 2023-06-21

xxslljrj2#

There seems to be a small error in your query.

The alias TOTAL_TOTAL in your GROUP BY clause doesn't match the column name TOTAL_CHG in the SELECT clause. That could be one reason why your query is not returning any results.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server I Need Assistance with a MIN Function

2条答案

相关问题

热门标签

最新问答