help on SQL syntax ?

Dear all,
I am not so familar with SQL syntax.
Is there a good link where keywords and syntax are clearly explain ?
I am running SQL server 2000 Sp4 on server 2003.
regards
sergeHi serge,
read the "BOL"
http://www.aspfaq.com/show.asp?id=2229
HTH, Jens Suessmeyer,|||Hi
Books online is a good source for a lot of information and should be the
first thing to look at. If it is not in your SQL Server program group then
you can download it from
http://www.microsoft.com/downloads/...&displaylang=en
John
"serge calderara" wrote:

> Dear all,
> I am not so familar with SQL syntax.
> Is there a good link where keywords and syntax are clearly explain ?
> I am running SQL server 2000 Sp4 on server 2003.
> regards
> serge|||If BOL is not your thing you might try this free book:
http://www.managedtime.com/freesqlbook.php3
Msql

Help on SQL Reporting Services

Can someone please explain what is required to install this Software ? I mean , anything special needed at all or I can just slap it on to any Win2K server . I am assuming it doesn't have to be on the same server as SQLIn case you are not concerned about the cost, you don't have to install MS Reporting Services and MS SQL Server on the same machine (you will need extra SQL Server license though). In case you'd like to create web-based reports only, you may fiind LGX Report interesting (it is free): www.FreeReporting.com.

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