Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TSQL mapping detailed records to group records

Posted on 2016-11-08
9
Medium Priority
?
91 Views
Last Modified: 2016-11-08
I have a detail table with order information and I use a query with group by to rollup the details into summary lines by customer. The key here is that the SUB_GROUP_KEY is the

select ORDER_NO, CUSTOMER, MIN(LINE_NO) AS SUB_GROUP_KEY, SUM(QTY) AS SUBTOTAL FROM Orders GROUP BY ORDER_NO,CUSTOMER

Open in new window


The detail table is
ORDER_NO, CUSTOMER, LINE_NO, QTY
12345          CompanyABC            1        5
12345          CompanyABC            2        5
12345          CompanyXYZ             3        5

The query I am using correctly returns data in the format
ORDER_NO, CUSTOMER, SUB_GROUP_KEY, QTY
12345          CompanyABC                        1        10
12345          CompanyXYZ                         3         5

What I need to be able to do is create a mapping table to be able to work backwards. It should show what LINE_NO detail records are included in a SUB_GROUP_KEY
LINE_NO    SUB_GROUP_KEY
1                  1
2                  1
3                  3

I obviously cant SELECT LINE_NO and MIN(LINE_NO) as SUB_GROUP_KEY
0
Comment
Question by:OriNetworks
9 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41879091
In my opinion all needed is a table to include the columns and data from ORDER_NO,  and LINE_NO and this combination should be also unique as you can't have the exact same item twice in an order but just quantity increased on it right?
0
 
LVL 17

Author Comment

by:OriNetworks
ID: 41879111
There are lots of other fields on the detail table that are involved in the rollup of data. I cannot change the table, I just need to a query to collect the data from it. I'm trying to give the most simple example here and I can add the additional data required in the grouping later.

Maybe I'm just anxious for lunchtime but I'm drawing a blank here on how to query the data have this mapping table.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41879141
able to work backwards
Not sure if you can do that.

Reporting is grouping data according to criteria, and once grouped, they cannot be reworked back to original values.
In essence, work back is the original tables.

If you can add more info, by uploading a sample database, and elaborate more, it will add to our understanding.
1
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 41879235
<The query I am using correctly returns data in the format....>

Can you post your query?
0
 
LVL 17

Author Comment

by:OriNetworks
ID: 41879275
EugeneZ:  The query is posted above. That provides the "summary" of information.

I need to be able to create a way to see which detailed lines were included in each grouping.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41879294
If you have this:
LINE_NO    SUB_GROUP_KEY
1                  1
2                  1
3                  3
ORDER_NO, CUSTOMER, LINE_NO, QTY
12345          CompanyABC            1        5
12345          CompanyABC            2        5
12345          CompanyXYZ             3        5

The query I am using correctly returns data in the format
ORDER_NO, CUSTOMER, SUB_GROUP_KEY, QTY
12345          CompanyABC                        1        10
12345          CompanyXYZ                         3         5

Then what output are looking for?
0
 
LVL 17

Author Comment

by:OriNetworks
ID: 41879305
This is the result I am looking for which shows both the detail line number and the group key.

LINE_NO    SUB_GROUP_KEY
 1                  1
 2                  1
 3                  3
0
 
LVL 17

Assisted Solution

by:OriNetworks
OriNetworks earned 0 total points
ID: 41879328
I have my own solution here but it isn't pretty. By joining the detail table to its summarized result and putting every field in the join condition I can get the mapping of order+group key to detail.

creating some sample data:
if OBJECT_ID('tempdb..#orders') is not null
BEGIN	
drop TABLE #orders 
END
create table #orders
(ORDER_NO int,
CUSTOMER varchar(15),
LINE_NO int,
ITEM varchar(15),
QTY int,
SHIP_DATE smalldatetime)
insert into #orders select 12345,'CustomerABC',1,'Item1',5,'1/1/2017'
insert into #orders select 12345,'CustomerABC',2,'Item1',6,'2/2/2017'
insert into #orders select 12345,'CustomerXYZ',3,'Item1',4,'1/1/2017'
insert into #orders select 99999,'CustomerABC',1,'Item1',5,'1/1/2017'

Open in new window


Select the summarized data:
select ORDER_NO, CUSTOMER, ITEM, MIN(LINE_NO) AS SUB_GROUP_KEY, SUM(QTY) AS SUBTOTAL FROM #orders GROUP BY ORDER_NO,CUSTOMER,ITEM
ORDER BY ORDER_NO,SUB_GROUP_KEY

Open in new window


This gets me what I need:
select summary.ORDER_NO,summary.SUB_GROUP_KEY, detail.LINE_NO, detail.*  From 
(select ORDER_NO, CUSTOMER, ITEM, MIN(LINE_NO) AS SUB_GROUP_KEY, SUM(QTY) AS SUBTOTAL FROM #orders GROUP BY ORDER_NO,CUSTOMER,ITEM
) summary
inner join #orders  detail on summary.ORDER_NO=detail.ORDER_NO and summary.CUSTOMER=detail.CUSTOMER and summary.ITEM=detail.ITEM

Open in new window


I added some more fields to the sample data to give an idea of join conditions but there are 15-20 other fields that I would have to join on. I guess I can get around this by joining the #orders table yet again using order and line_no as join conditions but it seems excessive to join the table 3 times. I will leave the question open for a short while in case anyone has any better options.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 41879506
The last output, can be produced using this query:
It is the main table increasing the projection by SUB_GROUP_KEY, which is derived from the minimum of LINE_NO in each group of:
ORDER_NO, CUSTOMER, and ITEM

SELECT ORDER_NO,(SELECT MIN(LINE_NO) FROM #ORDERS AS i WHERE i.ORDER_No=o.ORDER_NO AND i.CUSTOMER=o.CUSTOMER AND i.ITEM=o.ITEM) AS SUB_GROUP_KEY,LINE_NO, CUSTOMER, ITEM,QTY,SHIP_DATE
FROM #ORDERS AS o;

Open in new window


Still the problem is nuclear to me.
Thanks for supplying the data, it is very helpful. The communication will be simplified to the same data.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question