• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
lulu50
Asked:
lulu50
  • 5
  • 5
1 Solution
 
Phillip BurtonCommented:
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
 
Dany BalianCTOCommented:
in which tables the techname/business name are found?
0
 
lulu50Author Commented:
Phillip,

I get this error


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

Using myTable as (
0
Independent Software Vendors: 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!

 
Phillip BurtonCommented:
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
 
lulu50Author Commented:
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
 
Phillip BurtonCommented:
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
 
lulu50Author Commented:
Phillip,

I am not sure where to put it

line 20 is group by
0
 
Phillip BurtonCommented:
I mean Line 15:

Select AppID,ApplicationName, AppDescription,AppLifecycle, domain
0
 
lulu50Author Commented:
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
 
Phillip BurtonCommented:
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
 
lulu50Author Commented:
Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now