Case When Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Case When Help

Case When Help

Rate Topic

Display Mode

Topic Options

Author

Message

helal.mobasher 13209

Posted Tuesday, September 11, 2012 6:14 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 24, 2013 2:05 PM
Points: 23, Visits: 99

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

dwain.c

Posted Tuesday, September 11, 2012 6:26 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:31 PM
Points: 2,343, Visits: 3,180

You can try this:

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1357751

subbareddy542

Posted Wednesday, September 12, 2012 1:12 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:17 AM
Points: 832, Visits: 613

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 »

Permissions