lulu50
asked on
SQL case statement
Hi,
I have a query like this
that generate this output
the output that I am looking for is like this :
I need to have in my select statement a case if RoleID = 6 then it is a tech name else if RoleID = 7 then it is business name.
Thank you for your help .
lulu
I have a query like this
SELECT EA.AppID,AR.ApplicationName, CAST(EA.AppDescription AS NVARCHAR(100)) as AppDescription,EA.AppLifecycle, BP.BusinessProcessName as domain,
AC.EmpID,AC.RoleID,EA.Update_Date
FROM AppRepository AR
left outer join BusinessProcess BP on BP.BusinessProcessId = AR.BusinessProcessID
left join EDIT_ApplicationDescription EA on EA.AppID = AR.applicationID
left join Application_Contact AC on AC.ApplicationID = AR.ApplicationID
WHERE
AR.Retired = 1 and AC.RoleID in (6,7)
And AR.BusinessProcessId in (20,40,60,80,100,120,140,
160,180,200)
group by EA.AppID,AR.ApplicationName, EA.AppLifecycle, EA.Update_Date,CAST(EA.AppDescription AS NVARCHAR(100)),BP.BusinessProcessName
,AC.EmpID,AC.RoleID
order by EA.AppID asc
that generate this output
AppID ApplicationName AppDescription AppLifecycle domain EmpID RoleID Update_Date
13 ApplicatinName Description lifecyle thedomain John Smith 7 2013-06-25 15:11:51.000
13 ApplicatinName Description lifecyle thedomain Sue Olea 6 2013-06-25 15:11:51.000
13 ApplicatinName Description lifecyle thedomain John Smith 7 2013-06-25 15:11:51.000
13 ApplicatinName Description lifecyle thedomain Sue Olea 6 2013-06-25 15:11:51.000
14 ApplicatinName Description lifecyle thedomain Nichole fuss 7 2013-06-25 15:11:51.000
14 ApplicatinName Description lifecyle thedomain John Fussies 6 2013-06-25 15:11:51.000
14 ApplicatinName Description lifecyle thedomain Nichole fuss 7 2013-06-25 15:11:51.000
14 ApplicatinName Description lifecyle thedomain John Fussies 6 2013-06-25 15:11:51.000
15 ApplicatinName Description lifecyle thedomain Lynn thomas 6 2013-06-25 15:11:51.000
17 ApplicatinName Description lifecyle thedomain Joyce Blabla 7 2013-06-25 15:11:51.000
17 ApplicatinName Description lifecyle thedomain katy Blury 6 2013-06-25 15:11:51.000
the output that I am looking for is like this :
AppID ApplicationName AppDescription AppLifecycle domain Tech Business Update_Date
13 ApplicatinName Description lifecyle thedomain Sue Olea John Smith 2013-06-25 15:11:51.000
14 ApplicatinName Description lifecyle thedomain John Fussies Nichole fuss 2013-06-25 15:11:51.000
15 ApplicatinName Description lifecyle thedomain Lynn thomas 2013-06-25 15:11:51.000
17 ApplicatinName Description lifecyle thedomain katy Blury Joyce Blabla 2013-06-25 15:11:51.000
I need to have in my select statement a case if RoleID = 6 then it is a tech name else if RoleID = 7 then it is business name.
Thank you for your help .
lulu
in which tables the techname/business name are found?
ASKER
Phillip,
I get this error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Using myTable as (
I get this error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Using myTable as (
Sorry, instead of
Using
use
With
Oops.
So:
Using
use
With
Oops.
So:
With myTable as (
SELECT EA.AppID,AR.ApplicationName, CAST(EA.AppDescription AS NVARCHAR(100)) as AppDescription,EA.AppLifecycle, BP.BusinessProcessName as domain,
AC.EmpID,AC.RoleID,EA.Update_Date
FROM AppRepository AR
left outer join BusinessProcess BP on BP.BusinessProcessId = AR.BusinessProcessID
left join EDIT_ApplicationDescription EA on EA.AppID = AR.applicationID
left join Application_Contact AC on AC.ApplicationID = AR.ApplicationID
WHERE
AR.Retired = 1 and AC.RoleID in (6,7)
And AR.BusinessProcessId in (20,40,60,80,100,120,140,
160,180,200)
group by EA.AppID,AR.ApplicationName, EA.AppLifecycle, EA.Update_Date,CAST(EA.AppDescription AS NVARCHAR(100)),BP.BusinessProcessName
,AC.EmpID,AC.RoleID
)
Select AppID,ApplicationName, AppDescription,AppLifecycle, domain,
(Select Min(EmpID) from myTable as N where M.AppID = N.AppID and N.RoleID = 6) as Tech,
(Select Min(EmpID) from myTable as N where M.AppID = N.AppID and N.RoleID = 7) as Business,
Min(Update_Date) as Update_Date
From myTable as M
Group by AppID,ApplicationName, AppDescription,AppLifecycle, domain
Order by AppID
ASKER
Phillip,
this is great!!!!
one last thing on it
I want to say
based on this query
I get this output
I want to put in the AppLifecycle "Retired" if it is the last entry of that record
The output like this:
just to add "Retired" to the last AppID
this is great!!!!
one last thing on it
I want to say
based on this query
I get this output
AppID ApplicationName AppDescription AppLifecycle domain Tech Business Update_Date
13 Name Description NULL domain Sue Smith John Race 2006-01-18 08:29:21.000
13 Name Description NULL domain Kathy tolido Jeremy Mou 2009-06-11 15:43:45.000
13 Name Description NULL domain Dave Nick Nick Alean 2013-06-05 15:24:41.000
14 Name Description NULL domain Mike NoLastName David Blabla 2011-08-16 15:55:01.000
18 Name Description NULL domain Myier, Robinhood Brideau, Wanda 2007-08-09 08:06:22.000
18 Name Description NULL domain Myner, RobinHead Brideau, Wanda 2010-10-22 09:09:34.000
18 Name Description Retired domain Myier, linbin Brion, Wssda 2012-08-30 14:08:06.000
I want to put in the AppLifecycle "Retired" if it is the last entry of that record
The output like this:
AppID ApplicationName AppDescription AppLifecycle domain Tech Business Update_Date
13 Name Description NULL domain Sue Smith John Race 2006-01-18 08:29:21.000
13 Name Description NULL domain Kathy tolido Jeremy Mou 2009-06-11 15:43:45.000
13 Name Description Retired domain Dave Nick Nick Alean 2013-06-05 15:24:41.000
14 Name Description Retired domain Mike NoLastName David Blabla 2011-08-16 15:55:01.000
18 Name Description NULL domain Myier, Robinhood Brideau, Wanda 2007-08-09 08:06:22.000
18 Name Description NULL domain Myner, RobinHead Brideau, Wanda 2010-10-22 09:09:34.000
18 Name Description Retired domain Myier, linbin Brion, Wssda 2012-08-30 14:08:06.000
just to add "Retired" to the last AppID
Change AppLifecycle in line 20 to:
select case Row_Number() Over(Partition by AppID Order By Update_Date DESC)
when 1 then 'Retired'
Else '' End as AppLifeCycle
select case Row_Number() Over(Partition by AppID Order By Update_Date DESC)
when 1 then 'Retired'
Else '' End as AppLifeCycle
ASKER
Phillip,
I am not sure where to put it
line 20 is group by
I am not sure where to put it
line 20 is group by
I mean Line 15:
Select AppID,ApplicationName, AppDescription,AppLifecycle, domain
Select AppID,ApplicationName, AppDescription,AppLifecycle, domain
ASKER
Error :
Msg 8120, Level 16, State 1, Line 17
Column 'myTable.Update_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'myTable.Update_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'myTable.Update_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'myTable.Update_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
With myTable as (
SELECT EA.AppID,AR.ApplicationName, CAST(EA.AppDescription AS NVARCHAR(100)) as AppDescription,EA.AppLifecycle, BP.BusinessProcessName as domain,
AC.EmpID,AC.RoleID,EA.Update_Date
FROM AppRepository AR
left outer join BusinessProcess BP on BP.BusinessProcessId = AR.BusinessProcessID
left join EDIT_ApplicationDescription EA on EA.AppID = AR.applicationID
left join Application_Contact AC on AC.ApplicationID = AR.ApplicationID
WHERE
AR.Retired = 1 and AC.RoleID in (6,7)
And AR.BusinessProcessId in (20,40,60,80,100,120,140,
160,180,200)
group by EA.AppID,AR.ApplicationName, EA.AppLifecycle, EA.Update_Date,CAST(EA.AppDescription AS NVARCHAR(100)),BP.BusinessProcessName
,AC.EmpID,AC.RoleID
)
Select AppID,ApplicationName, AppDescription,
(select case Row_Number() Over(Partition by AppID Order By Update_Date DESC)
when 1 then 'Retired'
Else '' End) as AppLifeCycle
, domain,
(Select Min(EmpID) from myTable as N where M.AppID = N.AppID and N.RoleID = 6) as Tech,
(Select Min(EmpID) from myTable as N where M.AppID = N.AppID and N.RoleID = 7) as Business,
Min(Update_Date) as Update_Date
From myTable as M
Group by AppID,ApplicationName, AppDescription,AppLifecycle, domain
Order by AppID, Update_Date asc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!! !!!!!
Open in new window