SQL Server MAX Function Returning Two Rows

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

关注(0)|答案(1)|浏览(110)

I have a query that should be pulling in five rows of data but it is only pulling in four rows of data and I don't know why the MAX function in my query is causing it to miss the one row of data. Here is my query:

SELECT TX_ID
,at.TX_CSN
,c.CUSOTMER_ORG_ID
,c.CUSOMTER_NAME
,at.CUSTOMER_ID
,at.SERVICE_DATE
,CASE WHEN (st.TECH_TYPE IN ('Trainee','Assistant')) THEN st1.SERVICE_TECH
    ELSE st.SERVICE_TECH END "SERVICE_TECH"
,d.DIVISION_NAME
,p.DIVISION_ABBR
,p.POS
,l.LOC_NAME
,UNIT_CODE
,sca.UNIT_CATEGORY
,PER_UNIT

FROM AR_TXS at
JOIN CUSTOMER c ON at.CUSTOMER_ID = c.CUSTOMER_ID
JOIN SERVICE_TECH st ON at.SERV_TECH_ID = st.SERV_TECH_ID
JOIN DEPARTMENT d ON at.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN POS p ON at.POS_ID = p.POS_ID
JOIN LOC l ON at.LOC_ID = l.LOC_ID
JOIN SERVICE_TECH st1 ON at.BILLING_SERV_TECH_ID = st1.SERV_TECH_ID
JOIN SERVICE_CODE sc ON at.PROC_ID = sc.PROC_ID
JOIN SERVICE_CAT sca ON sc.PROC_CAT_ID = sca.PROC_CAT_ID

WHERE at.SERVICE_DATE >='5/8/23' AND at.SERVICE_DATE <= '5/12/23' AND VOID_DATE IS NULL AND at.ROUTER_SRC_ID IS NOT NULL AND d.DIV_PREFIX = '599' AND at.CUSTOMER_ID = '4407'
AND at.PER_UNIT = (SELECT MAX(PER_UNIT)
    
                        FROM AR_TXS at1
                        WHERE at1.CUSTOMER_ID = at.CUSTOMER_ID AND at.SERVICE_DATE = at1.SERVICE_DATE
                        )

GROUP BY at.CUSTOMER_ID
,at.TX_ID
,at.SERVICE_DATE
,st.SERV_TECH_ID
,CPT_CODE
,RVU_TOTAL
,d.DEPARTMENT_NAME
,p.POS_NAME_ABBR
,p.POS_NAME
,l.LOC_NAME
,c.CUSOTMER_ORG_ID
,c.CUSOMTER_NAME
,at.TX_CSN
,sca.UNIT_CATEGORY
st.TECH_TYPE
,st.SERVICE_TECH
,st1.SERVICE_TECH

Here are the results with this query and then the results with the MAX Function removed. Results with Qry

Results with MAX Removed from Qry

sql-server

来源：https://stackoverflow.com/questions/76492044/max-function-returning-two-rows

1条答案

按热度按时间

gojuced71#

You can use a window function like this:

WITH CTE as (SELECT TX_ID
    ,at.TX_CSN
    ,c.CUSOTMER_ORG_ID
    ,c.CUSOMTER_NAME
    ,at.CUSTOMER_ID
    ,at.SERVICE_DATE
    ,CASE WHEN (st.TECH_TYPE IN ('Trainee','Assistant')) THEN st1.SERVICE_TECH
        ELSE st.SERVICE_TECH END "SERVICE_TECH"
    ,d.DIVISION_NAME
    ,p.DIVISION_ABBR
    ,p.POS
    ,l.LOC_NAME
    ,UNIT_CODE
    ,sca.UNIT_CATEGORY
    ,PER_UNIT
    ,ROW_NUMBER() OVER (PARTITION BY at.CUSTOMER_ID,at.SERVICE_DATE ORDER BY at.PER_UNIT DESC) rn
FROM AR_TXS at
JOIN CUSTOMER c ON at.CUSTOMER_ID = c.CUSTOMER_ID
JOIN SERVICE_TECH st ON at.SERV_TECH_ID = st.SERV_TECH_ID
JOIN DEPARTMENT d ON at.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN POS p ON at.POS_ID = p.POS_ID
JOIN LOC l ON at.LOC_ID = l.LOC_ID
JOIN SERVICE_TECH st1 ON at.BILLING_SERV_TECH_ID = st1.SERV_TECH_ID
JOIN SERVICE_CODE sc ON at.PROC_ID = sc.PROC_ID
JOIN SERVICE_CAT sca ON sc.PROC_CAT_ID = sca.PROC_CAT_ID
WHERE at.SERVICE_DATE >='5/8/23' AND at.SERVICE_DATE <= '5/12/23' AND VOID_DATE IS NULL AND at.ROUTER_SRC_ID IS NOT NULL AND d.DIV_PREFIX = '599' AND at.CUSTOMER_ID = '4407')
SELECT 
    TX_ID
    ,at.TX_CSN
    ,c.CUSOTMER_ORG_ID
    ,c.CUSOMTER_NAME
    ,at.CUSTOMER_ID
    ,at.SERVICE_DATE
    ,SERVICE_TEC
        ,d.DIVISION_NAME
    ,p.DIVISION_ABBR
    ,p.POS
    ,l.LOC_NAME
    ,UNIT_CODE
    ,sca.UNIT_CATEGORY
    ,PER_UNIT
FROM CTE WHERE rn = 1

Which would give only 1 row per customer and date with the highest price.

The order in the ROW_NUMBER will determine which row to select to get all rows with the highest price, you can add more conditions, if you need one specific row.

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

我来回答

SQL Server MAX Function Returning Two Rows

1条答案

相关问题

热门标签

最新问答