how to write next one in sequence? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

how to write next one in sequence?

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-20 : 10:50:39

Morning,

I have a request for a report that says read from the bottom. When the status = Started then show that work center. When there is no started status find one where the status is Completed and show the first Open one after that.

An example would be this one:


Workcenter     Status     operation
PC DRWPART     COMPLETED  90912
DT MSG325      STARTED    90921
fc OPTICAL     OPEN       90922
QC CMM         OPEN       90923
PP POSTPOL     OPEN       90924
QC GENERAL     OPEN       90925
EPNE           OPEN       90926
QC GENERAL     OPEN       90927
CT ENVR        OPEN       90928
QC GENERAL     OPEN       90929

What should happen is that the DT MSG325 is selected. But what is happening is the FC optical is being selected.

I have this for the code:


select top 1
case
when status = 'S' then (select top 1 Work_Center
						from Job_Operation 
						where Status = 'S'
						and job  = j.job
						order by Job_Operation desc)
when Status = 'O' then (select  top 1 Work_Center
						from Job_Operation 
						where Status = 'O'
						and job  = j.job
						order by Job_Operation)
end wc 
from Job_Operation j
where j.job  = '405432'
order by Job_Operation desc

I think I'm close but any thoughts would be great.

Thanks so much

Laura

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-20 : 11:50:40

Found it! I added a case statement in the order by:


select top 1
case
when status = 'S' then (select top 1 Work_Center
						from Job_Operation 
						where Status = 'S'
						and job  = j.job)
when Status = 'O' then (select  top 1 Work_Center
						from Job_Operation 
						where Status = 'O'
						and job  = j.job)
else Work_Center
end wc ---, Job_Operation,Status, Work_Center
from Job_Operation j
where j.job  = '401454'
order by 
		case  status 
			 WHEN 'S' then 1
			 when 'O' then 2
			 else 3
         END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources