| Author |
Topic |
|
Yars
Starting Member
7 Posts |
 Posted - 2004-10-04 : 10:03:45
|
| Hello,i have a table called Message with two fields, Type(varchar) and Submitted(byte) and an identity column. What i need to do is write a stored procedure that will find the last row where the Type is "Count" and Submitted is "1", then find the first row where the Type is also "Count" but Submitted is "0", then return the number of rows between them. So if i had a row that was a count type and submitted and then 5 rows with some other type, and then another row of type count and not submitted, then i want to get the number 5 back.I dont really know how to go about writing this query. Any help will be greatly appreciated. ThanksYars |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-04 : 10:12:46
|
| [code]Select--find the first 0 after the last 1 (Select min(id) from myTable A Where type='Count' and Submitted=0 and not exists(Select * From myTable Where type='Count' and Submitted=1) and id>A.id) ---find the last 1 (Select max(id) from myTable Where type='Count' and Submitted=1)[/code]Corey |
 |
|
|
VIG
Yak Posting Veteran
86 Posts |
Posted - 2004-10-04 : 10:13:18
|
| select count(*) from Messagewhere Id >(select top 1 id from message where type='Count' and Submitted=1 order by id desc)and Id <(select top 1 id from message where type='Count' and Submitted=0 order by id asc) |
|
|
|
Yars
Starting Member
7 Posts |
Posted - 2004-10-04 : 10:18:04
|
| wow that was fast, you guys are awesome, thanks!Yars |
|
|
|
Yars
Starting Member
7 Posts |
Posted - 2004-10-05 : 17:18:57
|
| i have another question about this. What do i do if the first message of type count is submitted = 0. then there's no message of the same type before it where submitted = 0 |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-05 : 18:18:03
|
| maybe you could default it to 0...select count(*) from Messagewhere Id > isnull((select top 1 id from message where type='Count' and Submitted=1 order by id desc),0)and Id <(select top 1 id from message where type='Count' and Submitted=0 order by id asc)Corey |
|
|
|
Yars
Starting Member
7 Posts |
Posted - 2004-10-06 : 11:30:37
|
| thanks Corey, that works perfectly |
|
|
|
Yars
Starting Member
7 Posts |
Posted - 2004-10-06 : 13:49:48
|
| One more question, haha.Right now the query works the way it's supposed to, but i want to be able to tell if i get a result of zero b/c there are two type count rows with nothing between them or if it's b/c i dont have any type count rows where submitted=0.This is important to me b/c i need to know if i should process the zero, in the case that there is nothing between a submitted=1 and submitted = 0 rows, or if i should send a message saying that all type Count rows are submitted=1. Thanks in advance.Yars |
|
|
|
Yars
Starting Member
7 Posts |
Posted - 2004-10-06 : 14:23:21
|
| Please disregard my previous post i realized that i wasn't making any sense and figured out what i needed to do already. It was not a problem with this query but a different one. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-06 : 14:35:41
|
That's funny...I usually use...Please disregard my last post, as I was unconscious at he time.... Brett8-) |
|
|
|
|
Count question