July 31, 2008 at 12:15 pm
I was wondering if this is possible to do in SQL. I am currently on SQL Server 8.0 and I have the following Query:
SELECT T1.A5CPGP AS [CUSTOMER PRICE GROUP],
T3.KIICID AS [ITEM KEY],
T4.BPLITM AS [ITEM NUMBER],
T4.BPUPRC AS [UNIT PRICE],
T4.BPEFTJ AS [DATE EFF FROM],
T4.BPEXTJ AS [DATE EFF TO]
FROMF0301 T1
INNER JOIN F4094 T3 ON T1.A5CPGP = T3.KIPRGR
INNER JOIN F4106 T4 ON T3.KIICID = T4.BPICID
WHERET4.BPEFTJ <= GETDATE()
ANDT4.BPEXTJ >= GETDATE()
I am looking to only return records when the T4.BPUPRC is different within the group. In the example below there are 6 different Customer Price Groups for each Item Number that I have. Sometimes the unit price is the same, but sometimes they are different due to promotions, or special pricing for a particualr region. In this example, I would only want to see the second set of data for ITEMKEY2 since there are differences in the unit price (T4.BPUPRC ) field. Is there a way to achieve this? Thanks!!
CUSTPRCGRP1ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP2ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP3ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP4ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP5ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP6ITEMKEY1 ITEMNUM11.792008-01-012008-06-30
CUSTPRCGRP1ITEMKEY2 ITEMNUM22.032008-01-012008-06-30
CUSTPRCGRP2ITEMKEY2 ITEMNUM22.032008-01-012008-06-30
CUSTPRCGRP3ITEMKEY2 ITEMNUM22.892008-01-012008-06-30
CUSTPRCGRP4ITEMKEY2 ITEMNUM22.892008-01-012008-06-30
CUSTPRCGRP5ITEMKEY2 ITEMNUM22.032008-01-012008-06-30
CUSTPRCGRP6ITEMKEY2 ITEMNUM22.032008-01-012008-06-30
July 31, 2008 at 12:39 pm
Not sure about the syntax but i would do something like this.
SELECT T1.A5CPGP AS [CUSTOMER PRICE GROUP],
T3.KIICID AS [ITEM KEY],
T4.BPLITM AS [ITEM NUMBER],
T4.BPUPRC AS [UNIT PRICE],
T4.BPEFTJ AS [DATE EFF FROM],
T4.BPEXTJ AS [DATE EFF TO]
FROM F0301 T1
INNER JOIN F4094 T3 ON T1.A5CPGP = T3.KIPRGR
INNER JOIN F4106 T4 ON T3.KIICID = T4.BPICID
INNER JOIN
(
SELECT
T3.KIICID,
T4.BPLITM,
COUNT(DISTINCT T4.BPUPRC) NUM_OF_ITEM_NUMBERS -- NOT NEEDED
FROM F0301 T1
INNER JOIN F4094 T3 ON T1.A5CPGP = T3.KIPRGR
INNER JOIN F4106 T4 ON T3.KIICID = T4.BPICID
WHERE T4.BPEFTJ <= GETDATE()
AND T4.BPEXTJ >= GETDATE()
GROUP BY
T3.KIICID,
T4.BPLITM
HAVING COUNT(DISTINCT T4.BPUPRC) > 1
)
TBL1 ON T3.KIICID = TBL1.KIICID AND T4.BPLITM = TBL1.BPLITM
WHERE T4.BPEFTJ <= GETDATE()
AND T4.BPEXTJ >= GETDATE()
July 31, 2008 at 12:40 pm
GROUP BY the other fields. I think that will get you where you want to be.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 31, 2008 at 1:49 pm
Very close, but the distinct count is giving me a value of '1' since it truly is a distinct record. The Customer Price group is what makes the record distinct and I can't seem to get the correct count. Not sure if maybe I am just not grouping correctly, or what is going on. Thanks!!
July 31, 2008 at 2:05 pm
Also, how would this work on a single table? Is it possible if I am trying the same thing but on 1 table? Thanks!
July 31, 2008 at 2:30 pm
It should work. We are only doing a distinct on the price. see below. Also this is a good example of running the same thing on a single table.
CREATE TABLE #T1
(
A5CPGP VARCHAR(16),
KIICID VARCHAR(16),
BPLITM VARCHAR(16),
BPUPRC NUMERIC(10,2),
BPEFTJ DATETIME,
BPEXTJ DATETIME)
INSERT #T1
VALUES
('CUSTPRCGRP1','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP2','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP3','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP4','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP5','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP6','ITEMKEY1','ITEMNUM1','1.79','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP1','ITEMKEY2','ITEMNUM2','2.03','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP2','ITEMKEY2','ITEMNUM2','2.03','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP3','ITEMKEY2','ITEMNUM2','2.89','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP4','ITEMKEY2','ITEMNUM2','2.89','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP5','ITEMKEY2','ITEMNUM2','2.03','2008-01-01','2008-06-30')
INSERT #T1
VALUES
('CUSTPRCGRP6','ITEMKEY2','ITEMNUM2','2.03','2008-01-01','2008-06-30')
SELECT * FROM #T1
SELECT T1.A5CPGP AS [CUSTOMER PRICE GROUP],
T1.KIICID AS [ITEM KEY],
T1.BPLITM AS [ITEM NUMBER],
T1.BPUPRC AS [UNIT PRICE],
T1.BPEFTJ AS [DATE EFF FROM],
T1.BPEXTJ AS [DATE EFF TO]
FROM #T1 T1
INNER JOIN
(
SELECT
KIICID,
BPLITM,
COUNT(DISTINCT BPUPRC) NUM_OF_ITEM_NUMBERS -- NOT NEEDED
FROM #T1
GROUP BY
KIICID,
BPLITM
HAVING COUNT(DISTINCT BPUPRC) > 1
)
TBL1 ON T1.KIICID = TBL1.KIICID AND T1.BPLITM = TBL1.BPLITM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply