CODEWITHSUNDEEP
sqlsql-server
ManiMuthuPandi
ManiMuthuPandi

Reputation: 1604

How to use case here

I need to create a SQL query to generate the below output from DB.

enter image description here

I am searching a condition like this

When week_val is 1 then hour_val should come from week_val 2 row. Otherwise hour_val comes from same row.

Upvotes: 2

Views: 64

Answers (4)

i486
i486

Reputation: 6573

Maybe this - JOIN instead of subselect:

SELECT
    t1.week_val,
    CASE t1.week_val WHEN 1 THEN t2.hour_val ELSE t1.hour_val END AS hour_val 
  FROM
    table_name t1 LEFT JOIN table_name t2 ON t2.week_val = t1.week_val + 1

Upvotes: 1

vercelli
vercelli

Reputation: 4767

If you are certain there's going to be a row with week_val = 2 then you can avoid a subquery.

select week_val, 
       case week_val when 1 then lead(hour_val) over (order by week_val) else hour_val end as hour_val
  from t

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You may need subquery with CASE Statment

SELECT CASE WHEN week_val =1 THEN (SELECT TOP 1 hour_val FROM TABLE NAME WHERE week_val <> 1)
       ELSE  hour_val
       END
FROM  TableName

Upvotes: 1

nilsman
nilsman

Reputation: 396

SELECT week_val, (CASE week_val 
  WHEN 1 THEN (SELECT hour_val FROM your_tbl WHERE week_val = 2) 
  ELSE hour_val) 
FROM your_tbl;

Upvotes: 2

Related Questions