what sql would do this?

[karen4201]

Hi,

I have a table with a document's history. The table's fields are: docid (int), name (varchar), version(int), status(varchar)
status could be = under construction, published, expired

with each editing of a document, a new record is inserted and the version number is incremented. Here is an example document:

docid, name, version, status
2, 'my doc', 1, 'under construction'
2, 'my doc', 2, 'under construction'
2, 'my doc', 3, 'under construction'
2, 'my doc', 4, 'published'

here is another example:
docid, name, version, status
3, 'my doc b', 1, 'under construction'
3, 'my doc b', 2, 'under construction'
3, 'my doc b', 3, 'under construction'
3, 'my doc b', 4, 'published'
3, 'my doc b', 5, 'under construction'

here is another example:
docid, name, version, status
6, 'my doc c', 1, 'published'

I'd like to run a query that would show me a count of all documents that are 'under construction',
where
the version number is the greatest version number that is under construction
and
the greatest version under construction is greater than published (if published even exists)


so....some logic: a document does not need to have a published version; once a document is published the previous version under construction should be ignored


Any suggestions would be greatly appreciated.

 

[bborissov]

CREATE TABLE #test (docid int, name varchar(20), version int, status varchar(50))
insert into #test VALUES (2, 'my doc', 1, 'under construction')
insert into #test VALUES (2, 'my doc', 2, 'under construction')
insert into #test VALUES (2, 'my doc', 3, 'under construction')
insert into #test VALUES (2, 'my doc', 4, 'published')

insert into #test VALUES (3, 'my doc b', 1, 'under construction')
insert into #test VALUES (3, 'my doc b', 2, 'under construction')
insert into #test VALUES (3, 'my doc b', 3, 'under construction')
insert into #test VALUES (3, 'my doc b', 4, 'published')
insert into #test VALUES (3, 'my doc b', 5, 'under construction')
insert into #test VALUES (6, 'my doc c', 1, 'published')

SELECT COUNT(*) AS Cnt
FROM #test
INNER JOIN #test Tbl1 ON #test.DocId   = Tbl1.DocId   AND
                           #test.Version < Tbl1.Version AND
                           Tbl1.Status = 'under construction'
WHERE #test.Status = 'published'
drop table #test

?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[BillLumbergh]

Don't completely understand the second part of your where, but is this close?

select *
from documents
where status = 'Under Construction'
and version = (select max(version) from documents where status = 'Under Construction')
and version > isnull((select max(version) from documents where status = 'Published'), 0)

 

[bborissov]

OK, can youpost the expecting results from you example data? Because you said "count", but I didn't see any count in your SELECT. Change SELECT COUNT(*) to SELECT * in my example and see if this works for you. What I do:
I join table to itself on DocId and want only records that are Under constuction and has version smaller than published to be joined.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.