please help ... goods price list query
-
my current sql query :
SELECT GdsSellPriceDet.GdsCode, Gds.GdsName, Gds.CcyCode, Gds.UnitCode, GdsSellPriceDet.CustGroupCode, CustomerGroup.CustGroupName,
GdsSellPriceDet.DefaultSellPrice
FROM GdsSellPriceDet LEFT OUTER JOIN
CustomerGroup ON GdsSellPriceDet.CustGroupCode = CustomerGroup.CustGroupCode LEFT OUTER JOIN
Gds ON GdsSellPriceDet.GdsCode = Gds.GdsCode
and the result is :
GdsCode GdsName CcyCode UnitCode CustGroupCode CustGroupName SellPrice
AAA GOODS AAA USD UNIT RTL RETAIL 1.7
AAA GOODS AAA USD UNIT WHO WHOLESALE 1.5
BBB GOODS BBB USD UNIT RTL RETAIL 1.8
BBB GOODS BBB USD UNIT WHO WHOLESALE 1.8
CCC GOODS CCC USD UNIT RTL RETAIL 2.1
CCC GOODS CCC USD UNIT MOD MODERN MARKET 2.1
CCC GOODS CCC USD UNIT WHO WHOLESALE 2.0
the result i need is below :
GdsCode GdsName CcyCode UnitCode CustGroupCode CustGroupName SellPrice
AAA GOODS AAA USD UNIT RTL RETAIL 1.7
AAA GOODS AAA USD UNIT WHO WHOLESALE 1.5
BBB GOODS BBB USD UNIT RTL RETAIL 1.8
CCC GOODS CCC USD UNIT RTL RETAIL 2.1
CCC GOODS CCC USD UNIT WHO WHOLESALE 2.0
the query i need : if a goods code has 3 records with 2 of them have the same price, the second record with th same price will be skipped ( like the example above ).
i need some advice, would you help me to solve this case ?
thanks alot for your help.....
rom beng
-
So why did one of the BBB rows go away? BBB only had two rows...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
to mr jeff moden,
yes, 1 of BBB records will not be included in the new result because has the same price with the other BBB row on purpose to save paper if it is printed.
the current query i mentioned is the normal query, and i want only rows with difference price viewed in the new result.
sorry, i hope you'll understand my english, my english not so good and understand what i mean.
please, any suggestion ? thanks in advance
rom beng
-
to mr bledu,
the priority is the first record found using different price will be viewed in the new result.
in the purposed result, the second BBB record will not be included because has same price with previous BBB shown.
anu suggestion ? please help and thanks alot....
below the table definition :
/****** Object: Table [dbo].[GdsSellPriceDet] Script Date: 8/3/07 3:06:35 PM ******/
CREATE TABLE [dbo].[GdsSellPriceDet] (
[GdsCode] [nvarchar] (20) NOT NULL ,
[CustGroupCode] [nvarchar] (3) NOT NULL ,
[DefaultSellPrice] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Gds] Script Date: 8/3/07 3:06:35 PM ******/
CREATE TABLE [dbo].[Gds] (
[GdsCode] [nvarchar] (20) NOT NULL ,
[GdsName] [nvarchar] (100) NULL ,
[BrandCode] [nvarchar] (3) NULL ,
[GdsGroupCode] [nvarchar] (3) NULL ,
[CcyCode] [nvarchar] (3) NULL ,
[UnitCode] [nvarchar] (4) NULL ,
[LastUpdate] [datetime] NULL ,
[UserID] [nvarchar] (10) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CustomerGroup] Script Date: 8/3/07 3:06:33 PM ******/
CREATE TABLE [dbo].[CustomerGroup] (
[CustGroupCode] [nvarchar] (3) NOT NULL ,
[CustGroupName] [nvarchar] (50) NULL ,
[LastUpdate] [datetime] NULL ,
[UserID] [nvarchar] (10) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [i_Gds] ON [dbo].[Gds]([GdsCode]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [i_GdsSellPriceDet] ON [dbo].[GdsSellPriceDet]([GdsCode], [CustGroupCode]) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [i_CustomerGroup] ON [dbo].[CustomerGroup]([CustGroupCode]) ON [PRIMARY]
GO
-
Can you define which row to be considered "first"?
There is no natural physical order of rows in tables.
_____________
Code for TallyGenerator -
Rom Beng,
Something like this should do it for you... read the embedded comments for an explanation, please... I did not use your given table structures... this is all based on the data from your first post... if you were to use your first query to populate a temp table similar to that below, you'll be done.
--=======================================================================================
-- Setup and populate a test table.
-- NOTE THAT NOTHING IN THIS SECTION IS PART OF THE SOLUTION. IT'S FOR DEMO ONLY!.
--=======================================================================================
--DROP TABLE #GdsSellPriceDet
--===== Create a test table to run with
CREATE TABLE #GdsSellPriceDet
(
GdsCode VARCHAR( 5),
GdsName VARCHAR(10),
CcyCode VARCHAR( 5),
UnitCode VARCHAR( 5),
CustGroupCode VARCHAR( 5),
CustGroupName VARCHAR(20),
SellPrice DECIMAL(9,1) --Precision of 1 to 9 always takes 5 bytes
)
--===== Populate the test table with the given data
INSERT INTO #GdsSellPriceDet
(GdsCode, GdsName, CcyCode, UnitCode, CustGroupCode, CustGroupName, SellPrice)
SELECT 'AAA','GOODS AAA','USD','UNIT','RTL','RETAIL' ,'1.7' UNION ALL
SELECT 'AAA','GOODS AAA','USD','UNIT','WHO','WHOLESALE' ,'1.5' UNION ALL
SELECT 'BBB','GOODS BBB','USD','UNIT','RTL','RETAIL' ,'1.8' UNION ALL
SELECT 'BBB','GOODS BBB','USD','UNIT','WHO','WHOLESALE' ,'1.8' UNION ALL
SELECT 'CCC','GOODS CCC','USD','UNIT','RTL','RETAIL' ,'2.1' UNION ALL
SELECT 'CCC','GOODS CCC','USD','UNIT','MOD','MODERN MARKET','2.1' UNION ALL
SELECT 'CCC','GOODS CCC','USD','UNIT','WHO','WHOLESALE' ,'2.0'
--=======================================================================================
-- Demo the solution for the given data.
-- In the absence of all other information (like what the PK is), it is assumed
-- that the following columns are sufficient for identifying unique rows...
-- GdsCode, CcyCode, UnitCode, CustGroupCode
--
-- Note, too, that the "dupes" are selected in alpha order according to the
-- CustGroupCode. This means that the precise row you wanted suppressed for CCC is
-- has been replaced by a different row. Again, in the absense of all other info,
-- this is about the best that can be done.
--=======================================================================================
SELECT t3.*
FROM #GdsSellPriceDet t3
LEFT OUTER JOIN
(--==== Derived table "d" finds "dupes" to NOT be shown (alpha order on CustGroupCode)
SELECT t2.GdsCode,t2.CcyCode,t2.UnitCode,t2.CustGroupCode
FROM #GdsSellPriceDet t1,
#GdsSellPriceDet t2
WHERE t2.GdsCode = t1.GdsCode
AND t2.CcyCode = t1.CcyCode
AND t2.UnitCode = t1.UnitCode
AND t2.SellPrice = t1.SellPrice
AND t2.CustGroupCode > t1.CustGroupCode
)d
ON t3.GdsCode = d.GdsCode
AND t3.CcyCode = d.CcyCode
AND t3.UnitCode = d.UnitCode
AND t3.CustGroupCode = d.CustGroupCode
WHERE d.GdsCode IS NULL
ORDER BY t3.GdsCode, t3.CcyCode, t3.UnitCode, t3.CustGroupCode
If you have SQL Server 2005, you could use your original query as a CTE for the above solution.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
P.S. If you do decide to go the temp table route as I suggested, then adding the appropriate indexes and a primary key on the temp table may help speed things up a bit, but it's going to be pretty fast as is and I don't think you'll be able to get any Index SEEKs out of it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
It seems you overcomplicated the last query.
This returns the same result:
SELECT t1.*
FROM #GdsSellPriceDet t1
LEFT JOIN #GdsSellPriceDet t2
ON t2.GdsCode = t1.GdsCode
AND t2.CcyCode = t1.CcyCode
AND t2.UnitCode = t1.UnitCode
AND t2.SellPrice = t1.SellPrice
AND t2.CustGroupCode < t1.CustGroupCode
WHERE t2.GdsCode IS NULL
And again,
you (and me after you
) assume that "first" means "having minimal CustGroupCode".No evidence it's true.
_____________
Code for TallyGenerator -
Boy, I'll say... dunno why I made the dupe check separate...
must've been that old donut I ate this morning 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
> must've been that old donut I ate this morning
Mmmmm... Dooonut...
_____________
Code for TallyGenerator -
to mr jeff moden and mr serqiy,
i already combined your suggestions and the result was very good, met my requirement about saving papers.
i considered my case is solved, thank you very much for all your help and attention.
thanks again....
rom beng
-
Thanks for the feedback, Rom.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
If the purpose was to save paper, why not do a sort of pivot query and put all the prices for each line item on a seperate line?
-
Heh... or concatenate the groups on a single line?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply