Solved

SQL case statement

Posted on 2015-01-30
11
157 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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