Help On query Writting

Hi all,
I am sorry… that I could not explain you my problem in my previous mail.
In my table I have only two columns. One is srNO and other one is stdID or
StudentID. This table will contain records of multiple students but for
example I am considering only one ie 100 only.
In srNo column there is some data about his absences it is a only day of
month. I just want to count how many times student was continuously
absent/present so if my data is like this
SrNo StdID
-- --
1 100
2 100
3 100
6 100
7 100
9 100
10 100
11 100
12 100
as 4,5, 8 … records are missing. I will start counting from top as 4 is
missing I wll stop at 3 count as 3 next I will start from 6 and stop at 7
count is 2 .. and so on…..
so my out put will be like this for a stident 100
3 100
2 100
4 100
and for multiple students
3 100
2 100
4 100
1 101
4 102
2 103
this is a simple problem of one big problem
I hope This is now clear.
Waiing for help
KishorKishor,
I think this will do what you want.
create table T (
SrNo int not null,
StdID int not null,
primary key (StdID, SrNo)
)
insert into T values (1,100)
insert into T values (2,100)
insert into T values (3,100)
insert into T values (6,100)
insert into T values (7,100)
insert into T values (9,100)
insert into T values (10,100)
insert into T values (11,100)
insert into T values (12,100)
insert into T values (1,200)
insert into T values (2,200)
insert into T values (3,200)
insert into T values (4,200)
insert into T values (7,201)
insert into T values (9,202)
insert into T values (10,202)
insert into T values (12,202)
insert into T values (13,202)
select
max(SrNo) - min(SrNo) + 1 as Length,
StdID,
min(SrNo) as Start,
max(SrNo) as Finish
from (
select
N1.StdID,
N1.SrNo,
count(N2.SrNo) - N1.SrNo
from T as N1, T as N2
where N2.SrNo <= N1.SrNo
and N2.StdID = N1.StdID
group by N1.StdID, N1.SrNo
) as N(StdID,SrNo,gp)
group by StdID,gp
order by StdID, Start
With the sample data above, the results are
Length StdID Start Finish
-- -- -- --
3 100 1 3
2 100 6 7
4 100 9 12
4 200 1 4
1 201 7 7
2 202 9 10
2 202 12 13
I added Start and Finish columns to the result,
but you can remove them if you don't need them.
-- Steve Kass
-- Drew University
-- Ref: E7A35D3E-1397-42E8-B350-7FEC557655E2
kishor wrote:

>Hi all,
>I am sorry… that I could not explain you my problem in my previous mail.
>In my table I have only two columns. One is srNO and other one is stdID or
>StudentID. This table will contain records of multiple students but for
>example I am considering only one ie 100 only.
> In srNo column there is some data about his absences it is a only day of
>month. I just want to count how many times student was continuously
>absent/present so if my data is like this
>SrNo StdID
>-- --
>1 100
>2 100
>3 100
>6 100
>7 100
>9 100
>10 100
>11 100
>12 100
>as 4,5, 8 … records are missing. I will start counting from top as 4 is
>missing I wll stop at 3 count as 3 next I will start from 6 and stop at 7
>count is 2 .. and so on…..
>so my out put will be like this for a stident 100
>3 100
>2 100
>4 100
>and for multiple students
>3 100
>2 100
>4 100
>1 101
>4 102
>2 103
>
>
>this is a simple problem of one big problem
>I hope This is now clear.
>Waiing for help
>Kishor
>|||Try,
use northwind
go
create table t (
SrNo int not null check (SrNo > 0),
StdID int not null,
constraint pk_t primary key (StdID, SrNo)
)
set nocount on
insert into t values(1, 100)
insert into t values(2, 100)
insert into t values(3, 100)
insert into t values(6, 100)
insert into t values(7, 100)
insert into t values(9, 100)
insert into t values(10, 100)
insert into t values(11, 100)
insert into t values(12, 100)
insert into t values(5, 101)
insert into t values(4, 102)
insert into t values(5, 102)
insert into t values(6, 102)
insert into t values(7, 102)
insert into t values(21, 103)
insert into t values(22, 103)
set nocount off
go
create view my_view
as
select
StdID,
SrNo,
(
select
coalesce(min(b.SrNo), -1)
from
t as b
where
b.StdID = a.StdID
and b.SrNo > a.SrNo
and (b.SrNo - (select max(c.SrNo) from t as c where c.StdID = b.StdID and
c.SrNo < b.SrNo)) > 1
) as group_id
from
t as a
go
select
StdID,
count(*)
from
my_view
group by
StdID,
group_id
order by
StdID,
min(SrNo)
go
drop view my_view
go
drop table t
go
AMB
"kishor" wrote:

> Hi all,
> I am sorry… that I could not explain you my problem in my previous mail.
> In my table I have only two columns. One is srNO and other one is stdID or
> StudentID. This table will contain records of multiple students but for
> example I am considering only one ie 100 only.
> In srNo column there is some data about his absences it is a only day of
> month. I just want to count how many times student was continuously
> absent/present so if my data is like this
> SrNo StdID
> -- --
> 1 100
> 2 100
> 3 100
> 6 100
> 7 100
> 9 100
> 10 100
> 11 100
> 12 100
> as 4,5, 8 … records are missing. I will start counting from top as 4 is
> missing I wll stop at 3 count as 3 next I will start from 6 and stop at 7
> count is 2 .. and so on…..
> so my out put will be like this for a stident 100
> 3 100
> 2 100
> 4 100
> and for multiple students
> 3 100
> 2 100
> 4 100
> 1 101
> 4 102
> 2 103
>
>
> this is a simple problem of one big problem
> I hope This is now clear.
> Waiing for help
> Kishor|||Hi Steve!
Can you check this post, please?
http://www.microsoft.com/technet/co...>
4&sloc=en-us
Thanks,
Alejandro Mesa
"Steve Kass" wrote:

> Kishor,
> I think this will do what you want.
> create table T (
> SrNo int not null,
> StdID int not null,
> primary key (StdID, SrNo)
> )
> insert into T values (1,100)
> insert into T values (2,100)
> insert into T values (3,100)
> insert into T values (6,100)
> insert into T values (7,100)
> insert into T values (9,100)
> insert into T values (10,100)
> insert into T values (11,100)
> insert into T values (12,100)
> insert into T values (1,200)
> insert into T values (2,200)
> insert into T values (3,200)
> insert into T values (4,200)
> insert into T values (7,201)
> insert into T values (9,202)
> insert into T values (10,202)
> insert into T values (12,202)
> insert into T values (13,202)
> select
> max(SrNo) - min(SrNo) + 1 as Length,
> StdID,
> min(SrNo) as Start,
> max(SrNo) as Finish
> from (
> select
> N1.StdID,
> N1.SrNo,
> count(N2.SrNo) - N1.SrNo
> from T as N1, T as N2
> where N2.SrNo <= N1.SrNo
> and N2.StdID = N1.StdID
> group by N1.StdID, N1.SrNo
> ) as N(StdID,SrNo,gp)
> group by StdID,gp
> order by StdID, Start
> With the sample data above, the results are
> Length StdID Start Finish
> -- -- -- --
> 3 100 1 3
> 2 100 6 7
> 4 100 9 12
> 4 200 1 4
> 1 201 7 7
> 2 202 9 10
> 2 202 12 13
>
> I added Start and Finish columns to the result,
> but you can remove them if you don't need them.
> -- Steve Kass
> -- Drew University
> -- Ref: E7A35D3E-1397-42E8-B350-7FEC557655E2
> kishor wrote:
>
>|||Done! Thanks for pointing me there.
SK
Alejandro Mesa wrote:
>Hi Steve!
>Can you check this post, please?
>http://www.microsoft.com/technet/co...
f4&sloc=en-us
>Thanks,
>Alejandro Mesa
>"Steve Kass" wrote:
>
>sql