Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 7th, 2007, 07:50 AM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default Grouping By query

Hi,

I have created the following query and need some assistance with the Grouping (Output).

Code:
INSERT INTO dbo.MER_Sales
(
ORDER_ID,
MAT_YEAR,
Delivery_Address,
Rep_SalesStats,
REP_ID,
MERCHANT,
Payer_TradeAddress,
PAYER,
PRODUCT,
ITEM_QUANTITY,
ITEM_VALUE,
Period,
Division_Reference
)
SELECT 
'', 
dbo.RMS_SALESSTATS.SOS_YEAR,
dbo.Delivery_Address.Delivery_Address_ID,
dbo.RMS_SALESSTATS.SOS_REP,
dbo.MER_REPS.REP_ID,
'',
CustAddress.ADD_CODE,
dbo.MER_Payer.Payer_ID,
dbo.MER_Product.Product_ID,
CAST(dbo.RMS_SALESSTATS.SOS_QUANTITY_7 AS DECIMAL (18,4)),
CAST(dbo.RMS_SALESSTATS.SOS_VALUE_7 AS DECIMAL (18,2)),
'7', 
'1' 
FROM 
(((((((dbo.RMS_SALESSTATS JOIN dbo.RMS_ADDRESS AS CustAddress ON 
CustAddress.ADD_CODE = dbo.RMS_SALESSTATS.SOS_CUSTOMER AND
CustAddress.ADD_COMPANY = dbo.RMS_SALESSTATS.SOS_CO) 
JOIN dbo.RMS_ADDRESS AS LocAddress ON
LocAddress.ADD_CODE = dbo.RMS_SALESSTATS.SOS_LOCATION AND
LocAddress.ADD_COMPANY = dbo.RMS_SALESSTATS.SOS_CO)
JOIN dbo.RMS_TRADERS ON
dbo.RMS_SALESSTATS.SOS_CUSTOMER = dbo.RMS_TRADERS.TRD_CODE AND
dbo.RMS_SALESSTATS.SOS_CO = dbo.RMS_TRADERS.TRD_COMPANY)
JOIN dbo.RMS_ADDRESS As TradeAddress ON
dbo.RMS_TRADERS.TRD_COMPANY = TradeAddress.ADD_COMPANY AND
dbo.RMS_TRADERS.TRD_INVOICE_REF = TradeAddress.ADD_CODE)
JOIN dbo.Delivery_Address ON
dbo.RMS_SALESSTATS.SOS_CUSTOMER = dbo.Delivery_Address.ADD_CODE)
LEFT JOIN dbo.MER_REPS ON
dbo.RMS_SALESSTATS.SOS_REP = dbo.MER_REPS.ADD_CODE)
LEFT JOIN dbo.MER_PAYER ON
dbo.MER_Payer.Customer_Reference = dbo.RMS_SALESSTATS.SOS_CUSTOMER)
LEFT JOIN dbo.MER_Product ON
dbo.MER_Product.Product_Reference = dbo.RMS_SALESSTATS.SOS_PRODUCT 
WHERE 
dbo.RMS_SALESSTATS.SOS_RECTYPE = 'B' AND
dbo.RMS_SALESSTATS.SOS_CO = 'B' AND
CAST(dbo.RMS_SALESSTATS.SOS_QUANTITY_7 AS DECIMAL (18,4)) > 0.0000 AND 
CAST(dbo.RMS_SALESSTATS.SOS_VALUE_7 AS DECIMAL (18,2)) > 0.00 AND
CustAddress.ADD_TYPE = 'C' AND
dbo.RMS_TRADERS.TRD_TYPE = 'C' AND
TradeAddress.ADD_TYPE = 'C' AND
LocAddress.ADD_TYPE = 'L' AND
SUBSTRING(LocAddress.ADD_Message, 9, 2) = 'KA' AND
dbo.Delivery_Address.Division_Reference = '1' AND
dbo.MER_REPS.Division_Reference = '1' AND
dbo.MER_Payer.Division_Reference = '1' AND
dbo.MER_Product.Division_Reference = '1' 
GROUP BY dbo.RMS_SALESSTATS.SOS_YEAR,
dbo.Delivery_Address.Delivery_Address_ID,
dbo.RMS_SALESSTATS.SOS_REP,
dbo.MER_REPS.REP_ID,
CustAddress.ADD_CODE,
dbo.MER_Payer.Payer_ID,
dbo.MER_Product.Product_ID,
dbo.RMS_SALESSTATS.SOS_QUANTITY_7,
dbo.RMS_SALESSTATS.SOS_VALUE_7
[u]KEY</u>
The 'dbo.MER_Sales' table key is a combination of:
MAT_YEAR,
Delivery_Address,
REP_ID,
MERCHANT,
PAYER,
PRODUCT,
Period,
Division_Reference

[u]QUESTION</u>
What I need to do is sum all occurences of dbo.RMS_SALESSTATS.SOS_QUANTITY_7 and dbo.RMS_SALESSTATS.SOS_VALUE_7 in a single record, where the Key is unique.

Can I achieve it within this INSERT query, or do I need to create another query based on the MER_SALES ('ITEM _QUANTITY' and 'ITEM_VALUE') record results?

Any help please?

Thanks in advance,


Neal

A Northern Soul
__________________
Neal

A Northern Soul
 
Old June 11th, 2007, 09:11 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

I think I'd probably make a derived table with a GROUP BY to do that work and then join with the rest of your query... you might be able to drop some of the other joins because of it...

--Jeff Moden




Similar Threads
Thread Thread Starter Forum Replies Last Post
grouping logic mike123 XSLT 7 October 27th, 2007 01:20 PM
XSLT Grouping vernc XSLT 2 October 2nd, 2007 03:59 AM
Grouping and Pagination jayne XSLT 13 June 6th, 2007 01:52 AM
Protect cells and allow grouping/un-grouping sfreuden Excel VBA 4 December 14th, 2006 08:01 AM
Grouping List chemi XSLT 1 October 20th, 2005 09:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.