Solved

SQL case statement

Posted on 2015-01-30
11
160 Views
Last Modified: 2015-01-30
Hi,

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

Open in new window



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

Open in new window



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	

Open in new window



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
0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579828
Try this:

Using 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

Open in new window

0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40579831
in which tables the techname/business name are found?
0
 

Author Comment

by:lulu50
ID: 40579838
Phillip,

I get this error


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

Using myTable as (
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579853
Sorry, instead of

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

                                          

Open in new window

0
 

Author Comment

by:lulu50
ID: 40579935
Phillip,

this is great!!!!

one last thing on it

I want to say

based on this query


Open in new window


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

Open in new window




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

Open in new window



just to add "Retired" to the last AppID
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579952
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
0
 

Author Comment

by:lulu50
ID: 40579969
Phillip,

I am not sure where to put it

line 20 is group by
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579976
I mean Line 15:

Select AppID,ApplicationName, AppDescription,AppLifecycle, domain
0
 

Author Comment

by:lulu50
ID: 40579988
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.



 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       

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40579997
You've added something to the last line:

Order by AppID, Update_Date asc  

It should read

Order by AppID, Min(Update_Date) asc
0
 

Author Closing Comment

by:lulu50
ID: 40580030
Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question