Help with SQL Query

Hello Everyone,

I need help writing this query. I have a table named "PMP" that stores up to 10 objective ID's:

Code Snippet

SELECT ObjectiveID1,ObjectiveID2,ObjectiveID3,ObjectiveID4,... ObjectiveID10

FROM PMP

WHERE PMPID = @.PMPID

I have a child table named "METRICS" that stores the details for each objective:

Code Snippet

SELECT ObjectiveID, TitleText, Description, KPIvalue

FROM METRICS

WHERE ObjectiveID IN (query the PMP table)

I need to write a query that will return all the Metrics that are used for a given PMPID. Any ideas how this can be done?

Thank You

My first suggestion is to correct a bad design mistake. You have a severly de-normalized table.

You most likely 'should' NOT have columns Objective1-Objective10.

You most likely would benefit from having a separate table for Objectives.

THEN issues such as this one would become MUCH easier to solve.

If you are using SQL 2005, you may find the UNPIVOT statement to be useful.

|||

Which version of SQL Server are you using?

AMB

|||

I am using SQL Server 2005 Express Edition

I understand why you would think this table is de-normalized, but it's the right structure for the solution. My organization allows a maximum of 10 objectives to be identified per request. I need to send my crystal report 1 record.

Can someone help me with the UNPIVOT query?

Thank You

|||

I figured it out:

SELECT * FROM COACHING.METRICS

WHERE OBJECTIVEID IN (

SELECT OBJECTIVEID

FROM

(SELECT RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID

FROM COACHING.PMPFORMS

WHERE PMPID = 271 ) OBJ

UNPIVOT

(OBJECTIVEID FOR PMPID IN (RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID)) VALS)

|||

Thanks Arnie Rowland and hunchback for your reponses

|||I'm glad we could point you in a direction that worked.sql

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
Chuck
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>
|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
Chuck
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>
|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the p
rimary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I sol
ved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered inde
x scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple
I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know abo
ut
| your data or needs, but in my general experience I've found that indexes o
n
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used f
or
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical que
ries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Wort
h
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip

>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
50,000 records.
clustered, non-unique index.
index scan against the
>primary key of the table, with a WHERE clause for the
far more complex. I
>solved it by altering the joins and rearranging predicates
use the clustered
>index scan at 100% cost and the execution time on that
query
preceding query is so
>simple I don't know what to do.
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
>
> I don't know about
> found that indexes on
> me this one row
> hand, are used for
> identified by this FK).
> FKBatchID would allow
> increasing your
> that it includes
> query shouldn't
> it needs... Worth
> 50,000 records.
> clustered, non-unique index.
> index scan against the
> far more complex. I
> use the clustered
> query
> preceding query is so|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||Thanks for the clarification, Ray; most of my experience is with large
datawarehouse type applications so light on the insert (during production
hours) and very heavy querying is the direction I'm coming from.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:es0OpkwAEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Try putting a nonclustered index on processcount and fkbatchID if this is
> something you do often, if fkbatchid is your clustered index you only have
> to put a nonclustered index on processcount as the clustered key is
included
> in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
> propertied column) if you are heavy inserts this is a good idea as it
> creates hot spots on the disk as your inserts will fall to bottom of the
> leaf level (reducing page splits and affording you not to have to mess
with
> fill factor) If you are query intensive and light inserts, test out Adam's
> solution.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Gallucci" <chris@.gallucci.com> wrote in message
> news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
index.
the
> primary key of the table, with a WHERE clause for the
> solved it by altering the joins and rearranging predicates
> index scan at 100% cost and the execution time on that query
> simple I don't know what to do.
>

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the primary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I solved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered index scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know about
| your data or needs, but in my general experience I've found that indexes on
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used for
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical queries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Worth
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
>> This query takes 1 minute to execute...
>> SELECT SUM([ProcessCount])
>> FROM [ProcessTable]
>> WHERE [FKBatchID] = 1
>> The table contains about 5,000,000 records.
>> The total record count that matches the WHERE clause is
50,000 records.
>> The table has a clustered index on the primary key.
>> The foreign key in the WHERE clause has a non-
clustered, non-unique index.
>> The execution plan shows a 100% cost on a clustered
index scan against the
>primary key of the table, with a WHERE clause for the
>> [FKBatchID] column.
>> I had a similar problem with another query which was
far more complex. I
>solved it by altering the joins and rearranging predicates
>> in the WHERE clause. This cause the plan to no longer
use the clustered
>index scan at 100% cost and the execution time on that
query
>> went from 2 minutes to 5 seconds. However, the
preceding query is so
>simple I don't know what to do.
>> Please help.
>> ChrisG
>>
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
> >--Original Message--
> >Two questions/suggestions:
> >
> >First of all, why are you clustering on the primary key?
> I don't know about
> >your data or needs, but in my general experience I've
> found that indexes on
> >PKs are used more often for random data retrieval (give
> me this one row
> >identified by this one PK). Indexes on FKs, on the other
> hand, are used for
> >more range-related activity (give me these rows
> identified by this FK).
> >Indeed, for this query, having a clustered index on
> FKBatchID would allow
> >the data to be read contiguously from the disc, greatly
> increasing your
> >performance. So you might consider switching that.
> >
> >Second, you could try covering the non-clustered index so
> that it includes
> >ProcessCount (something like, CREATE INDEX MyIndex ON
> >ProcessTable(FKBatchID, ProcessCount)) ... This way, the
> query shouldn't
> >have to go into the leaves of the cluster to get the data
> it needs... Worth
> >a try, at any rate...
> >
> >
> >"Chris Gallucci" <chris@.gallucci.com> wrote in message
> >news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> >> This query takes 1 minute to execute...
> >> SELECT SUM([ProcessCount])
> >> FROM [ProcessTable]
> >> WHERE [FKBatchID] = 1
> >>
> >> The table contains about 5,000,000 records.
> >> The total record count that matches the WHERE clause is
> 50,000 records.
> >> The table has a clustered index on the primary key.
> >> The foreign key in the WHERE clause has a non-
> clustered, non-unique index.
> >> The execution plan shows a 100% cost on a clustered
> index scan against the
> >primary key of the table, with a WHERE clause for the
> >> [FKBatchID] column.
> >>
> >> I had a similar problem with another query which was
> far more complex. I
> >solved it by altering the joins and rearranging predicates
> >> in the WHERE clause. This cause the plan to no longer
> use the clustered
> >index scan at 100% cost and the execution time on that
> query
> >> went from 2 minutes to 5 seconds. However, the
> preceding query is so
> >simple I don't know what to do.
> >>
> >> Please help.
> >>
> >> ChrisG
> >>
> >>
> >
> >
> >.
> >|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>

Help with simple(ish) Select query?

Hi

I Have the following table

SequenceNumber___TypeID
8_________________IMG
7_________________IMG
6_________________IMG
5_________________IMG
4_________________IMG
3_________________IMG
2_________________FLP
2_________________IMG

I want to pull the data out in the following format,

SequenceNumber___TypeID
8_________________IMG
2_________________FLP

This basically shows the highest SequenceNumber of each TypeID,

I've tried many different SQL queries but I can't seem to get it! Any ideas?

Thanks

SELECT Max( SequenceNumber ), TypeID
FROM YourTable
GROUP BY TypeID|||Excellent!. I knew it was simple

Help with Select to return Duplicate rows

Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.

SELECT TOP (100)PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.AddressFROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVING (COUNT(dbo.tblClientAddresses.Address) > 1)ORDER BY dbo.tblClientAddresses.Address

TOP 100 PERCENT is redundant.

SELECT dbo.tblClient.LastName, dbo.tblClientAddresses.Address,count(*)FROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVINGCOUNT(*) > 1ORDER BY dbo.tblClientAddresses.Address

|||

ndinakar,

Is there a way to also return the Client_ID of each row?

|||

Hi Jack,

If the Client_ID is a primary key with unique values, so the duplicate rows have different Client_ID, we cannot return it.

If duplicate rows have the same Client_ID, yes, we can return it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

try dinakar's query just add client id in select list. but dont group by client id. i think that should work.

thanks,

satish.

Help With Select Statement

My Current Query:

select rpg.rpg_sortorder,rpg.rpg_groupname,
act.act_cardprocid, sum(act.act_trxamtn) as Amount from
actlog act
right outer join
report_groups rpg on rpg.rpg_groupcode = act.act_CardProcID
WHERE
(rpg.rpg_report = 'SS') AND
(rpg.rpg_groupname <> 'not on report')
group by rpg.rpg_groupname, rpg.rpg_sortorder, act.act_cardprocid
order by rpg_sortorder

Report_groups looks like this

rpg_sortorder rpg_groupname rpg_groupcode
2 debit cards db
3 discover ds
4 visa vs
8 food stamps ef
10 gift cards gc
14 fleet cards wx
15 fleet cards mf
16 fleet cards vy
17 ach ac

Actlog looks like this:

act_cardprocid Amount
db 25.00
db 25.00
vs 100.00
vs 200.00

resultset I wish to achieve

rpg_sortorder rpg_groupname act_cardprocid Amount
2 debit cards db 50.00
3 Discover null null
4 Visa vs 300.00
8 food stamps null null
10 gift cards null null
14 Fleet Cards null null
17 ach null null

Note that in the join, I only need one record to represent group name and sortorder.
If there happens to be three records in report_groups for the same groupname, I only want
the top record. Hence, I do NOT want the following to showup in my results:

15 Fleet cards null null
16 Fleet cards null null

How can I filter out these unwanted records?

This seems to produce your desired output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Report_Groups table
( Rpg_SortOrder int,
Rpg_GroupName varchar(20),
Rpg_GroupCode char(2)
)

INSERT INTO @.Report_Groups VALUES ( 2, 'debit cards', 'db' )
INSERT INTO @.Report_Groups VALUES ( 3, 'discover', 'ds' )
INSERT INTO @.Report_Groups VALUES ( 4, 'visa', 'vs' )
INSERT INTO @.Report_Groups VALUES ( 8, 'food stamps', 'ef' )
INSERT INTO @.Report_Groups VALUES ( 10, 'gift cards', 'gc' )
INSERT INTO @.Report_Groups VALUES ( 14, 'fleet cards', 'wx' )
INSERT INTO @.Report_Groups VALUES ( 15, 'fleet cards', 'mf' )
INSERT INTO @.Report_Groups VALUES ( 16, 'fleet cards', 'vy' )
INSERT INTO @.Report_Groups VALUES ( 18, 'ach', 'ac' )

DECLARE @.Actlog table
( Act_CardProcID char(2),
Act_TrxAmtn decimal(10,2)
)

INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 100.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 200.00 )

SELECT
SortOrder = min( r.Rpg_SortOrder ),
GroupName = r.Rpg_GroupName,
CardProdID = min( a.Act_CardProcID ),
Amount = sum( a.Act_TrxAmtn )
FROM @.Report_Groups r
LEFT JOIN @.ActLog a
ON r.Rpg_GroupCode = a.Act_CardProcID
GROUP BY r.Rpg_GroupName
ORDER BY SortOrder

SortOrder GroupName CardProdID Amount
-- -- -
2 debit cards db 50.00
3 discover NULL NULL
4 visa vs 300.00
8 food stamps NULL NULL
10 gift cards NULL NULL
14 fleet cards NULL NULL
18 ach NULL NULL

Help with select statement

Hi guys,

I have the following data

CaseNumber ConnectionToCase PersonID

-

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00002 L 355

00003 F 577

00003 J 235

00003 C 744

00003 K 563

00005 A 501

00005 K 455

00009 R 500

00009 6 017

00009 K 011

I would like to select all columns based on casenumber that contains only ConnectionToCase = '6'.

Therefore i need a query that will return this result:

CaseNumber ConnectionToCase PersonID

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00009 R 500

00009 6 017

00009 K 011

How can i do that? thanks guys

Jul.

Something like this should work:

Code Snippet

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber IN ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||

See if this works for you:

Code Snippet

select a.CaseNumber, a.ConnectionToCase, a.PersonID

from casedata a

inner join casedata b

on a.CaseNumber = b.CaseNumber

and b.ConnectionToCase = '6'

|||

Here you go....

Code Snippet

Create Table #casedata (

[CaseNumber] Varchar(100) ,

[ConnectionToCase] Varchar(100) ,

[PersonID] Varchar(100)

);

Insert Into #casedata Values('00001','A','500');

Insert Into #casedata Values('00001','J','235');

Insert Into #casedata Values('00001','6','014');

Insert Into #casedata Values('00001','K','016');

Insert Into #casedata Values('00002','A','500');

Insert Into #casedata Values('00002','B','477');

Insert Into #casedata Values('00002','6','251');

Insert Into #casedata Values('00002','L','355');

Insert Into #casedata Values('00003','F','577');

Insert Into #casedata Values('00003','J','235');

Insert Into #casedata Values('00003','C','744');

Insert Into #casedata Values('00003','K','563');

Insert Into #casedata Values('00005','A','501');

Insert Into #casedata Values('00005','K','455');

Insert Into #casedata Values('00009','R','500');

Insert Into #casedata Values('00009','6','017');

Insert Into #casedata Values('00009','K','011');

Select

*

From

#casedata

where

[CaseNumber] in (

Select

[CaseNumber]

From

#casedata

Where [ConnectionToCase]='6'

)

|||

SELECT * FROM myTable

WHERE CaseNumber IN (SELECT CaseNumber FROM myTable WHERE ConnectionToCase = 6)

Adamus

|||

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've got this error when ran this query:

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber = ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||Thanks guys!!

Help with Select Statement

Edited by moderator XIII, please don't use [ code ] tags around your code. Instead change your Profile | Site settings to use the Rich editor (with code support) to insert colorized code and line numbers:

I have a select statement that follows:

SELECT Events.legendID AS Events_legendID,
Units.unitID AS Units_unitID,
Units.msbbID, Units.belongsTo,
Units.name AS unitName,
Legend.legendID AS Legend_legendID,
Legend.Color,
Legend.Name legendName,
Legend.Active,
Events.unitID AS Events_UnitID,
Events.userID AS eventUserID,
Events.startDate,
Events.endDate,
Events.eventID,
Events.Title
FROM Events INNER JOIN Units ON Events.unitID = Units.unitID
AND Events.unitID = Units.unitID INNER JOIN Legend ON Events.legendID = Legend.legendID
WHERE startDate BETWEEN convert(datetime, '4/01/2006') AND convert(datetime, '6/30/2006')
ORDER BY unitName ASC;

This code works great with the exception of the events that start and end outside of the quarter dates I am entering. For example if there is an event that start in January and ends in August then I need to show that event in the 1st quarter, 2nd quarter and 3rd quarter.

any help on how to get those events that start and end outside the 'active' quarter BUT pertain to the active quarter?

thanks in advance!

Hello, if i get you right, maybe this would work:

WHERE
startDate <= convert(datetime, '6/30/2006') AND
endDate >= convert(datetime, '4/01/2006')

Anyway, i'd suggest you craft your data structure a bit more, to work on "quarters" rather than generic (unconstrained) dates...

HTH -LV

|||what do you mean by "... work on quarters rather then generic..."?|||

Mmm, what's unclear? So to say, i'd rather not write a treaty...

Also, i'm not such an expert with SqlServer either, so i guess we'll need someone else for a "ground-breaking" implementation sample...

-LV

Help with select statement

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
PK[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployee]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO
CREATE TABLE [dbo].[tblEmployee] (
PK[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EmployeeID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployeeAsset]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployeeAsset]
GO
CREATE TABLE [dbo].[tblEmployeeAsset] (
PK[EmpID] [int] NOT NULL ,
PK[AssetID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
tblAsset
AssetID AssetName
1 RyanPC
--
tblEMployee
EmpID EmployeeID
1 Ryan
--
tblemployeeasset
EmpID AssetID
1 1
I want a select statement that gives me this result...
Employee Asset
Ryan RyanPC"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
> I want a select statement that gives me this result...
> Employee Asset
> Ryan RyanPC
>
SELECT E.EmployeeID, A.AssetName
FROM tblAsset AS A
JOIN tblEmployeeAsset AS T
ON A.AssetID = T.AssetID
JOIN tblEmployee AS E
ON T.EmpID = E.EmpID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This works exactly the way I want in the querly analyzer thank you.
However, I am calling this select statement from within some C# code
via a dataadapter, when I run the query I get an error, any idea why?
SqlDataAdapter da = new SqlDataAdapter("select e.EmployeeID,e.EmpID,
a.AssetName, a.AssetId from tblAsset a join tblEmployeeAsset t on
a.assetID = t.AssetID join tblEmployee e on t.EmpID = E.EmpID", str);
DataSet ds = new DataSet();
da.Fill(ds);
dataGrid1.DataSource = ds;
The connection exists, and every other thing works, but why not this?
If this is beyond your scope i will promptly post in a C# forum...
David Portas wrote:
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
>
> SELECT E.EmployeeID, A.AssetName
> FROM tblAsset AS A
> JOIN tblEmployeeAsset AS T
> ON A.AssetID = T.AssetID
> JOIN tblEmployee AS E
> ON T.EmpID = E.EmpID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

Help with SELECT statement

Hi,
I have two tables with a UserID column and need to construct a query that
lists all UserIDs from Table A that is not present in Table B.
Any help with this select statement would be appreciated
NiclasSelect A.* from TableA as A where Not Exists (select * from TableB as B
where B.UserId = A.UserId)
You can also go with a Left Outer Join but it's a little more complicated to
understand:
Select A.* from TableA as A Left Outer Join TableB as B on A.UserId =
B.UserId
Where B.UserId is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||A third possibility would be to use the IN clause:
Select A.* from TableA as A where A.UserId Not IN (select UserId from TableB
Where UserId is not Null)
The condition Where B.UserId is Not Null is a necessity if there is a
possibility that B.UserId can be Null; otherwise the result won't be good if
the IN clause encounter a Null value.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||Many thanks !
Niclas
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:emU3YG65FHA.2888@.tk2msftngp13.phx.gbl...
>A third possibility would be to use the IN clause:
> Select A.* from TableA as A where A.UserId Not IN (select UserId from
> TableB Where UserId is not Null)
> The condition Where B.UserId is Not Null is a necessity if there is a
> possibility that B.UserId can be Null; otherwise the result won't be good
> if the IN clause encounter a Null value.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Niclas" <lindblom_niclas@.hotmail.com> wrote in message
> news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
>

help with Select statement

Hi!

This is my table:

CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)

This is some values:

INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)

As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.

Please help.

Best regards,
Zvonkoselect sifrob,katbroj,sum(kol) as kol
from Query_Result
group by sifrob,katbroj

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Zvonko wrote:

Quote:

Originally Posted by

Hi!
>
This is my table:
>
CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)
>
>
This is some values:
>
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)
>
As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.
>
Please help.
>
Best regards,
Zvonko

Help with Select statement

I need help creating a select statement that will show users who have
attempted to fill out a form more than once and have not completed it
at least once.
Here is the basic table layout.
ID int PK
PersonID int
Completed bit
Date DateTime
The data looks like this
ID Person ID Completed Date
1 101 True 5/1/06
2 102 True 5/2/06
3 103 False 5/3/06
4 104 True 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
9 106 True 5/4/06
10 104 True 5/4/06
I need to select all users who have attempted to fill out the form
multiple times and have failed to complete the form(a False in the
Completed column) at least one of those times. So for the above data
I would want the results of the select to be
ID Person ID Completed Date
3 103 False 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
Can anyone help me with this.
Thanksselect ID,
PersonID,
Completed,
Date
from mytable
where PersonID in (
select PersonID
from mytable
group by PersonID
having count(*)>1
and min(cast(Completed as int))=0)|||It worked great. Thank You.

Help with Select statement

I have 2 columns with data in different sequences in one table referencing a single column in different table.

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!

I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.

CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)

Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form

CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDI am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:

SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID

In your query, this bit looks to me like a syntax error:

(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers

... unless SQL Server has a very different SQL syntax that the one I know.|||Sorry about the confusion but thats it. Thank yousql

Help with Select statement

Help me with the following query.
I have employee table as follows
EmpID (Number)
FullName (Text)
ReportTo(Number)
ReportTo field contain number from EmpID
Sample Data from the table
EmpIDFullNameReports To
1Nancy Davolio2
2Andrew Fuller
3Janet Leverling2
4Margaret Peacock2
5Steven Buchanan2
6Michael Suyama5
7Robert King5
8Laura Callahan2
9Anne Dodsworth5
10Andrew Leverling3
11Michael Miller5
12Robert Davolio3
13Nancy Suyama6
14Margaret King7
I would like to create a single query (T-SQL), when I pick an
employee ID, I would like to have the entire list of employees come
under that employee including that employee and all below him/her.
Example. If I pick ID = 5, should get the following list.
EmpIDFullNameReports To
5Steven Buchanan2
6Michael Suyama5
7Robert King5
9Anne Dodsworth5
11Michael Miller5
13Nancy Suyama6
14Margaret King7
The list above shows all staff who report to ID 5 directly, But I want very
who are reporting to staff reporting to staff report to ID 5 and below in the
hieratical organization.
How do I do that query?
Thanks
Harry
Harry J Nathan
see
http://www.mindsdoor.net/SQLTsql/Ret...Hierarchy.html
"Harry J Nathan" wrote:

> Help me with the following query.
> I have employee table as follows
> EmpID (Number)
> FullName (Text)
> ReportTo(Number)
> ReportTo field contain number from EmpID
> Sample Data from the table
> EmpIDFullNameReports To
> 1Nancy Davolio2
> 2Andrew Fuller
> 3Janet Leverling2
> 4Margaret Peacock2
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 8Laura Callahan2
> 9Anne Dodsworth5
> 10Andrew Leverling3
> 11Michael Miller5
> 12Robert Davolio3
> 13Nancy Suyama6
> 14Margaret King7
>
> I would like to create a single query (T-SQL), when I pick an
> employee ID, I would like to have the entire list of employees come
> under that employee including that employee and all below him/her.
> Example. If I pick ID = 5, should get the following list.
> EmpIDFullNameReports To
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 9Anne Dodsworth5
> 11Michael Miller5
> 13Nancy Suyama6
> 14Margaret King7
> The list above shows all staff who report to ID 5 directly, But I want very
> who are reporting to staff reporting to staff report to ID 5 and below in the
> hieratical organization.
> How do I do that query?
> Thanks
> Harry
> --
> Harry J Nathan

Help with SELECT query

Hi
Is there a way to get a limited number of rows results from a select query
?
like select top 50 * from mytable returns only the first 50 records, but
what if a want to get records 50 to 100.
(I woulk like to spread the results over multiple pages.)
I would like to avoid to have all records pulled from the server and do the
job at the client side.
Johan
If you are using SQL Server 2005 you can use a windowing function like
Rownumber() for getting a partial resultsset. See the BOL for more
information. This requires having SQL Server 2005 as this
functionality is not present in SQL Server 2000.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

Help with select query

Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA0111.000022.0000
DVD0036.000053.5600
ZZZ0234.000044.5000
BMM0023.000029.8500
BMM0013.000032.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01blah blah 57750.0000115500.0000
BMM001blah blah blah 8739.000095692.0500
DVD003blah blah blabber DVD4230.000037759.8000
BMM002Yadda Yadda2772.000027581.4000
DVD001Dooddly doo DVD1605.000013658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.
Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.