Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/

print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate

delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate

delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/

--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test

--need help

-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query execution time problem

We are executing following query from query analyzer,
SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
[Table Strcuture => id int, data varchar(500)]
This table contains 45000 records.
When this query is executed from Query Analyzer locally on the database
server, output is returned in 30 seconds.
However, when the query is executed from Query Analyzer installed on
application server it takes 110 seconds.
Execution plan is same both times. CPU, reads are identical for both
executions. However there is big difference between the execution times.
Also, both database server and application server in same VLAN and direct
network connectivity is available without any interim hops.
Any kind of help or suggestion giving insight into this aspect is welcomed.
Thanks in advance for all the inputs.pvv30 (pvv30@.discussions.microsoft.com) writes:
> We are executing following query from query analyzer,
> SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
> [Table Strcuture => id int, data varchar(500)]
> This table contains 45000 records.
> When this query is executed from Query Analyzer locally on the database
> server, output is returned in 30 seconds.
> However, when the query is executed from Query Analyzer installed on
> application server it takes 110 seconds.
> Execution plan is same both times. CPU, reads are identical for both
> executions. However there is big difference between the execution times.
> Also, both database server and application server in same VLAN and direct
> network connectivity is available without any interim hops.
The most obvious guess is that network latency matters. It may be a good
VLAN, but on the database server the connection is over shared memory
so it's not unreasonable that it takes more time to get it over the
wire.
Another thing to consider is the CPU power on the two machines.
Particularly if you run the output in text mode in QA, this could
matter.
Then again, I don't really see the point in measuring how long time
takes to receive 45000 rows in Query Analyzer. The tool is not really
intended for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Query Analyzer was used just to indicate a client application.
We are also monitoring the SQL calls using Profiler which is also giving us
the same output.
Also, the two boxes are of same configuration in terms of RAM & CPU.
You mentioned about connection being created in shared memory. Can anyone
please explain it in more detail as to how will it impact the query
performance? It might help us in understanding the problem better and work
towards a solution.
This time we enabled client statistics option in Query Analyzer. The
difference between two queries is for following attributes,
1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
(app server)] v/s 81 [for 30 sec execution (locally from database server)]
2. "Number of bytes received" recieved is slightly more for query executed
from app server compared to database server
What does "Cumulative wait time on server replies" stats represents and how
to interpret it?
Thanks in advance for the responses.
"Erland Sommarskog" wrote:

> pvv30 (pvv30@.discussions.microsoft.com) writes:
> The most obvious guess is that network latency matters. It may be a good
> VLAN, but on the database server the connection is over shared memory
> so it's not unreasonable that it takes more time to get it over the
> wire.
> Another thing to consider is the CPU power on the two machines.
> Particularly if you run the output in text mode in QA, this could
> matter.
> Then again, I don't really see the point in measuring how long time
> takes to receive 45000 rows in Query Analyzer. The tool is not really
> intended for that.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||pvv30 (pvv30@.discussions.microsoft.com) writes:
> Query Analyzer was used just to indicate a client application.
I don't think it's a good benchmark for that purpose, since QA does a lot
of things a client app wouldn't do. You are better of writing a client
application that just receives the dataset. Particularly, you should use
the client API you plan to use in your client application.

> You mentioned about connection being created in shared memory. Can anyone
> please explain it in more detail as to how will it impact the query
> performance? It might help us in understanding the problem better and work
> towards a solution.
Normally clients communicate with SQL Server over network connection, most
commonly these days is TCP/IP, but there is also support for named pipes
and a few more protocols.
But when the client and server are on the same physical machine, using
the network is bit of overkill and therefore there is the possibiliy to
use shared memory, and this is also the default when connecting locally.
You can use the Client Network Utility to turn off shared memory, but
even with TCP/IP locally, you would see much better execution times
locally than over the network.

> This time we enabled client statistics option in Query Analyzer. The
> difference between two queries is for following attributes,
> 1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
> (app server)] v/s 81 [for 30 sec execution (locally from database server)]
> 2. "Number of bytes received" recieved is slightly more for query executed
> from app server compared to database server
> What does "Cumulative wait time on server replies" stats represents and
> how to interpret it?
Books Online says "Cumulative amount of time the driver spent waiting for
replies from the server."
I would account the difference in numbers to the network connection. I
did a quick test on our site. I ran the same SELECT * against a 35000
row table on two servers. One which is hear at our main office in
Stockholm, and one which is at our branch office in Gvle. For the
server here in Stockholm the number was 60, and the server in Gvle
it was 12075. The difference in execution time was 2 seconds in
Stockholm and 28 seconds for the server in Gvle.
I ran both tests from my own machine, which hosts none of the servers in
question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Design

I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***Please do not multi-post. See my reply in comp.databases.ms-sqlserver.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bill Bob" <nospam@.devdex.com> wrote in message
news:uMGK1tFNGHA.1032@.TK2MSFTNGP11.phx.gbl...
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <= @.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil