Solved

TSQL mapping detailed records to group records

Posted on 2016-11-08
9
30 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 39

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 30

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
 
LVL 42

Expert Comment

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

Can you post your query?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 30

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 30

Accepted Solution

by:
hnasr earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now