Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case When Help Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 6:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:35 AM
Points: 27, Visits: 116
I am trying to assign Active or inactive based on member termdate. A member may have multiple termdates. The logic is if a member MAX termdate is greater than getdate() then regardless of his/her previous termdate, he/she is assigned Active. If a member Max termdate is less than getdate(), then he/she is assinged inactive. Here are DLLs:
CREATE TABLE table1
(
ID Nvarchar (10),
Date smalldat
)

INSERT INTO table1
(ID,Date)
SELECT '1', '12/31/2078'
UNION ALL
SELECT '1', '12/31/2011'
UNION ALL
SELECT '2', '12/31/2011'
UNION ALL
SELECT '2', '11/30/2011'
UNION ALL
SELECT '2', '10/31/2011'

--Results
CREATE TABLE Results
(
ID Nvarchar (10),
Date smalldat,
Status varchar(15)
)

INSERT INTO Results
(ID,Date,Status)
SELECT '1', '12/31/2078' , 'Active'
UNION ALL
SELECT '1', '12/31/2011' , 'Active'
UNION ALL
SELECT '2', '12/31/2011' , 'Inactive'
UNION ALL
SELECT '2', '11/30/2011' , 'Inactive'
UNION ALL
SELECT '2', '10/31/2011' , 'Inactive'

Thank for the help

Helal
Post #1357750
Posted Tuesday, September 11, 2012 6:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
You can try this:

SELECT ID, Date
,Status=CASE WHEN GETDATE() < MAX(Date) OVER (PARTITION BY ID) THEN 'Active' ELSE 'Inactive' END
FROM Table1





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1357751
Posted Wednesday, September 12, 2012 1:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
Pls try below code.

CREATE TABLE #table1
(
ID Nvarchar (10),
Date DATE
)

INSERT INTO #table1
(ID,Date)
SELECT '1', '12/31/2078'
UNION ALL
SELECT '1', '12/31/2011'
UNION ALL
SELECT '2', '12/31/2011'
UNION ALL
SELECT '2', '11/30/2011'
UNION ALL
SELECT '2', '10/31/2011'
SELECT *,(SELECT CASE WHEN MAX(T2.Date)>GETDATE() THEN 'ACTIVE' ELSE 'INACTIVE' END FROM #table1 T2 WHERE T1.ID=T2.ID) STATUS FROM #table1 T1
Post #1357812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse