Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL mapping detailed records to group records

Posted on 2016-11-08
9
Medium Priority
?
85 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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