• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

TSQL mapping detailed records to group records

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
OriNetworks
Asked:
OriNetworks
2 Solutions
 
lcohanDatabase AnalystCommented:
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
 
OriNetworksAuthor Commented:
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
 
hnasrCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eugene ZCommented:
<The query I am using correctly returns data in the format....>

Can you post your query?
0
 
OriNetworksAuthor Commented:
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
 
hnasrCommented:
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
 
OriNetworksAuthor Commented:
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
 
OriNetworksAuthor Commented:
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
 
hnasrCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now