Multiple CASE in Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple CASE in Select Statement

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-11-07 : 11:22:01

I"m working with the following code which works fine.


SELECT     TOP (100) PERCENT dbo.imrechst_sql.rec_hst_dt, dbo.imrechst_sql.item_no, dbo.imrechst_sql.vend_no, dbo.cicmpy.cmp_name, dbo.imrechst_sql.promise_dt, 
                      dbo.imrechst_sql.ord_no, SUM(dbo.imrechst_sql.qty_received) AS Received, DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) AS DaysLate, 
                      CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) > 0 THEN 'L' END AS LateFlag

In my case statement I would like to add a couple more conditions.

If datediff < -3 then 'E'

Anything between -3 and 0 would be 'O'

Is that possible or would i have to create 2 more columns for that?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-07 : 11:26:13

CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) > 0 THEN 'L'
WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) < -3 THEN 'E'
else 'O'
END AS LateFlag

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:26:25

you want this?


SELECT     TOP (100) PERCENT dbo.imrechst_sql.rec_hst_dt, dbo.imrechst_sql.item_no, dbo.imrechst_sql.vend_no, dbo.cicmpy.cmp_name, dbo.imrechst_sql.promise_dt, 
                      dbo.imrechst_sql.ord_no, SUM(dbo.imrechst_sql.qty_received) AS Received, DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) AS DaysLate, 
                      CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) > 0 THEN 'L'
                           WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) < -3 THEN 'E'
                           ELSE 'O' END AS LateFlag

or this?


SELECT     TOP (100) PERCENT dbo.imrechst_sql.rec_hst_dt, dbo.imrechst_sql.item_no, dbo.imrechst_sql.vend_no, dbo.cicmpy.cmp_name, dbo.imrechst_sql.promise_dt, 
                      dbo.imrechst_sql.ord_no, SUM(dbo.imrechst_sql.qty_received) AS Received, DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) AS DaysLate, 
                      CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) > 0 THEN 'L' END AS LateLFlag,                      
                      CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) < -3 THEN 'E' END AS LateEFlag,
                      CASE WHEN DATEDIFF(day, dbo.imrechst_sql.promise_dt, dbo.imrechst_sql.rec_hst_dt) BETWEEN -3 AND 0 THEN 'O' END AS lateOFlag

I think what you need is former though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-11-07 : 11:27:09

Thanks. That worked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:28:48

quote:
Originally posted by Vack

Thanks. That worked

out of curiosity,
which one was you looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources