SQL Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Grouping

Author

Topic

Ally
Starting Member

10 Posts

Posted - 2012-07-20 : 14:21:26

Hi All,

I have a table with Run_date , JobID,Status, Table_Name columns

Some jobids have more than 1 tables.

If one of the status has atleast one F I like to put status F for same JobID.

Run_Date JobID Status Table_name
2012-07-20 50030 P Table1
2012-07-20 50030 F Table2
2012-07-20 50030 P Table3

How can I put query above table to get result like :

Run_Date JobID Status
2012-07-20 50030 F

Thanks
Ally

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 14:39:10

[code]
select Run_Date,JobID,
CASE WHEN SUM(CASE WHEN Status = 'F' THEN 1 END)>0 THEN 'F' ELSE MAX(Status) END
from table
group by Run_Date,JobID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ally
Starting Member

10 Posts

Posted - 2012-07-20 : 14:53:24

It works.
Thanks a lot.

Ally

quote:
Originally posted by visakh16

select Run_Date,JobID,
CASE WHEN SUM(CASE WHEN Status = 'F' THEN 1 END)>0 THEN 'F' ELSE MAX(Status) END
from table
group by Run_Date,JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 22:18:13

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources