asked on

SQL case statement

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

Phillip Burton

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

Dany Balian

in which tables the techname/business name are found?

lulu50

ASKER

Phillip,

I get this error

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

Using myTable as (

Phillip Burton

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

lulu50

ASKER

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

Phillip Burton

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

lulu50

ASKER

Phillip,

I am not sure where to put it

line 20 is group by

Phillip Burton

I mean Line 15:

Select AppID,ApplicationName, AppDescription,AppLifecycle, domain

lulu50

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.

 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

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lulu50

ASKER

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!