Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
> news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
>> If the index were clustered on the date then it certainly will help.
>> But I would not rush to cluster on the date, the choice of a proper
>> clustered index is not that simple.
>> If the date ranges are very narrow compared to the overall table then
>> a non-clustered index on the date may very well be chosen by the
>> optimizer and save time. The optimizer is going to estimate the
>> number of pages it will have to read in random order to use the index,
>> and compare that to the number of pages it will have to read to scan
>> the entire table. The smaller number wins.
>> Without more information on the table, data and query I don't know
>> what else to say.
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdb
On Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT
|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb
|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com :
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

help on Indexes

Hi all,
I haven't add indexes to a database for a long long time so I've
completely forgotten how to go about designing an index.
Say I have the following query (which is run often):
SELECT
bl.TotalRooms, bt.TypeName, u.UserWorkTel,
p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode,
p.idPublicDetails,
p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef,
u.idUserType, lt.idAdType,
pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
lc.idLetCategory, lc.CategoryName,
c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath)
AS 'Logo', al.idOffice,
FROM Property p
JOIN PropertyType pt
ON p.idPType = pt.idPType
JOIN Country c
ON p.idCountry = c.idCountry
JOIN District d
ON p.idDistrict = d.idDistrict
JOIN CountyCity cy
ON p.idCountyCity = cy.idCountyCity
JOIN Frequency fr
ON p.idFrequency = fr.idFrequency
LEFT OUTER JOIN BedroomList bl
ON p.idList = bl.idList
LEFT OUTER JOIN BedroomType bt
ON bl.idBedroomType = bt.idBedroomType
LEFT OUTER JOIN PropertyPicture pp
ON p.idList = pp.idList AND pp.Thumbnail = 1
LEFT OUTER JOIN LetCategoryList lcl
ON p.idList = lcl.idList
LEFT OUTER JOIN LetCategory lc
ON lcl.idLetCategory = lc.idLetCategory
LEFT OUTER JOIN Agent a
ON a.idUser = p.idCreatedBy
LEFT OUTER JOIN AgencyLogo al
ON al.idOffice = a.idOffice
JOIN Users u ON
u.idUser = p.idCreatedBy
LEFT OUTER JOIN ListingType lt
ON lt.idList = p.idList
JOIN PropContactList pc
ON pc.idList = p.idList
JOIN Users us
ON us.idUser = pc.idMainContact
WHERE p.idCRule = 1
GROUP BY p.idList, bl.TotalRooms, p.ListRef,
al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName,
p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType,
fr.FrequencyName, d.DistrictPostcode,
pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel,
c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName,
pp.PicPath, al.idOffice, lt.idAdType
ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc
... where do I begin looking to add indexes for this query '
(note that I
have primary keys on each table on the columns begining with 'id' so
therefore I already have one clustered index on each table)
Any advice much appreciated!
Peter
--
fiddlewidawiddumIs your p.IDCRule greatly disparate? In other words, are there more
than a couple distinct values in it (1-10 as opposed to 1-3)? If so,
it might be a candidate for an index. I'd also look at indexing the
foreign key columns on each table (e.g., p.idCreatedBy)
Do you know how to work with execution plans? They'll reveal a lot
about what indexes are being used, and how the addition of an index
will alter the performance.
Be aware that adding an index can impact performance on INSERTs and
UPDATEs, so be careful and don't over-do it.
HTH,
Stu|||Stimp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Stimp" <ren@.spumco.com> wrote in message
news:slrne3206s.4or.ren@.carbon.redbrick.dcu.ie...
> Hi all,
> I haven't add indexes to a database for a long long time so I've
> completely forgotten how to go about designing an index.
> Say I have the following query (which is run often):
> SELECT
> bl.TotalRooms, bt.TypeName, u.UserWorkTel,
> p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode,
> p.idPublicDetails,
> p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef,
> u.idUserType, lt.idAdType,
> pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
> lc.idLetCategory, lc.CategoryName,
> c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath)
> AS 'Logo', al.idOffice,
> FROM Property p
> JOIN PropertyType pt
> ON p.idPType = pt.idPType
> JOIN Country c
> ON p.idCountry = c.idCountry
> JOIN District d
> ON p.idDistrict = d.idDistrict
> JOIN CountyCity cy
> ON p.idCountyCity = cy.idCountyCity
> JOIN Frequency fr
> ON p.idFrequency = fr.idFrequency
> LEFT OUTER JOIN BedroomList bl
> ON p.idList = bl.idList
> LEFT OUTER JOIN BedroomType bt
> ON bl.idBedroomType = bt.idBedroomType
> LEFT OUTER JOIN PropertyPicture pp
> ON p.idList = pp.idList AND pp.Thumbnail = 1
> LEFT OUTER JOIN LetCategoryList lcl
> ON p.idList = lcl.idList
> LEFT OUTER JOIN LetCategory lc
> ON lcl.idLetCategory = lc.idLetCategory
> LEFT OUTER JOIN Agent a
> ON a.idUser = p.idCreatedBy
> LEFT OUTER JOIN AgencyLogo al
> ON al.idOffice = a.idOffice
> JOIN Users u ON
> u.idUser = p.idCreatedBy
> LEFT OUTER JOIN ListingType lt
> ON lt.idList = p.idList
> JOIN PropContactList pc
> ON pc.idList = p.idList
> JOIN Users us
> ON us.idUser = pc.idMainContact
> WHERE p.idCRule = 1
> GROUP BY p.idList, bl.TotalRooms, p.ListRef,
> al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName,
> p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType,
> fr.FrequencyName, d.DistrictPostcode,
> pt.PTypeName, lcl.OccasionalWends, lcl.WendPrice,
> lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel,
> c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName,
> pp.PicPath, al.idOffice, lt.idAdType
> ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc
>
> ... where do I begin looking to add indexes for this query '
> (note that I
> have primary keys on each table on the columns begining with 'id' so
> therefore I already have one clustered index on each table)
> Any advice much appreciated!
> Peter
> --
> fiddlewidawiddum|||The rule of thumb which I've found useful with indexes is to create the
ones that allow the optimiser to quickly bound the size of the result
set. So for example if you were passing a parameter such as a daterange
into your where clause, this would be a good candidate for the index as
it would probably cut that table down to a few rows, such that all
other operations on that result set from then on would be trivial.
in your case however you don't seem to be passing anything to really
cut down on the size of the results, so as Stu said, it's the execution
plans which will really show you where the best improvements can be
made (CTRL-K in query analyser (SQL 2000) and CTRL-M in SQL 2005)
Cheers
Will|||Run the profiler with output to a file.
and run this query along with any other query that you run on these tables.
Take the result file and pass it to the index tuning wizard.
You should get a fair idea on what indexes to use.|||On Mon, 3 Apr 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> Run the profiler with output to a file.
> and run this query along with any other query that you run on these tables
.
> Take the result file and pass it to the index tuning wizard.
> You should get a fair idea on what indexes to use.
this seems to do the trick alright.. mucho gracias y'all!
--
fiddlewidawiddum|||Stimp
I'd not rely on ITW ,because on more complex (involved many tables)
queries it does not provide a good advice. Instead , examine an execution
plan of the query , see if the optimier was available to use idexes.
"Stimp" <ren@.spumco.com> wrote in message
news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> this seems to do the trick alright.. mucho gracias y'all!
> --
> fiddlewidawiddum|||On Mon, 3 Apr 2006 Uri Dimant <urid@.iscar.co.il> wrote:
> Stimp
> I'd not rely on ITW ,because on more complex (involved many tables)
> queries it does not provide a good advice. Instead , examine an execution
> plan of the query , see if the optimier was available to use idexes.
Hi Uri,
I looked at the execution plan and couldn't make head-or-tail of it, and
since I was on a tight schedule I didn't have time to research how to
translate execution plans.
Is there a tutorial on how to read execution plans, and so use them to
derive indexes?
Cheers,
Peter

>
> "Stimp" <ren@.spumco.com> wrote in message
> news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
>
fiddlewidawiddum|||Hi
Vist at http://www.sql-server-performance.c...performance.asp and
you'll find lots of good info
"Stimp" <ren@.spumco.com> wrote in message
news:slrne34eqg.n0v.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Uri Dimant <urid@.iscar.co.il> wrote:
> Hi Uri,
> I looked at the execution plan and couldn't make head-or-tail of it, and
> since I was on a tight schedule I didn't have time to research how to
> translate execution plans.
> Is there a tutorial on how to read execution plans, and so use them to
> derive indexes?
> Cheers,
> Peter
>
>
> --
> fiddlewidawiddum

Help on grouping time

Hi all,
Having this table
ID ServerTime User
1 2004-05-18 14:44:30.000 A
2 2004-05-18 14:44:31.000 A
3 2004-04-30 08:28:06.000 B
4 2004-04-30 08:29:19.000 B
5 2004-04-30 08:30:46.000 B
6 2004-04-30 08:30:47.000 B
7 2004-04-30 08:30:58.000 B
8 2004-04-30 14:54:28.000 A
9 2004-04-30 14:54:29.000 A
10 2004-04-30 14:54:37.000 A
11 2004-05-13 16:54:58.000 C
12 2004-05-18 12:29:42.000 C
13 2004-05-18 12:29:43.000 C
14 2004-05-18 12:35:41.000 B
15 2004-05-18 16:55:54.000 B
16 2004-05-18 20:55:27.000 C
what is the best way to calculate (compute) amout of time for each user
(without cursor)?
ie
A has DateDiff(row2,1) + DateDiff(row10,8)
B has DateDiff(row7,3) + DateDiff(row15,14)
and so on...
Any suggestion'?
Thanks in advanceHow about :
SELECT User, dateidiff(day,min(ServerTime), max(ServerTime)
FROM ThisTable
GROUP BY User
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 08:13:47 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Hi all,
>Having this table
>ID ServerTime User
>1 2004-05-18 14:44:30.000 A
>2 2004-05-18 14:44:31.000 A
>3 2004-04-30 08:28:06.000 B
>4 2004-04-30 08:29:19.000 B
>5 2004-04-30 08:30:46.000 B
>6 2004-04-30 08:30:47.000 B
>7 2004-04-30 08:30:58.000 B
>8 2004-04-30 14:54:28.000 A
>9 2004-04-30 14:54:29.000 A
>10 2004-04-30 14:54:37.000 A
>11 2004-05-13 16:54:58.000 C
>12 2004-05-18 12:29:42.000 C
>13 2004-05-18 12:29:43.000 C
>14 2004-05-18 12:35:41.000 B
>15 2004-05-18 16:55:54.000 B
>16 2004-05-18 20:55:27.000 C
>
>what is the best way to calculate (compute) amout of time for each user
>(without cursor)?
>ie
>A has DateDiff(row2,1) + DateDiff(row10,8)
>B has DateDiff(row7,3) + DateDiff(row15,14)
>and so on...
>Any suggestion'?
>Thanks in advance|||Try this
declare @.mytable table(ID int,
ServerTime datetime,
[User] char(1))
insert into @.mytable(ID,ServerTime,[User])
select 1 ,'20040518 14:44:30.000','A' union all
select 2 ,'20040518 14:44:31.000','A' union all
select 3 ,'20040430 08:28:06.000','B' union all
select 4 ,'20040430 08:29:19.000','B' union all
select 5 ,'20040430 08:30:46.000','B' union all
select 6 ,'20040430 08:30:47.000','B' union all
select 7 ,'20040430 08:30:58.000','B' union all
select 8 ,'20040430 14:54:28.000','A' union all
select 9 ,'20040430 14:54:29.000','A' union all
select 10 ,'20040430 14:54:37.000','A' union all
select 11 ,'20040513 16:54:58.000','C' union all
select 12 ,'20040518 12:29:42.000','C' union all
select 13 ,'20040518 12:29:43.000','C' union all
select 14 ,'20040518 12:35:41.000','B' union all
select 15 ,'20040518 16:55:54.000','B' union all
select 16 ,'20040518 20:55:27.000','C'
select sum(Seconds),[User]
from (
select datediff(second,
min(ServerTime),
max(ServerTime)) as Seconds,
[User]
from (
select
t1.ServerTime,
t1.[User],
t1.ID-(select count(*)
from @.mytable t2
where t2.[User]=t1.[User]
and t2.ID<=t1.ID) as Rn
from @.mytable t1) X1
group by [User],Rn) X2
group by [User]|||thanks for your response Roy but:
I have some dubt ...ie
grouping make sets for A, B and C...then calculate min and max for
each group
so for A datediff works on rows 10 and 1 (max and min for A) but the
result it is not correct......
Better:
A has DateDiff(row10,1) (with your select)
but in general different from
A has DateDiff(row2,1) + DateDiff(row10,8)|||I think I might have a glimmer of what you are asking for now.
First, you are making pairs of rows, and calculating a difference in
time for each pair. No row is ignored.
Then, based on the results from all the pairs matching a given User,
you want to add up those results.
The difficulty is coming up with the pairings. All I can see is that
the "first" is the first in a pair, the "next" is the next, and then
the "next after that" is another "first", etc. There is nothing on
the rows to identify their position in the pairings, and a missing row
will throw everything very far off.
For a while I thought the following returned what you expected, though
it may not be performaing the datediff calculation in the direction or
at the precision you want. But on closer examination of your test
data, and the pairings you made manually, I no longer have any faith
that I understand the rules for pairing.
CREATE TABLE ThisTable
(ID tinyint, ServerTime datetime, Usr char(1))
GO
INSERT ThisTable values(1, '2004-05-18 14:44:30.000', 'A')
INSERT ThisTable values(2, '2004-05-18 14:44:31.000', 'A')
INSERT ThisTable values(3, '2004-04-30 08:28:06.000', 'B')
INSERT ThisTable values(4, '2004-04-30 08:29:19.000', 'B')
INSERT ThisTable values(5, '2004-04-30 08:30:46.000', 'B')
INSERT ThisTable values(6, '2004-04-30 08:30:47.000', 'B')
INSERT ThisTable values(7, '2004-04-30 08:30:58.000', 'B')
INSERT ThisTable values(8, '2004-04-30 14:54:28.000', 'A')
INSERT ThisTable values(9, '2004-04-30 14:54:29.000', 'A')
INSERT ThisTable values(10, '2004-04-30 14:54:37.000', 'A')
INSERT ThisTable values(11, '2004-05-13 16:54:58.000', 'C')
INSERT ThisTable values(12, '2004-05-18 12:29:42.000', 'C')
INSERT ThisTable values(13, '2004-05-18 12:29:43.000', 'C')
INSERT ThisTable values(14, '2004-05-18 12:35:41.000', 'B')
INSERT ThisTable values(15, '2004-05-18 16:55:54.000', 'B')
INSERT ThisTable values(16, '2004-05-18 20:55:27.000', 'C')
SELECT Usr, sum(datediff(second,StartTime,EndTime))
FROM (select T1.Usr,
T1.ServerTime as EndTime,
MAX(T2.ServerTime) as StartTIme
from ThisTable as T1
join ThisTable as T2
on T1.Usr = T2.Usr
and T1.ServerTime > T2.ServerTime
group by T1.Usr, T1.ServerTime
having count(*) % 2 = 1) as X
GROUP BY Usr
ORDER BY 1
Lets just look at the data for A. From the test data supplied:
id Usr ServerTime
-- -- --
1 A 2004-05-18 14:44:30.000
2 A 2004-05-18 14:44:31.000
8 A 2004-04-30 14:54:28.000
9 A 2004-04-30 14:54:29.000
10 A 2004-04-30 14:54:37.000
But that is ordered by id. I made the assumption that we should be
ordering by datetime:
id Usr ServerTime
-- -- --
8 A 2004-04-30 14:54:28.000
9 A 2004-04-30 14:54:29.000
10 A 2004-04-30 14:54:37.000
1 A 2004-05-18 14:44:30.000
2 A 2004-05-18 14:44:31.000
By THAT rule I was pairing the rows quite differently from what you
requested.
On a large table performance will be horrible. This is because the
inner query must do a "half-cartesian" join within each user; as the
number of rows per user increases, the work involved will get worse at
the rate of half the square of the number of rows.
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 09:11:19 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>thanks for your response Roy but:
>I have some dubt ...ie
>grouping make sets for A, B and C...then calculate min and max for
>each group
>so for A datediff works on rows 10 and 1 (max and min for A) but the
>result it is not correct......
>Better:
>A has DateDiff(row10,1) (with your select)
>but in general different from
>A has DateDiff(row2,1) + DateDiff(row10,8)|||If ID 4 had a ServerTime of 2004-04-30 08:27 (putting it earlier than ID 3),
would that alter your desired results? In other words, it looks like you're
doing calculations on contiguous rows of a user, but contiguous based on ID
order. Without more understanding of what you're doing, it would seem more
meaningful if the contiguous groups were based on ServerTime order, not ID.
Vern Rabe
"2006 Flauzer" wrote:

> Hi all,
> Having this table
> ID ServerTime User
> 1 2004-05-18 14:44:30.000 A
> 2 2004-05-18 14:44:31.000 A
> 3 2004-04-30 08:28:06.000 B
> 4 2004-04-30 08:29:19.000 B
> 5 2004-04-30 08:30:46.000 B
> 6 2004-04-30 08:30:47.000 B
> 7 2004-04-30 08:30:58.000 B
> 8 2004-04-30 14:54:28.000 A
> 9 2004-04-30 14:54:29.000 A
> 10 2004-04-30 14:54:37.000 A
> 11 2004-05-13 16:54:58.000 C
> 12 2004-05-18 12:29:42.000 C
> 13 2004-05-18 12:29:43.000 C
> 14 2004-05-18 12:35:41.000 B
> 15 2004-05-18 16:55:54.000 B
> 16 2004-05-18 20:55:27.000 C
>
> what is the best way to calculate (compute) amout of time for each user
> (without cursor)?
> ie
> A has DateDiff(row2,1) + DateDiff(row10,8)
> B has DateDiff(row7,3) + DateDiff(row15,14)
> and so on...
> Any suggestion'?
> Thanks in advance
>|||Excuse me if I have not been very clear but over the difficulty of the
question the English is not my first language (..it should be enough
clear :)))
The context of the question is analyze a "log" table:
ServerTime and User are the most important fields and substantially the
table records the
activities that every user make:First, we have "activities" for user A,
then user B and so on...
Therefore, there can be n rows for the user A m rows for the user B
etc... etc..
and these activities are adjoining and repeated (we not known how many
rows
As example, the working sequence could be A--C and then again =C1-B
etc...
The most obvious question is: can I count (with all the limitations of
the case) how much time to debit for the user =C1, how much for user B
and so on...
I looked for the best way to do this, and I've heard that "cursor" is
not a good way...
If someone has some suggestion=20
thanks in advance....|||If the ONLY activities that are recorded are the start and end of
whatever it is you are measuring, then something along the lines of
what I already posted can work.
If there is more information on the log that can be used to identify
the start and end of whatever it is you are charging for, then there
is probably a simpler (and more efficient) answer than what I posted.
Roy Harvey
Beacon Falls, CT
If there are other types of a
On 7 Jun 2006 13:06:18 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Excuse me if I have not been very clear but over the difficulty of the
>question the English is not my first language (..it should be enough
>clear :)))
>The context of the question is analyze a "log" table:
>ServerTime and User are the most important fields and substantially the
>table records the
>activities that every user make:First, we have "activities" for user A,
>then user B and so on...
>Therefore, there can be n rows for the user A m rows for the user B
>etc... etc..
>and these activities are adjoining and repeated (we not known how many
>rows
>As example, the working sequence could be A--C and then again -B
>etc...
>The most obvious question is: can I count (with all the limitations of
>the case) how much time to debit for the user , how much for user B
>and so on...
>I looked for the best way to do this, and I've heard that "cursor" is
>not a good way...
>If someone has some suggestion
>thanks in advance....|||Yes, Roy you are right...
Unfortunately i have no other useful info......
discovering min and max of every "group" is the key... something like
this
select T1.[User],
MIN(T1.ServerTime) as StartTIme ,
MAX(T2.ServerTime) as EndTime
from TB_LOG as T1
join TB_LOG as T2
on T1.[User] = T2.[User] AND
T1.ServerTime >= T2.ServerTime
group by T1.[User]
but this works only if i-user works only one time....otherwise
grouping set alter min and max values.....
Thanks for your useful thoughts|||If the times are what you have to work with, look at the query I
posted earlier.
Roy
On 7 Jun 2006 14:18:19 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Yes, Roy you are right...
>Unfortunately i have no other useful info......
>discovering min and max of every "group" is the key... something like
>this
>select T1.[User],
> MIN(T1.ServerTime) as StartTIme ,
> MAX(T2.ServerTime) as EndTime
> from TB_LOG as T1
> join TB_LOG as T2
> on T1.[User] = T2.[User] AND
> T1.ServerTime >= T2.ServerTime
> group by T1.[User]
>but this works only if i-user works only one time....otherwise
>grouping set alter min and max values.....
>Thanks for your useful thoughts

Help on elapse time to return data ??

Dear all,
I have build an ASP.net application which calls different store procedure.
When my customer request data from store procedure, I would like to display
on the page, the time it takes to return data.
How to do that ?
regards
sergeSomething like that
create proc myproc1
as
--here is your body's code
--usage
declare @.dt datetime
set @.dt=getdate()
exec myproc1
select datediff(s,@.dt,getdate())
drop proc myproc1
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||To add to Uri's response, you can also calculate the elapsed time in your
application. C# example:
DateTime startTime = DateTime.Now;
//execute query
TimeSpan duration = DateTime.Now.Subtract(startTime);
Response.Write(duration.ToString();
Hope this helps.
Dan Guzman
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||Thnaks dan, I will do that
regards
serge
"Dan Guzman" wrote:

> To add to Uri's response, you can also calculate the elapsed time in your
> application. C# example:
> DateTime startTime = DateTime.Now;
> //execute query
> TimeSpan duration = DateTime.Now.Subtract(startTime);
> Response.Write(duration.ToString();
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
>
>

Help on Dates

I'm new to SQL server...
I'm trying to create a date (only) with no time.
I'm importing the data from navchar to datetime and getting the time stamp. How do I configure the table to show only the date part?
I did not see a date datatype...
ThanksThat creature does not exist. You will have to use the convert function using a style - look at sql server books online under 'Cast and Convert'.
The time value stored when no time is provided is midnight - so you would have a date followed by 00:00:00.000. You might also consider smalldatetime.

For example, using northwind's orders table:

select convert(varchar(10), orderdate,101) from orders|||If my date field is indexed (2-3 million rows), then will the request read all records before converting and selecting?|||/*
--replace "origDateTime" with our datetime value/column

_ You can use datetime column with check constraint
( "origDateTime"=convert(datetime,convert(int,"origDateTime")) )
to ensure that there is no time saved, in the future you could need
time, so you only remove this constraint. Linking to date analysis
table will be done by indexed computed column on table level
( "compOnlyDate" AS convert(int,"origDateTime") )
to get additional precomputed information about date
(year,month,day in week,season,...) faster.
_ If you NEVER use time, consider using only int
or smallint ( convert(int,getdate())-30000 ),
but be prepared on problems with user reports and applications.
*/

Help On Buffer latch timeout

Time out occurred while waiting for buffer latch type 2,bp 0x18b7d40, page 1:11558916), stat 0xb, object ID 9:1842105603:2, EC 0x5862D9C8 : 0, waittime 300. Not continuing to wait.
What does this mean any reason and fix for it..?
ThanksI don't see a reference to the database ID. Usually page reference can be seen in the form 2:1:12312, where 2 is the database id (tempdb in this case), 1 is the file number (1st data file), and the object id (ALL THIS IS RIGHT IF I RECALL CORRECTLY!)

Also, check Buffer Latch Timeouts or Server Sluggishness Occurs When You Remove Procedures From Cache (http://support.microsoft.com/default.aspx?scid=kb;EN-US;309093) for references.

Help needed with SQL UPDATE statement

I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement.....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Thanks!

Eugh

what is the error message?|||The statement looks correct|||Are you sure the problem is with your SQL UPDATE statement? It's obvious that it's part of your program, so I'm wondering if the rest of your code is where the problem lies. Post more codes to see if anyone can find the cause.|||

Sorry to post and run last Friday. Thanks for taking a look at it. I've never written anything in .net for web applications before. I installed IIS and VB.net myself so if the SQL statement looks correct I'm wondering if I have a permissions issue. I gave the ASPNET read/write permissions by right clicking on the DB in windows explorer, properties, security tab, add, (machinename\ASPNET). The DB is in a subfolder under wwwroot. Did I do something wrong there perhaps?

I'm using VB.Net 2003 Standard Edition w/ and Access 2003 DB. My exact error message is...

Server Error in '/Proposal_List' Application.

---

Syntax error in UPDATE statement.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

Source Error:

Line 112: objConn.Open()Line 113:Line 114: objCmd.ExecuteNonQuery()Line 115: objConn.Close()Line 116:

Source File:c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb Line:114

Stack Trace:

[OleDbException (0x80040e14): Syntax error in UPDATE statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) System.Data.OleDb.OleDbCommand.ExecuteNonQuery() Proposal_List.Pnumberlist.dgPnum_UpdateRow(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb:114 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

HERE IS MY COMPLETE CODE

VB............................................................................................................

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

IfNot Page.IsPostBackThen

BindData()

EndIf

EndSub

Sub BindData()

'Create a connection

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

'create a command object for the query

Const strSQLAsString = "SELECT * FROM [tbl-Pnumber_list]"

Dim objCMDAsNew OleDbCommand(strSQL, objConn)

'create a dataadapter

Dim objDAAsNew OleDbDataAdapter

objDA.SelectCommand = objCMD

'Populate a dataset and close the connection

Dim objDSAsNew DataSet

objDA.Fill(objDS)

objConn.Close()

'specify the data source and bind the data

dgPnum.DataSource = objDS

dgPnum.DataBind()

EndSub

Sub dgPnum_EditRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = e.Item.ItemIndex

BindData()

EndSub

Sub dgPnum_UpdateRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

'get info from columns

Dim PMTextBoxAs TextBox = e.Item.Cells(3).Controls(0)

Dim Project_TitleTextBoxAs TextBox = e.Item.Cells(2).Controls(0)

Dim iIDAsInteger = dgPnum.DataKeys(e.Item.ItemIndex)

'update the database

Dim strSQLAsString

'strSQL = "UPDATE tbl-Pnumber_list SET Project_Manager = @.PMParam," & _

'"Project_Title = @.Project_TitleParam" & _

'"WHERE ID = @.IDParam"

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

Dim objCmdAsNew OleDbCommand(strSQL, objConn)

Dim PMParamAsNew OleDbParameter("@.NameParam", OleDbType.VarChar, 200)

Dim Project_TitleParamAsNew OleDbParameter("@.CommentParam", OleDbType.VarChar, 254)

Dim IDParamAsNew OleDbParameter("@.IDParam", OleDbType.Integer, 4)

PMParam.Value = PMTextBox.Text

objCmd.Parameters.Add(PMParam)

Project_TitleParam.Value = Project_TitleTextBox.Text

objCmd.Parameters.Add(Project_TitleParam)

IDParam.Value = iID

objCmd.Parameters.Add(IDParam)

'Issue the SQL command

objConn.Open()

objCmd.ExecuteNonQuery()

objConn.Close()

dgPnum.EditItemIndex = -1

BindData()

EndSub

Sub dgpnum_CancelRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = -1

BindData()

EndSub

HTML....................................................................................................................................

<form id="Form1" method="post" runat="server">
<asp:datagrid id="dgPnum" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 32px" runat="server"
AutoGenerateColumns="False" CellPadding="3" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
BackColor="White" OnCancelCommand="dgPnum_CancelRow" OnUpdateCommand="dgPnum_UpdateRow" OneditCommand="dgPnum_EditRow"
DataKeyField="ID">
<FooterStyle ForeColor="#000066" BackColor="White"></FooterStyle>
<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#669999"></SelectedItemStyle>
<ItemStyle ForeColor="#000066"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" HeaderText="Edit" Edittext="Edit" UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn DataField="ID" HeaderText="ID" Visible="false"></asp:BoundColumn>
<asp:BoundColumn DataField="Proposal_Year" HeaderText="Proposal Year"></asp:BoundColumn>
<asp:BoundColumn DataField="Number" HeaderText="Number"></asp:BoundColumn>
<asp:BoundColumn DataField="Client" HeaderText="Client"></asp:BoundColumn>
<asp:BoundColumn DataField="Project_Title" HeaderText="Project"></asp:BoundColumn>
<asp:BoundColumn DataField="Project_Manager" HeaderText="PM"></asp:BoundColumn>
<asp:BoundColumn DataField="Office" HeaderText="Office"></asp:BoundColumn>
<asp:BoundColumn DataField="Discipline" HeaderText="Discipline"></asp:BoundColumn>
<asp:BoundColumn DataField="F/Q/P" HeaderText="F/Q/P"></asp:BoundColumn>
<asp:BoundColumn DataField="Start_Date" HeaderText="Start Date"></asp:BoundColumn>
<asp:BoundColumn DataField="End_Date" HeaderText="End Date"></asp:BoundColumn>
<asp:BoundColumn DataField="Submitted" HeaderText="Submitted"></asp:BoundColumn>
<asp:BoundColumn DataField="Folder" HeaderText="Folder"></asp:BoundColumn>
<asp:BoundColumn DataField="Job_Number" HeaderText="Job Number"></asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#000066" BackColor="White" Mode="NumericPages"></PagerStyle>
</asp:datagrid></form>

Thanks again!

Cheers

Eugh

|||

OleDB doesn't understand the @. parameters, change them to ? instead.

WHERE something=?

|||

I'm not familiar with the way you're providing the UPDATE parameters in your code. Also, I use C#, so the syntax may be different. Following is a sample code from what I used to do in ASP.NET 1.x. Maybe you can get some ideas from it and translate it to your code. Just keep in mind that with OLE-db (Access), the order of parameter specified in your UPDATE statement is all that matters, not the actual placeholder specified:

OleDbCommand myCmd =new OleDbCommand();

myCmd.Connection = oleDbConnection1;

myCmd.CommandText ="UPDATE TableName SET Date_Upd = ?, Name_Last = ? WHERE Rec_ID = ?";

myCmd.Parameters.Add("Date_Upd", OleDbType.Date).Value =DateTime.Now;

myCmd.Parameters.Add("Name_Last", OleDbType.Char).Value = Name_Last.Text;

myCmd.Parameters.Add("Rec_ID", OleDbType.Integer).Value = Session["REC_ID"].ToString();

myCmd.CommandType =CommandType.Text;

myCmd.Connection.Open();

myCmd.ExecuteNonQuery();

myCmd.Connection.Close();

|||

Good to know that Access doesnt understand the @. parameters, I would have been stuck there for a while. Just to see my DB update I changfed the UPDATE statement to....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = 1"

I didnt change anything else, should that have worked? It didnt.

Also, thanks for the example it helps clear some things up for me.

|||Looks okay, assuming your ID is a numeric field. Also, I'm not 100% sure about using single-quote versus double-quote when you specify a text within Access SQL statement. You can probably play with that to verify.|||Try the same statement again with tbl-Pnumber_list in []'s, `'s, or "'s (I'm not sure how Access quotes it's identifiers). The - causes some RDMSs a fit because, well... It's hard to know you aren't trying to say something strange like... tbl (minus) Pnumber_list, which well, doesn't make a lot of sense.|||

Yeah, wadda know... putting [] around tbl-Pnumber_list worked like a champ. Man it feels great to get outta this rut, but it sure hits ya in the gut knowing how simple the solution was. I'll just make sure I wont tell my boss what it was

Thanks to both of you!

Remember me, because I'm sure I'll be begging for some help again soon.

Cheers!

Eugh

HELP needed with SQL - timetabling problem

I've been scratching my head for long enough, so it's time to humbly ask if someone's done it before (which is of course the case :-):

My database is a travel timetable, and has these four tables:

1. tblCity: CityID (PK), CityName

2. tblStop: StopID (PK), CityID (FK), StopName

3. tblTime: TimeID (PK), StopID(FK), RouteID (FK), arrival, departure

4. tblRoute: RouteID (PK), RouteName, startDate, endDate

I tried to keep it neat and simple. Now I need to do searches on it with two parameters: DestinationCity and TravelDate

Query should return the routes on which the Destination City is if the date falls in between those when the route is operating. I came up with this:

SELECT DISTINCT tblTime.RouteID

FROM (tblCity INNER JOIN tblStop ON tblCity.CityID = tblStop.StopCity)

INNER JOIN (tblRoute INNER JOIN tblTime on tblTime.RouteID=tblRoute.RouteID

WHERE tblRoute.startDate<TravelDate

AND tblRoute.endDate>TravelDate)

ON tblStop.StopID = tblTime.StopID

WHERE CityName='DestinationCity';

When I run this I'm getting no records, while there are some routes that operate on the given date to the chosen destination... Any clues what am I doing wrong? THANKS!

You are not really doing anything wrong travel scheduling uses ANSI SQL time interval current SQL Server does not have it but SQL Server 2005 has it. So the time part of your query is making SQL Server to return no records. I found a UDF(User defined function) that can do it. Try the link below and also check out the Time Tracker starter kit it may have something close to what you want. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||Great! So this IS a problem for me - I'm trying to do this in Access..! Any suggestions how this can be solved in Access - if at all...?|||

I have found sample code using SQL Server time interval DATEDIFF, try the links below now you will get the time and date in between. Sorry it took so long, if you have not found a solution. Hope this helps.

http://databasejournal.com/features/mssql/article.php/3076421
http://www.stanford.edu/~bsuter/sql-datecomputations.html

Help needed with method for SQL

I have been using VB6 for a long time now and had no problems using ADODB.Recordset.

I had a module to which I would send my recordset (byref) and the SQL command (byval) and use the resultant recordset for adding, modifying or deleting records.

How can I do the same with VS2005? This is a major problem as there are dataadapters, grids, datareaders etc.

Does someone have a simple method to get the recordset so that I can modify the record using the code (eg: .Addnew/.Delete/.Update) and Close the connection?

Please note that I do not need to display anything and have to run 40/50 AddModDel for every click of the program.

Any help is greately appreciated. Thanks in Advance.:: Bump ::
Is there a solution?

Help needed urgently in CR...

Hi All, I'm a beginner in CR10. I am supposed to produced a report based on the time spent by each staff (weekly basis) on a specific projects. The report should be able to generate based on the staff name and date range (start date and end date). I'm using stored procedure, test it out in the SQL Query Analyzer and I got the output. But when I tried to pass the user id (from the stored procedure) in the CR, it doesnt work. Nothing came out...So here I am. Stuck and not moving. So would really appreciate if anybody, somebody could help me out...:( Thanks...

I'm using SQL Server and JSP.Pass proper parameter value from JSP
It will work

Help needed on Sql query with Time functions

Hello everyone,
can somone help me out with my problem? I have a table (named TblSensorRTData) with fields (sensorId -> char (3), dataTimeStamp -> datatime, readValue -> float) in MSDE 2000 (desktop version of MS SQL Server 2000).

There are 60 sensors at present and all of them are scanned exactly once every second. So, for every 1 second, a new set of 60 rows (corresponding to the 60 sensors) are populated into the table by the Acquisition program.

Phase II is that I have to display the data to the user at an interval he chooses (in another program).

For eg: if the user chooses the refresh rate as 10 seconds, I have to display information like this:
<pre>

Time Value1 Value2 Value3 Value4 Value5

10:30:00 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:10 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:20 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:30 xx.xx xx.xx xx.xx xx.xx xx.xx
.... .... ... ... ... ...
.... .... ... ... ... ...
.... .... ... ... ... ...

</pre>

The values correspond to the values in the table at the time specified by the column Time. Also, the user selects which, out of the 60 sensors, are to be displayed.

Hence I need to select the values of only those sensors (which are selected) and display their values at exactly those time (as specified in the interval). I have no clue how to do this. Hence I request you to help me out.

Regards,
Sriharsha.

PS: Please do not mistake my intensions. I am basically an Embedded Systems developer and I left Database programming 6-7 years ago. So, I can do it provided I have some time, but my schedule demands the results fast enough and hence I posted this query.Select columns
from table
where DATEPART(ss, date_column)%10 = 0

Should you wish to test the output,

create table test
(
date_field DATETIME,
pos INTEGER
)

Declare @.pos As Integer
Set @.pos = 0
While @.pos < 250000
Begin
insert into test values (GETDATE(), @.pos)
Set @.pos = @.pos + 1
End|||Dear Sir,

perhaps my question lacked the clarity it should have... Well, I have to display the updated fields every 'n' seconds (where n is the number chosen by the user). After the first n seconds, the values with time (seconds) n are to be selected and displayed. Also, a maximum of only 10 rows are to be displayed at any time. So after the 11th 'n' seconds, the screen has to scroll (im doing it in VB using the MS Flex Grid control) by one. i.e. all the rows should be shifted up by 1 and the last row should contain the latest extract.

So, at any given time when i am executing the query, I have to take the following into consideration:

1. Get only the values (of the required sensor ids) for time that is exactly 'n' seconds after the previous extract.
NOT ALL Values beginning some time and 'n' seconds downwards.

Warm Regards,
Sriharsha.

Help needed on reprting services

Hi, Can anyone help me to create a report with all drill down features
to be expanded at the time of display itself( without clicking the
expand button)
Thank youMake all the texbox visible ofcourse with toggle items and check in the
texbox->properties->visibility last option
ie "initial Appearence" select "expanded (-)"
Amarnath
"georgerijo@.gmail.com" wrote:
> Hi, Can anyone help me to create a report with all drill down features
> to be expanded at the time of display itself( without clicking the
> expand button)
> Thank you
>

Help needed linking into Time Dimension

Hi All,

Need some help and I'm not sure how to proceed on this one.

We have a table (tblState) which has the following layout:

PersonID (PK), LifeID, State, StateDate, EndDate

1, 1, 'Gatewy', 2000-01-01 00:00, 2001-01-01 00:00

The PersonID will appear more than once as that person will have more than one state and or life during their time with our company e.g:

PersonID (PK), LifeID, State, StateDate, EndDate

1, 96, 'GATEWY', 2000-01-01 00:00, 2000-04-25 09:37

1, 1, 'Completed', 2000-04-25 09:37, ''

Where the EndDate is null, this is because the person is still in that current state.

I need to link both the StartDate and EndDate into our time table, which has been setup by Analysis Services with a 10yr range.

When I try and map the StartDate onto the PK_Date, all is OK and seems to be OK, but when I build the cube and select a date from my time heiarchary, the number of records doesent shrink like I would expect with it only looking for people who have had a StartDate >= the date selected.

If I run a query through QA:

select * from dbo.tblstate S

INNER JOIN WDUKCube.Time T on S.StartDate = T.PK_Date

Where PK_Date BETWEEN '2000-01-01' AND '2000-12-01'

then this returns a few records, but the cube doesent, even though it's the same data source.

Does anyone have any suggestions they could give me ?

Assuming that you're using AS 2005: under the Dimension Usage tab for the cube, ensure that the correct relationship has been established between your Time dimension and Measure Group for tblstate (including the Granularity and Dimension and Measure Group columns).|||

Thanks Deepak

Should have said I was using AS2005...Whoops.

In my measure group I only have a count of PersonID for tblState, as everything else is sliced and diced based on the PersonID.

In the Dimension Usage I've created a referenced link on PK_Date and StartDate, as I can only link to one column.

Confused...

Help Needed For reporting service 2005(Regarding default date value for report parameter)

Hi experts
I am working on sql server reporting services 2005.
I am using Date time control for my report parameter. And default
value i given =Today(). This is working fine.
But i want some days previous date for default value and when I am
writting Today()-1, Report is giving error.
So can any body tell me how i can do this for defalut value. I want to
give one month previous date as default value.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can
install these reports. If yes then how can i make instalable file of
my this solution.
Any help will be gratefull.
Regards
DineshOn Oct 23, 9:38 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> I am using Date time control for my report parameter. And default
> value i given =Today(). This is working fine.
> But i want some days previous date for default value and when I am
> writting Today()-1, Report is giving error.
> So can any body tell me how i can do this for defalut value. I want to
> give one month previous date as default value.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can
> install these reports. If yes then how can i make instalable file of
> my this solution.
> Any help will be gratefull.
> Regards
> Dinesh
Hi!
You can try it;
(Date = DATEADD(Day, - 1, GetDate())
Hope this helps
Regards
Shima

Help needed creating select statement

Hi,

I have a need to create a table detailing the ID of all contacts and the
last time they were contacted. This information is stored in 2 tables,
'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
in the 'activity' table).

I guess I need some sort if iteration to go through each contact and find
find the last activity that took place against each of them (there many be
more than 1 activity against each contact) and then place the output values
into the new table.

Can anyone show me how to go about this?

Thanks!This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.

SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a need to create a table detailing the ID of all contacts and the
>last time they were contacted. This information is stored in 2 tables,
>'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
>in the 'activity' table).
>
>I guess I need some sort if iteration to go through each contact and find
>find the last activity that took place against each of them (there many be
>more than 1 activity against each contact) and then place the output values
>into the new table.
>
>Can anyone show me how to go about this?
>
>Thanks!
>

|||Hi Roy,

Many thanks. I've managed to use your example to get exactly what I need.
Cheers!

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:7240031r1ken2gmb5a3qe8gfost4nvma25@.4ax.com...

Quote:

Originally Posted by

This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.
>
SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:
>

Quote:

Originally Posted by

>>Hi,
>>
>>I have a need to create a table detailing the ID of all contacts and the
>>last time they were contacted. This information is stored in 2 tables,
>>'contact' and 'activity' (ID in the 'contact' table links to
>>'main_contact'
>>in the 'activity' table).
>>
>>I guess I need some sort if iteration to go through each contact and find
>>find the last activity that took place against each of them (there many be
>>more than 1 activity against each contact) and then place the output
>>values
>>into the new table.
>>
>>Can anyone show me how to go about this?
>>
>>Thanks!
>>

Help Needed - ISNULL when the field is already null..

Hello,
I just noticed today, after a long time, that if you do a
SELECT * FROM Table WHERE Field = Field
And that Field is null, it will return NOTHING at all...
Problem is, in my search stored procedures, I always use:
SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
So if the value is null, it simply avoid the check.
But now I'm screwed, for the first time I have to do this on fields that CAN
be null... and well, it returns NOTHING.
of course, WHERE Field IS NULL works like a charm, but I need this to be
working so if it's null, it skips the filter, if not, it does it.
Any help would be appreciated.Forgot to add...
the algorithm should be...
If null then don't check at all, return null and not null
but if not null, then must be it and only it...
I'm saying this, because I tried ISNULL(Field, 'foo') = ISNULL(@.Field,
'foo') and that, well, will only return rows with null values :-(
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> a crit dans le
message de news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Try,
...
where colA = @.par1 or @.par1 is null
...
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Nicolas LeBlanc" wrote:

> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>
>|||Read this article, which contains lots of info about doing this and similar
operations:
www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||How about :
CREATE TABLE [test] (
[col1] [int] IDENTITY (1, 1) NOT NULL ,
[col2] [datetime] NULL
) ON [PRIMARY]
GO
col1 col2
-- ---
1 2005-02-17 22:15:22.263
2 2005-02-15 22:15:24.920
3 2005-02-14 22:16:23.857
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
declare @.d datetime
set @.d = null -- try this one or the next
set @.d = '2005-02-17 22:15:22.263'
SELECT *
FROM test
WHERE
(col2 = @.d)
UNION ALL
SELECT *
FROM test
WHERE
(@.d IS NULL and col2 IS NULL)
Check execution plans to see which ones are optimized the best:
David Gugick
Imceda Software
www.imceda.com|||Based on your needs, you must use one of the two following statements:
Where (Field = @.Field)
Where @.Field is Null OR (Field = @.Field)
Where (@.Field is Null and Field is Null) OR (Field = @.Field)
Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
There is also an option in SQL-Server that will make the equality (Null =
Null) return True instead of False but I don't suggest using it; otherwise
you will have a continuous moving target.
S. L.
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
> CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Personally -- if I can get away with it security-wise -- I would rather use
dynamic SQL to solve these kinds of issues...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uYbp28dFFHA.2052@.TK2MSFTNGP09.phx.gbl...
> How about :
> CREATE TABLE [test] (
> [col1] [int] IDENTITY (1, 1) NOT NULL ,
> [col2] [datetime] NULL
> ) ON [PRIMARY]
> GO
> col1 col2
> -- ---
> 1 2005-02-17 22:15:22.263
> 2 2005-02-15 22:15:24.920
> 3 2005-02-14 22:16:23.857
> 4 NULL
> 5 NULL
> 6 NULL
> 7 NULL
> 8 NULL
> declare @.d datetime
> set @.d = null -- try this one or the next
> set @.d = '2005-02-17 22:15:22.263'
> SELECT *
> FROM test
> WHERE
> (col2 = @.d)
> UNION ALL
> SELECT *
> FROM test
> WHERE
> (@.d IS NULL and col2 IS NULL)
>
>
> Check execution plans to see which ones are optimized the best:
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Seems about right. I wonder why I didn't think of that one before...
I sure won't go into dynamic sql, no way!
Merci !
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> a
crit dans le message de news:Ozl8y$dFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Based on your needs, you must use one of the two following statements:
> Where (Field = @.Field)
> Where @.Field is Null OR (Field = @.Field)
> Where (@.Field is Null and Field is Null) OR (Field = @.Field)
> Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
> There is also an option in SQL-Server that will make the equality (Null =
> Null) return True instead of False but I don't suggest using it; otherwise
> you will have a continuous moving target.
> S. L.
> "Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
> news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>|||If there is only one predicate, then the simplest solution would be:
If @.Field IS NULL
SELECT ... FROM Table
Else
SELECT ... FROM Table WHERE Field = @.Field
Of course with more predicates this approach becomes unwieldly.
Gert-Jan
Nicolas LeBlanc wrote:
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.

HELP MS Access moving to SQL and dont know how

I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the error

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()

Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day.

So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me.

My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.

1. A good book to follow and easy to follow for SQL (connections / how tos) or website

2. Good examples or book on "ASP.Net Enterprise Manager"

3. How to convert access databases into SQL easy (some have over 50,000 listings)

4. how to convert Tab files into SQL easy.

Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.

Thanks again,

Rusty

Rusty,

To easily convert your Access tables to SQL Express, set up an ODBC System DSN for the SQL Server. Then when in Access, right click on the tables, one at a time, and choose export. From there choose the ODBC option and select the system DSN you just set up. Your tables should transfer automatically.

Of course I am assuming you have SQL Express setup already.

|||

I have it setup but I can't get it connected by dreamweaver. I get the error

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

I haven't found anything on exactly how to get it connected yet and still doing the trial and error thing.. LOL Now my access 2003 works perfect my server on the server online but no luck with SQL yet