query help

i need to include in my query a filter for dates. i need to just return
records that have happened in the last six months. I do i do this. help
please.
i have a startdate and a enddate field.try date between startdate and dateadd(m,-6,startdate)
"Nat Johnson" wrote:
> i need to include in my query a filter for dates. i need to just return
> records that have happened in the last six months. I do i do this. help
> please.
> i have a startdate and a enddate field.

Query Help

SQL DB Query Help
I have several suppliers of products.
When a customer does a Search
I want to return only the Lowest cost items by Part Number.
The query could return many different Part Numbers.
I also need to return Description, Part Number, Qty on Hand, Supplier etc.
These are all in the table.
Note that Description, Qty and Supplier are usually different.
Example items in DB
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
123 A Widget 2.00 5 2
123 Widget A 3.00 20 3
567 B Widget 9.00 3 1
567 Widget B 8.00 17 2
567 Widget 12.00 8 3

I would like to return
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
567 Widget B 8.00 17 2

Thanks in advance

Perhaps something like this:

SELECT
P.* FROM Parts P
INNER JOIN
(
SELECT P3.part as PartNum, MIN(P3.Cost) as MinCost
FROM Parts P3
GROUP BY P3.Part) AS P2
ON P.Part = P2.partnum
AND P.cost = P2.mincost

Query Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

Query Help

Im trying to write a query that will return the next daily sub number and
Volume number for our submissions. the volume number is easy as it is
incremented each time, but the Daily sub number needs to be reset each day.
Each time a submission is made the numbers are incememted
ive come up with this so far but am struggling with the daily Sub numbers
select
Right('000000' + cast((VolumeNumber + 1) as varchar(6)), 6) As Volume,
Right('000' + cast((DailySerial + 1) as varchar(6)), 6) as DailySub
from dbo.BureauSubRecord
this result would be
Volume DailySub
-- --
000001 0001
Then the table would be updater to read
Volume DailySub
-- --
000002 0002 etc
However the first sub of the following day should read
Volume DailySub
-- --
000003 0001
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[BureauSubRecord]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[BureauSubRecord]
GO
CREATE TABLE [dbo].[BureauSubRecord] (
[BureauId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VolumeNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DailySerial] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastSub] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [BureauSubRecord]
([BureauId],[VolumeNumber],[DailySerial]
,[LastSub])VALUES('12345','000000','000'
,'Jan 1 2005 12:00:00:000AM')
SET NOCOUNT OFFHi
I am not sure about your DDL as it does not seem to match your description
The following is untested but you may want something like:
SELECT B.[BureauId], B.[VolumeNumber],
(SELECT COUNT(*) FROM dbo.BureauSubRecord S WHERE
S.[BureauId] = B.[BureauId]
AND S.[VolumeNumber] = B.[VolumeNumber]
AND S.[LastSub] < B.[LastSub]
AND CONVERT(CHAR(8), S.[LastSub], 112 ) = CONVERT(CHAR(8), B.[LastSub], 112
) ) + 1 AS [DailySerial],
B.[LastSub]
FROM dbo.BureauSubRecord B
John
"Peter Newman" wrote:

> Im trying to write a query that will return the next daily sub number and
> Volume number for our submissions. the volume number is easy as it is
> incremented each time, but the Daily sub number needs to be reset each da
y.
> Each time a submission is made the numbers are incememted
> ive come up with this so far but am struggling with the daily Sub numbers
> select
> Right('000000' + cast((VolumeNumber + 1) as varchar(6)), 6) As Volume,
> Right('000' + cast((DailySerial + 1) as varchar(6)), 6) as DailySub
> from dbo.BureauSubRecord
> this result would be
> Volume DailySub
> -- --
> 000001 0001
>
> Then the table would be updater to read
> Volume DailySub
> -- --
> 000002 0002 etc
> However the first sub of the following day should read
> Volume DailySub
> -- --
> 000003 0001
>
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[BureauSubRecord]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[BureauSubRecord]
> GO
> CREATE TABLE [dbo].[BureauSubRecord] (
> [BureauId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VolumeNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DailySerial] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastSub] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [BureauSubRecord]
> ([BureauId],[VolumeNumber],[DailySerial]
,[LastSub])VALUES('12345','000000','000'
,'Jan 1 2005 12:00:00:000AM')
> SET NOCOUNT OFF
>

Query for SUM

Hi, i have a simple table with itemcode and quantity

Code Qty

--

A 2

B 3

C 5

A 3

How do i query the table to return me the total quantity by itemcode (as below)?

Code Qty

--

A 5

B 3

C 5

use aggregate function sum () and grup by

Select code,sum(qty) as TotalQty from YourTableName group by Code

Madhu

|||

use the following code,

Code Snippet

Create Table #data (

[Code] Varchar(100) ,

[Qty] int

);

Insert Into #data Values('A','2');

Insert Into #data Values('B','3');

Insert Into #data Values('C','5');

Insert Into #data Values('A','3');

Select

Code

,Sum(Qty)

From

#Data

Group By

Code

Query for Segments

All
I have a table that contains data for when a stretch of road was last
resurfaced and I am trying to return the cost per year for each completed
road segment. In the example below the road was resurfaced in 1971 and 1973
.
In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
kilometres to 9 kilometres. In 1973 the kilometre in between was resurfaced
.
I only want to show from start to finish the complete parts of the road that
were resurfaced and when as illustrated below:
CREATE TABLE rd_resurface
(
RoadNo NVARCHAR(20),
KMStart INT,
KMEnd INT,
Cost NUMERIC(13, 2),
Deprec NUMERIC(13, 2),
[Year] SMALLINT
)
GO
INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
I am trying to return the results below:
RoadNo KMStart KMEnd Cost Deprec Year
H001 1 4 $300.00 $30.00 1971
H001 4 5 $100.00 $10.00 1973
H001 5 9 $400.00 $40.00 1971
Thanks for any assistance that can be provided.David,
I'm not sure I got it right as you didn't say if the same km of road can
appear more than once in the same year. Assuming it can't...
The following query calculates a grouping factor which is the last kmstart
value within the segment:
select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1
RoadNo KMStart KMEnd Cost Deprec Year grp
-- -- -- -- -- -- --
H001 1 2 100.00 10.00 1971 3
H001 2 3 100.00 10.00 1971 3
H001 3 4 100.00 10.00 1971 3
H001 4 5 100.00 10.00 1973 4
H001 5 6 100.00 10.00 1971 8
H001 6 7 100.00 10.00 1971 8
H001 7 8 100.00 10.00 1971 8
H001 8 9 100.00 10.00 1971 8
The rest is simply to group the data and return the desired aggregates:
select roadno, year, min(kmstart) as kmstart, max(kmend) as kmend,
sum(cost) as cost, sum(deprec) as deprec
from (select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1) as d
group by roadno, year, grp
roadno year kmstart kmend cost deprec
-- -- -- -- -- --
H001 1971 1 4 300.00 30.00
H001 1971 5 9 400.00 40.00
H001 1973 4 5 100.00 10.00
BG, SQL Server MVP
www.SolidQualityLearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A73BA53-4633-4E5C-B72C-EBB3D4B73472@.microsoft.com...
> All
> I have a table that contains data for when a stretch of road was last
> resurfaced and I am trying to return the cost per year for each completed
> road segment. In the example below the road was resurfaced in 1971 and
> 1973.
> In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
> kilometres to 9 kilometres. In 1973 the kilometre in between was
> resurfaced.
> I only want to show from start to finish the complete parts of the road
> that
> were resurfaced and when as illustrated below:
> CREATE TABLE rd_resurface
> (
> RoadNo NVARCHAR(20),
> KMStart INT,
> KMEnd INT,
> Cost NUMERIC(13, 2),
> Deprec NUMERIC(13, 2),
> [Year] SMALLINT
> )
> GO
> INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
> INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
> I am trying to return the results below:
> RoadNo KMStart KMEnd Cost Deprec Year
> H001 1 4 $300.00 $30.00 1971
> H001 4 5 $100.00 $10.00 1973
> H001 5 9 $400.00 $40.00 1971
>
> Thanks for any assistance that can be provided.sql

Query error

Hi every one,
When I performed SQL delete command, server return message "[Microsoft][ODBC
SQL Server Driver][SQL Server]Internal Query Processor Error: The query ran
out of stack space during query optimization.". So I cann't delete the
record.
Can any body explain me how to fix this ?
ThanksHi Paul,
No, that table does not have any trigger.
And delete command very simple
DELETE FROM UserInfo WHERE (tp_ID = 667)
Thanks
"Paul Cahill" <paul.cahill@.cableinet.co.uk> wrote in message
news:uz54kDYZDHA.2932@.tk2msftngp13.phx.gbl...
> Any trigger on the tables?
> Is is a complex delete?
> Can you paste the statement here?
> Paul
> "Ben" <minh_nb@.yahoo.com> wrote in message
> news:OczUGXXZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> > Hi every one,
> >
> > When I performed SQL delete command, server return message
> "[Microsoft][ODBC
> > SQL Server Driver][SQL Server]Internal Query Processor Error: The query
> ran
> > out of stack space during query optimization.". So I cann't delete the
> > record.
> >
> > Can any body explain me how to fix this ?
> >
> > Thanks
> >
> >
> >
>

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
Homer
Homer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer
|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14)
,
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=1
4),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

query cannot return any results

I have a very uncommon problem ... I have a application that runs like this:
1) it receives users inserts, including a Status = 'U' field;
2) based on a Status field (index) the application query and select the last
inserted registries all day long, each 30 seconds;
3) every time it read the registries it changes the Status field to 'R'.
The problem is that after about 24 hours, the query identifies no longer
registries with that index. It returns nothing. I use a execurereader
command, and it happens that myreader.hasrows = false, even if there are row
s
with Status field = 'U'
Can somebody help me to know what is happening?
--
Sergio R Piresquery optimiser should use best available index or column stats to decide
strategy, and this may be cached for long period.
Maybe stats get recomputed automatically if you make lotsa changes and have
updatestats dboption set, or explicitly by your DBA [recommendation used to
be to do explicitly due to excessive overhead but nowadays with autonomics
MSSQL does the right thing].
If you truncate/delete staging table [daily] just before query is compiled
into cache it may decide to use tablescan even if index available [since so
_few_ rows] and this may persist some time even if cardinality builds up a
lot.
Unfortunately the sysindexes.rowcnt cannot be relied on for accuracy [due to
transaction activity], so you may have to force count(*) to get real count
but this has locking issues [nolock would only give approx count like
sysindexes].
Dependencies can be omitted from sysdepends [to support forward compilation]
so optimiser may be similarly ignorant.
I suspect the optimiser is getting , so suggest that
1. check latest Service Pack applied
2. exec sp_dboption 'pubs','auto create statistics','on' -- substitute
dbname for pubs
3. exec sp_dboption 'pubs','auto update statistics','on' -- substitute
dbname for pubs
4. use QA to show query plan [Control-L]
5. try explicit sp_recompile
6. check dependencies
if all else fails you can mark your sproc "WITH RECOMPILE" to ignore cached
copy, thus keep abreast of actual cardinality
best wishes!
Dick
"Sergio R Pires" wrote:

> I have a very uncommon problem ... I have a application that runs like thi
s:
> 1) it receives users inserts, including a Status = 'U' field;
> 2) based on a Status field (index) the application query and select the la
st
> inserted registries all day long, each 30 seconds;
> 3) every time it read the registries it changes the Status field to 'R'.
> The problem is that after about 24 hours, the query identifies no longer
> registries with that index. It returns nothing. I use a execurereader
> command, and it happens that myreader.hasrows = false, even if there are r
ows
> with Status field = 'U'
> Can somebody help me to know what is happening?
> --
> Sergio R Pires

query by recent dates

How would I construct a SQL statement that would query the database and return the 5 most recent dates added into the database?

Would I use the TOP SQL keyword to select the 5 most recent entries?

How would I query for the most recent dates?

SELECT TOP 5 *
FROM dbo.tblWeblog
WHERE blogDate = ?

Thanks for any help!
-Dman100-Close! I'd use:SELECT TOP 5 *
FROM dbo.tblWeblog
ORDER BY blogDate DESC-PatP|||Thanks Pat!
-Dman100-

query assistance -return most recent date

I have a table that has two fields, pkg_num, which is a number, and
del_date_time, which is a date-time. The table can contain duplicate pkg_num
values, as long as the del_date_time values are different for any given
number. I need a query that will return the most recent del_date_time for
each pkg_num. Any ideas?
On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:

>I have a table that has two fields, pkg_num, which is a number, and
>del_date_time, which is a date-time. The table can contain duplicate pkg_num
>values, as long as the del_date_time values are different for any given
>number. I need a query that will return the most recent del_date_time for
>each pkg_num. Any ideas?
Hi Rich_A2B,
Probably
SELECT pkg_num, MAX(del_date_time)
FROM MyTable
GROUP BY pkg_num
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That works, thanks! Now to complicate things, I have a third field,
DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
fields in the query result, but only show records with the most recent
DEL_DATE_TIME?
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:
>
> Hi Rich_A2B,
> Probably
> SELECT pkg_num, MAX(del_date_time)
> FROM MyTable
> GROUP BY pkg_num
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||

Quote:

Originally posted by Hugo Kornelis
On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)