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

Help on Date

Hi,
I'm new to SQL.
I like to pick up the previous date. How can I do this?
I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a query
to get the previous day's date. How do I convert the date to the previous
one?
For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
from the system date.
Please help,
Thanks,
Sarah>> I like to pick up the previous date. How can I do this?
Look up DATEADD function in SQL Server Books Online. The second argument for
this function can take negative values.
Anith|||Use DATEADD. something like this SELECT DATEADD(DAY, -1, GETDATE()) AS
PreviousDay
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
query
> to get the previous day's date. How do I convert the date to the previous
> one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Thanks so much for Tim and Anith quick response. I will give a try, good
start.
Appreicate
sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23WV1JrG7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Look up DATEADD function in SQL Server Books Online. The second argument
> for this function can take negative values.
> --
> Anith
>|||Hi,
I was in a view design, how can I convert GETDATE() TO day of the month,
like 1-31.
is there a function that I can use like MONTH() etc.
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
> query to get the previous day's date. How do I convert the date to the
> previous one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Use DATEPART
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||Try using the DATEPART function.
"SG" wrote:

> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>
>|||Thanks, that works.
"Devers" <Devers@.discussions.microsoft.com> wrote in message
news:86461C90-7674-4F8B-8B70-A195C546218C@.microsoft.com...
> Try using the DATEPART function.
> "SG" wrote:
>|||Hi Everyone,
Actually DATEADD() works for me. Thanks everyone for your help.
Appreciate it and have a good day,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>