December 6, 2012 at 6:44 am
First a few assumptions:
We have a table, B, looking something like this:
RowId = 1, CustNo = 1, ProductCode = 12, SubCode = 1, NumberOfItems = 10, PartnerId=NULL
RowId = 2, CustNo = 1, ProductCode = 99, SubCode = 999, NumberOfItems = 1, PartnerId=NULL
RowId = 3, CustNo = 1, ProductCode = 88, SubCode = 888, NumberOfItems = 2, PartnerId=NULL
and so on...
The rows are unique and can be referred to by RowId.
We also have a mapping table Maps,
ProductCode = 12, SubCode = 1, PartnerProductCode = 99, PartnerSubCode = 999, PartnerName = 'Joe'
ProductCode = 12, SubCode = 1, PartnerProductCode = 88, PartnerSubCode = 888, PartnerName = 'Jim'
So, in fact the customer has bought 10 items '12' '1', 1 of which was from Joe, two from Jim, and the rest from somebody else.
SELECT P.RowId, P.NumberOfItems AS PartnerItems From P,M.PartnerName
INNER JOIN M
ON P.ProductCode = M.PartnerProductCode
AND P.SubCode = M.PartnerSubCode
Now, the exercise is (I know, I'm implying a cursor) to step through this table,
- find the corresponding number of non Partner items (10)
- deduct PartnerItems (1) and update number of items to (Items - PartnerItems) (9)
- Insert a record with CustNo = 1, ProductCode = 12, SubCode = 1, NumberOfItems = 1, PartnerId='Joe'
- Go to the next record
- deduct PartnerItems (2) and update number of items to (Items - PartnerItems) (7)
- Insert a record with CustNo = 1, ProductCode = 12, SubCode = 1, NumberOfItems = 2, PartnerId='Jim'
continue in this fashion until all records are processed.
I've mucked about with joins, but am clearly not bright enough to come up with a workable solution, so I resorted to a cursor.
It works, but it's so sloooow 🙁
TIA
Peter
December 6, 2012 at 8:08 am
What would help is if you could post the DDL (CREATE TABLE statement) for the tables involved, the sample data as a series of INSERT INTO statements to populate the tables, and the expected results of the processing.
December 6, 2012 at 8:10 am
Is there anything at all in your second table that indicates which customer the data is tied to? Also, anything that indicates sequence?
Honestly, the data looks poorly designed. As presented, it's not relational data, so relational tools may not work well on it, if they work at all. That means you may be stuck with procedural coding, like cursors.
Is this data coming from a non-relational source, like a set of text files or some such, as its original source?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 6, 2012 at 3:18 pm
GSquared (12/6/2012)
Is there anything at all in your second table that indicates which customer the data is tied to? Also, anything that indicates sequence?Honestly, the data looks poorly designed. As presented, it's not relational data, so relational tools may not work well on it, if they work at all. That means you may be stuck with procedural coding, like cursors.
Is this data coming from a non-relational source, like a set of text files or some such, as its original source?
Q1: In a word - no. There is nothing in the map table that ties it to the customer. It's purely a description of the products relation to a sub-product and the relation of the product to the vendor. I don't see the customer as central to the problem.
Q2: Yes, these are txt / csv files. Although I tend to agree, whether it's poor design is rather academic - it's what I'm stuck with.
As far as "not relational" goes - once they are loaded into tables, they are plenty relational.
A major part of the problem, as I see it, is that the sub products all relate to the same main product, but have no knowledge of each other.
December 7, 2012 at 12:05 am
Unfortunately you haven't been much help. You still haven't provided us with the information I requested which just makes it harder to try and provide you with an answer to your question.
The following is my first crack at an answer. Once you provide us with the information I requested I'm sure you may get many more possible solutions.
/*
RowId = 1, CustNo = 1, ProductCode = 12, SubCode = 1, NumberOfItems = 10, PartnerId=NULL
RowId = 2, CustNo = 1, ProductCode = 99, SubCode = 999, NumberOfItems = 1, PartnerId=NULL
RowId = 3, CustNo = 1, ProductCode = 88, SubCode = 888, NumberOfItems = 2, PartnerId=NULL
and so on...
The rows are unique and can be referred to by RowId.
We also have a mapping table Maps,
ProductCode = 12, SubCode = 1, PartnerProductCode = 99, PartnerSubCode = 999, PartnerName = 'Joe'
ProductCode = 12, SubCode = 1, PartnerProductCode = 88, PartnerSubCode = 888, PartnerName = 'Jim'
*/
with ProductData as (
select
RowId,
CustNo,
ProductCode,
SubCode,
NumberOfItems,
PartnerId
from
(values
(1,1,12,1,10,null),
(2,1,99,999,1,null),
(3,1,88,888,2,null))dt(RowId,
CustNo,
ProductCode,
SubCode,
NumberOfItems,
PartnerId)
)
--select * from ProductData
, Mapping as (
select
ProductCode,
SubCode,
PartnerProductCode,
PartnerSubCode,
PartnerName
from
(values
(12,1,99,999,'Joe'),
(12,1,88,888,'Jim'))dt(ProductCode,
SubCode,
PartnerProductCode,
PartnerSubCode,
PartnerName)
)
--select * from Mapping
,BaseData1 as (
select
pd1.RowId,
pd1.CustNo,
pd1.ProductCode,
pd1.SubCode,
pd1.NumberOfItems,
pd1.PartnerId,
map1.ProductCode mapProductCode,
map1.SubCode mapSubCode,
map1.PartnerProductCode,
map1.PartnerSubCode,
map1.PartnerName
from
ProductData pd1
left outer join Mapping map1
on (pd1.ProductCode = map1.PartnerProductCode and
pd1.SubCode = map1.PartnerSubCode)
)
select
bd1.RowId,
bd1.CustNo,
coalesce(bd1.mapProductCode, bd1.ProductCode) as ProductCode,
coalesce(bd1.mapSubCode, bd1.SubCode) as SubCode,
bd1.NumberOfItems - coalesce(PartnerItems,0) as NumberOfItems,
bd1.PartnerName
from
BaseData1 bd1
cross apply (select sum(NumberOfItems) PartnerItems from BaseData1 bd2 where bd1.CustNo = bd2.CustNo and bd1.ProductCode = bd2.mapProductCode and bd1.SubCode = bd2.mapSubCode)dt(PartnerItems);
December 7, 2012 at 1:59 am
Hi Lynn,
It looks like you got it right the first time - although I'm not sure I fully understand it (yet).
Unfortunately i haven't had time to provide you with a properly cleaned anonymous data-set. The real world keeps intruding 🙂
Also, I'm not sure how I can upload test data?
First off I'm going to work with your example and see if I ca get it to produce the correct results on my data.
I'll be back with questions if there are any. Until then, thank you for your help.
December 7, 2012 at 3:06 am
Peter Pirker (12/7/2012)
Hi Lynn,It looks like you got it right the first time - although I'm not sure I fully understand it (yet).
Unfortunately i haven't had time to provide you with a properly cleaned anonymous data-set. The real world keeps intruding 🙂
Also, I'm not sure how I can upload test data?
First off I'm going to work with your example and see if I ca get it to produce the correct results on my data.
I'll be back with questions if there are any. Until then, thank you for your help.
Read the first article I reference below in my signature block, it provides you with the information you need to know what and how to post the information.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply