How to create derived columns in SELECT

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

How to create derived columns in SELECT

Author

Topic

SQLGeno
Starting Member

13 Posts

Posted - 2011-01-31 : 23:12:24

Great site with some people who know their stuff.
On to my question; consider the following sets;

CaseTable
CaseId.......Name
100.............Joe
200.............Mary
501.............Bob

CaseActionTable
CaseId...ActionId...ActionDate
100.........2..........10 Mar 2010
200.........4..........1 April 2010
501.........2..........12 May 2010
501.........4..........1 June 2010

Desired Result
Name....Action2Date.......Action4Date
Joe........10 Mar 2010.......NULL
Mary.......NULL..............1 April 2010
Joe........12 May 2010.......1 June 2010

Logic is to create a single row for the CaseId and add two derived columns that are populated with the respective dates when the Case has an associated row in the CaseActionTable. If there is no row for that ActionId then SET row to NULL (or whatever you like).

Should use a CASE with an EXISTS query in the SELECT statement? No sure of syntax here though.

PS - any help getting spaces or tabs (for column spacing) to work in my posts would be appreciated too.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 00:01:44

have you done any worked so for over this if so post your sql query ..

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-01 : 00:07:21

I have been trying this sort of thing. I won't bore you with the whole SP...

SELECT OffenderCaseId,
EXISTS (SELECT 1
FROM OffenderCaseActionStep
INNER JOIN OffenderCase
ON OffenderCaseActionStep.OffenderCaseId = OffenderCase.OffenderCaseId
WHERE OffenderCase.OffenderCaseId = OffenderCaseActionStep.OffenderCaseId
AND ActionStepId = 2) AS 'HasIncomplete'
FROM OffenderCase

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-01 : 00:09:30

Got the idea from http://www.ghidinelli.com/2009/01/30/sql-tip-using-exists-to-generate-a-column-in-place-of-a-subquery

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 00:15:45

leave that and check the below if this is what you are looking for!

declare @casetable table (caseid int,cname varchaR(10))
Declare @CaseActiontable table (caseid int,actionid int,actiondate datetime)
Insert into @casetable values(100,'Joe'),(200,'Mary'),(501,'Bob')
Insert into @CaseActiontable values(100,2,'2010-03-10'),(200,4,'2010-04-01'),(501,2,'2010-05-12'),(501,4,'2010-06-01')

SElect
C.cname
,Max(case when ca.actionid=2 and ca.actiondate is not NULL then ca.actiondate End)Action2Date
,MAx(case when ca.actionid=4 and ca.actiondate is not NULL then ca.actiondate End)Action4Date
From @Casetable c
Left Join @CaseActiontable ca on ca.caseid=C.caseid
Group by C.cname

Cheers!
MIK

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-01 : 01:19:39

Brilliant! Whole SP working perfectly in this manner which will make the report construction much easier. Thx a million.
Out of curiousity; could have the EXISTS in the SELECT statement ever been made to work?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 01:26:03

Welcome.... and in fact i haven't looked in detail over what you sent me .. as the scenario handled in that seems a bit change to what you asked for .. exists is just for to get yes/no over a condition/statement so not sure if it could give you the desired result.

Cheers!
MIK

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-01 : 01:33:04

I can't find any example where MAX is used the way you have used it, but I like it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources