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 Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Help on joining tables

Say I have three tables (just using Access for this one).

- inTheNews
- pressReleases
- events

Each for the most part, has identical columns. But, each also has one or two that are unique.

The primary key (unique identifier) in each is set to AutoNumber.

Is there a way to set it up so that the AutoNumber recognizes the numbers from the other tables and doesn't produce numbers identical to records in those tables. I would like to make sure each record in each of these tables has a unique identifier -- unique to all three tables.

Does that make sense?

Thanks.There isn't a well defined way to do this, although there are several different approaches that work reasonably well.

I think that the best way to handle this kind of problem is to create a "parent" table that has the common columns, with an autonumber as its primary key. Then create three sub-tables that have the unique columns for each of the sub-types, and create a (foreign key) relationship between these tables and the parent table.

This reduces redundancy, and it makes it easier to be sure that the autonumber values are only used by one of the sub-types. It isn't perfect, but it is probably the best you can do "out of the box".

-PatP

Help Needed

Hi All
In a table i m having two columns timein and timeout
i want to find out the no of hrs or days from timeout and timein
plz help me
this is the data

timein timeout
9/7/2007 4:44:57 PM 24/7/2007 10:55:11 AM
6/7/2007 10:59:51 AM 10/7/2007 12:00:03 PM

Waiting for valuable reply

BabaIf i understand you correctly, you may be looking at the DATEDIFF function (explained in Books Online).

Something along the lines of SELECT DATEDIFF(D, timein, timeout) will give you the number of days difference between the 2 columns.

HTH
|||

Baba:

Do you mean the difference between columns of the same row or do you mean the difference between columns of different consecutive rows? Also, are you using SQL 2005?

|||i got it. thank u

Baba
|||Hi Kent

We are using sqlserver2005

i got the ans but one doubt is there when i tried to get the data in months it is displaying 0

and one more doubt is i want gethrs,min,ss if poss day is it possible to include all these formats in date_diff

Waiting for u r valuable reply

|||Kent
u scolded me or u need the time to reply

Baba
|||

It was not intended as scolding at all; but I need time to adjust to the requirements. Please forgive. Maybe something like this:

Code Snippet

set dateformat dmy

declare @.aTable table
( timein datetime,
timeout datetime
)
insert into @.aTable
select '9/7/2007 4:44:57 PM', '24/7/2007 10:55:11 PM' union all
select '6/7/2007 10:59:51 AM', '10/7/2007 12:00:03 PM'

select timein,
timeout,
datediff(day, 0, timeout - timein) as Days,
datediff(hh, 0, timeout - timein) % 24 as Hours,
datediff(mi, 0, timeout - timein) % 60 as Minutes,
datediff(ss, 0, timeout - timein) % 60 as Seconds
from @.aTable

/*
timein timeout Days Hours Minutes Seconds
-- - -- --
2007-07-09 16:44:57.000 2007-07-24 22:55:11.000 15 6 10 14
2007-07-06 10:59:51.000 2007-07-10 12:00:03.000 4 1 0 12
*/

|||Thank u for u r reply

Baba

help me get rid of the noise words please!

Hi,
I am a programmer with no SQLServer dba experience and have set up a full
text index on two columns in the same table. The index is working fine
except for when a noise word is used in a search. My problem is that I have
been unable to remove the noise words. I have searched the drive and deleted
everything from all of the noise.enu files - they are zero length. I have
gone to a command prompt and typed "net stop mssearch" and then "net start
mssearch". After stopping and starting mssearch, I have repopulated my
indexes by going to the table where the indexes are and using the
edit-full-text indexing wizard. I'm sure there is a better way but at the
completion of the wizard it drops the index and rebuilds it. I do have a
scheduled repopulation that runs every morning. When I open this job it has
the following code:
use [central] exec sp_fulltext_table N'[dbo].[Product]', N'start_full'.
This has been running successfully for quite a while.
What am I doing incorrectly that I can't get rid of the noise words?
Your help would be greatly appreciated!
[code generated from ASP page]
select * from Product where contains(Desc1, 'dvd AND r') or contains(Desc2,
'dvd AND r')
[System Summary]
Item Value
OS Name Microsoft Windows 2000 Advanced Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Manufacturer Hewlett-Packard
System Model HP NetServer
System Type X86-based PC
Processor x86 Family 6 Model 8 Stepping 10 GenuineIntel ~933 Mhz
BIOS Version 10/15/01
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition2
Locale United States
Time Zone Eastern Standard Time
Total Physical Memory 1,310,188 KB
Available Physical Memory 30,864 KB
Total Virtual Memory 4,435,520 KB
Available Virtual Memory 1,987,148 KB
Page File Space 3,125,332 KB
Page File C:\pagefile.sys
you have several options
1) use a freetext query, but this may return results that are too fuzzy for
you.
2) strip the noise words out by using a client side script - search this
newsgroup for searchpage1.htm
3) using tsql parsing on the server possibly a UDF
4) write an extended proc to parse them out
5) I'm wondering if you got the correct noise file. noise.eng is for British
English, noise.enu is for American English. Change the noise word lists you
find in
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
You will have to stop and start the mssearch service to make these changes.
"woodysapsucker" <woody@.rohland.org> wrote in message
news:OMbPJgoEEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> I am a programmer with no SQLServer dba experience and have set up a full
> text index on two columns in the same table. The index is working fine
> except for when a noise word is used in a search. My problem is that I
have
> been unable to remove the noise words. I have searched the drive and
deleted
> everything from all of the noise.enu files - they are zero length. I have
> gone to a command prompt and typed "net stop mssearch" and then "net start
> mssearch". After stopping and starting mssearch, I have repopulated my
> indexes by going to the table where the indexes are and using the
> edit-full-text indexing wizard. I'm sure there is a better way but at the
> completion of the wizard it drops the index and rebuilds it. I do have a
> scheduled repopulation that runs every morning. When I open this job it
has
> the following code:
> use [central] exec sp_fulltext_table N'[dbo].[Product]', N'start_full'.
> This has been running successfully for quite a while.
> What am I doing incorrectly that I can't get rid of the noise words?
> Your help would be greatly appreciated!
>
> [code generated from ASP page]
> select * from Product where contains(Desc1, 'dvd AND r') or
contains(Desc2,
> 'dvd AND r')
>
> [System Summary]
> Item Value
> OS Name Microsoft Windows 2000 Advanced Server
> Version 5.0.2195 Service Pack 4 Build 2195
> OS Manufacturer Microsoft Corporation
> System Manufacturer Hewlett-Packard
> System Model HP NetServer
> System Type X86-based PC
> Processor x86 Family 6 Model 8 Stepping 10 GenuineIntel ~933 Mhz
> BIOS Version 10/15/01
> Windows Directory C:\WINNT
> System Directory C:\WINNT\system32
> Boot Device \Device\Harddisk0\Partition2
> Locale United States
> Time Zone Eastern Standard Time
> Total Physical Memory 1,310,188 KB
> Available Physical Memory 30,864 KB
> Total Virtual Memory 4,435,520 KB
> Available Virtual Memory 1,987,148 KB
> Page File Space 3,125,332 KB
> Page File C:\pagefile.sys
>
|||Thanks Hilary,
I am trying to still use the noise words in the queries. I would like the
query to be on "dvd r" not just "dvd". That is why I've been trying to empty
out the noise word files.
I am from the US but also emptied noise.eng files - just incase - and reran
the indexing.
Any other suggestions?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eYFPX1oEEHA.688@.tk2msftngp13.phx.gbl...
> you have several options
> 1) use a freetext query, but this may return results that are too fuzzy
for
> you.
> 2) strip the noise words out by using a client side script - search this
> newsgroup for searchpage1.htm
> 3) using tsql parsing on the server possibly a UDF
> 4) write an extended proc to parse them out
> 5) I'm wondering if you got the correct noise file. noise.eng is for
British
> English, noise.enu is for American English. Change the noise word lists
you
> find in
> C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
> You will have to stop and start the mssearch service to make these
changes.
> "woodysapsucker" <woody@.rohland.org> wrote in message
> news:OMbPJgoEEHA.3344@.tk2msftngp13.phx.gbl...
full
> have
> deleted
have
start
the
> has
> contains(Desc2,
>