September 11, 2012 at 6:14 pm
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
September 11, 2012 at 6:26 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 12, 2012 at 1:12 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply