CODEWITHSUNDEEP
sqlsql-servermultiple-columns
Jov
Jov

Reputation: 45

Using case-when for complex query under several condition

I have the data shown below:

    ID_LIFTING | ITEM | DATE
    ---------------------
    1101       | 6    | 2020-02-01
    1101       | 6    | 2020-02-02
    1101       | 6    | 2020-02-03
    1101       | 7    | 2020-02-01
    1101       | 7    | 2020-02-02
    1101       | 7    | 2020-02-03
    1102       | 6    | 2020-02-04
    1102       | 6    | 2020-02-04
    1102       | 7    | 2020-02-04
    1102       | 7    | 2020-02-04

What I want is, under similar ID_DATA and ITEM, there will be one column showing concatenated date range as string 'MIN(date) - MAX(date)'. But if under similar ID_DATA the date is similar, it will only show the DATE as is without range.

Note that there might be more than 2 rows of DATE for a single ID_DATA. I'm hoping to use case-when.

Following expected result:

    ID_LIFTING | ITEM | DATE
    ---------------------
    1101       | 6    | 2020-02-01 - 2020-02-03
    1101       | 7    | 2020-02-01 - 2020-02-03
    1102       | 6    | 2020-02-04
    1102       | 7    | 2020-02-04

Below is the query I've made. My query is still incorrect, I'm confused where to put the correct query for my above expected result :( (it's for ALD_DATE). Note that I have many joins below to get the data I need as shown above. ID_LIFTING is there after table parameter D.

    SELECT DISTINCT ...
            )
        ,ALD_DATE = (SELECT CASE WHEN MIN(DATE) = MAX(DATE) THEN CAST(MIN(DATE) AS CHAR)
            ELSE CONCAT(MIN(DATE), ' - ', MAX(DATE)) 
       END AS DATE
FROM data
GROUP BY ID_LIFTING, ITEM
ORDER BY ID_LIFTING),

    FROM MYTABLE A
    LEFT JOIN ...;

Upvotes: 0

Views: 101

Answers (1)

Nick
Nick

Reputation: 147166

You can use a CASE expression to check whether the MIN and MAX values of DATE are the same. If not, output a range, otherwise output a single value:

SELECT ID_LIFTING, ITEM,
       CASE WHEN MIN(DATE) = MAX(DATE) THEN CAST(MIN(DATE) AS CHAR)
            ELSE CONCAT(MIN(DATE), ' - ', MAX(DATE)) 
       END AS DATE
FROM data
GROUP BY ID_LIFTING, ITEM
ORDER BY ID_LIFTING

Output (for your sample data):

ID_LIFTING  ITEM    DATE
1101        6       2020-02-01 - 2020-02-03
1101        7       2020-02-01 - 2020-02-03
1102        6       2020-02-04
1102        7       2020-02-04

Demo on SQLFiddle

In terms of incorporating this into your query, you'll need to use window functions e.g.

ALD_DATE = CASE WHEN MIN(DATE) OVER (PARTITION BY ID_LIFTING, ITEM) = MAX(DATE) OVER (PARTITION BY ID_LIFTING, ITEM) THEN CAST(MIN(DATE) OVER (PARTITION BY ID_LIFTING, ITEM) AS CHAR) 
                ELSE CONCAT(MIN(DATE) OVER (PARTITION BY ID_LIFTING, ITEM), ' - ', MAX(DATE) OVER (PARTITION BY ID_LIFTING, ITEM)) 
           END

Upvotes: 1

Related Questions