Can this be done with one sql statement? How best to approach it? - 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 2000 Forums

SQL Server Development (2000)

Can this be done with one sql statement? How best to approach it?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-06 : 09:08:03

wes writes "Sql Server 6.5 sp 6 NT 4.0
I have a table in my system that tracks the history of work orders. I have been beating my head trying to figure out the best way to approach this.
How can I calculate the number of orders = 'INPRG' (inprogress) the first of the month for say each of the previous 12 months. Each stage of a work order's history will have a different status and changedate. A work order can be 'INPRG' over many months and needs to counted each month it is 'INPRG' and has not been (completed or closed) 'COMP' or 'CLOSE'(whichever occurred first) previously.

187002 counted Oct. 1
187020 would be counted Oct. 1 and Nov. 1
The others would be ignored because they were not INPRG on the first of the month.

 wonum     status   changedate                   
 ---------- -------- --------------------------- 
 187000     COMP     Sep 24 2001 10:29AM              
 187000     INPRG    Sep 19 2001  8:56AM             
 187000     WAPPR    Sep 19 2001  8:18AM             
 187001     COMP     Sep 25 2001  9:06AM             
 187001     INPRG    Sep 19 2001  8:55AM             
 187001     WAPPR    Sep 19 2001  8:22AM             
 187002     COMP     Oct 15 2001  9:29AM             
 187002     INPRG    Sep 19 2001  8:26AM             
 187002     WAPPR    Sep 19 2001  8:26AM             
 187020     CLOSE    Jan 9 2002  5:10PM          
 187020     COMP     Nov 21 2001  9:20AM         
 187020     INPRG    Sep 19 2001 10:08AM         
 187020     WAPPR    Sep 19 2001 10:07AM

Table
wostatus
CREATE TABLE dbo.wostatus (
rowstamp timestamp NOT NULL ,
wonum varchar (10) NOT NULL ,
status varchar (8) NOT NULL ,
changedate datetime NOT NULL ,
changeby varchar (20) NOT NULL ,
memo varchar (50) NULL ,
glaccount varchar (20) NULL ,
fincntrlid varchar (8) NULL
)

I would appreciate any direction you Gurus can provide.
Thx"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-07 : 14:28:04

Wes,
This ought to do it:

create table #wostatus (
wonum varchar (10) NOT NULL ,
status varchar (8) NOT NULL ,
changedate datetime NOT NULL ,
)

insert #wostatus( wonum, status, changedate )
select 187000, 'COMP', 'Sep 24 2001 10:29AM' union
select 187000, 'INPRG', 'Sep 19 2001 8:56AM' union
select 187000, 'WAPPR', 'Sep 19 2001 8:18AM' union
select 187001, 'COMP', 'Sep 25 2001 9:06AM' union
select 187001, 'INPRG', 'Sep 19 2001 8:55AM' union
select 187001, 'WAPPR', 'Sep 19 2001 8:22AM' union
select 187002, 'COMP', 'Oct 15 2001 9:29AM' union
select 187002, 'INPRG', 'Sep 19 2001 8:26AM' union
select 187002, 'WAPPR', 'Sep 19 2001 8:26AM' union
select 187020, 'CLOSE', 'Jan 9 2002 5:10PM' union
select 187020, 'COMP', 'Nov 21 2001 9:20AM' union
select 187020, 'INPRG', 'Sep 19 2001 10:08AM' union
select 187020, 'WAPPR', 'Sep 19 2001 10:07AM'

select w1.wonum, datediff( m, w1.changedate, w2.changedate )
from #wostatus w1
inner join (
select wonum, min(changedate) as changedate
from #wostatus
where status in ( 'COMP','CLOSE' )
group by wonum) w2 on w1.wonum = w2.wonum
where w1.status = 'INPRG'

setBasedIsTheTruepath
<O>

weswilliams
Starting Member

1 Post

Posted - 2002-05-22 : 10:59:18

Thanks for the reply, it is pretty slick, but
while this gives me how many months each order was open, it doesn't provide me with a means to report by month how many were INPRG.
Is this possible? Again I could have 20k orders or more each year and probably average about a 1200 order backlog which equates to INPRG, we need to calculate that backlog on the first of each month and back 12 months.

I was looking for the results to be as such.

month cnt
-------------
Oct 2
Nov 1
.
.
Thx

Wes

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-22 : 11:37:11

I'll take a shot at it

select count(changedate)
from wostatus
where '10-1-00' between (select changedate from wostatus where status = 'inprg') and (select changedate from wostatus where status = 'comp')

Hmm, that won't work but I think the idea is right. You'd need to make that into a cursor and go through each wonum and inclde each wonum in the subqueries.. I think... and I don't like that.

another option would be to Create a temp table, with 2 columns (lets say a and b. load one column with the changedate where status = inprg and the other with Min(changedate) where status = 'closed' or 'comp' the you just run that query as

select count(a)
from #temp
where '10-1-00' between a and b

this is for october, to get for previous monthes you'd need to change the where statement to '09-01-00'.. which could be done in a while statement

declare @temp int,
@date datetime
set @date = '10-1-00'
select count(a)
from #temp
where '10-1-00' between a and b
while @temp < 12
begin
set @temp = @temp + 1
select datename(mm, dateadd(month,-(@temp),@date) ) as 'month' ,count(a) as 'total in prg'
from #temp
where dateadd(month,-(@temp),@date) between a and b
end

@date is the will be the first date you want to be displayed, it will show 12 monthes before that. You could easily make this into a stored proc so that the stored proc builds the temp table too.

If you need anything else... just email

Edited by - M.E. on 05/22/2002 11:39:07

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources