April 22, 2013 at 1:18 pm
I asked for some ideas about this last week, but nothing quite suited my situation, which is kinda tricky...maybe if I explain it differently...
I need to run a query that gets each dealer_id in my database, looking at some criteria, in order to determine some sales numbers.
I have a list of about 5000 customers. Some of them have "linked" customers. I have a table that maps dealer_ids to any "child" dealer_ids they may have. Only about 25 dealers have "child" accounts.
I need to do the calculations mentioned about to get totals, percentages, etc for each dealer...
(Basically I total up a couple of sales numbers for each customer, and divide by a total inventory to show each dealer's percent of their inventory they've sold)
BUT...
If they have "child" accounts I have to add up the totals of the "parent" and all "children", base a percent calculation on that, and then apply that same percentage across the "parent" and each of the parent's "children". (Actually they're kinda treated like "siblings.") And then for dealers that have no children I just need to do the percentage calculations on them individually.
One way, I guess, is to do all the accounts NOT in the parent-child mapping table, and then come up with a second routine that handles the parent-with-children accounts.
This seems really clunky and convoluted though.
Has anybody ever seen something like this before? Is this a known type of routine? Has anyone seen any example? Or is this easy to write SQL for, but I'm not seeing it?
Any help VERY MUCH appreciated!
April 22, 2013 at 1:25 pm
brian.battles (4/22/2013)
I asked for some ideas about this last week, but nothing quite suited my situation, which is kinda tricky...maybe if I explain it differently...I need to run a query that gets each dealer_id in my database, looking at some criteria, in order to determine some sales numbers.
I have a list of about 5000 customers. Some of them have "linked" customers. I have a table that maps dealer_ids to any "child" dealer_ids they may have. Only about 25 dealers have "child" accounts.
I need to do the calculations mentioned about to get totals, percentages, etc for each dealer...
(Basically I total up a couple of sales numbers for each customer, and divide by a total inventory to show each dealer's percent of their inventory they've sold)
BUT...
If they have "child" accounts I have to add up the totals of the "parent" and all "children", base a percent calculation on that, and then apply that same percentage across the "parent" and each of the parent's "children". (Actually they're kinda treated like "siblings.") And then for dealers that have no children I just need to do the percentage calculations on them individually.
One way, I guess, is to do all the accounts NOT in the parent-child mapping table, and then come up with a second routine that handles the parent-with-children accounts.
This seems really clunky and convoluted though.
Has anybody ever seen something like this before? Is this a known type of routine? Has anyone seen any example? Or is this easy to write SQL for, but I'm not seeing it?
Any help VERY MUCH appreciated!
You probably didn't get want you wanted because we could not see clearly what was needed.
If you could post the DDL for the table(s) involved, some sample data (not real data), and expected results based on the sample data you will probably get better answers.
Please read the first article I reference in my signature block as it will walk you through everything you need to post and how to post it. In return for this extra effort you will get tested code in return.
Right now, I really have no idea how I would approach your problem as I can;t see what you see.
April 22, 2013 at 2:28 pm
This sounds like a very typical parent-child type of situation. How do you code for this? That depends entirely on what your ddl looks like. It might be a recursive CTE, it might be as simple as a where clause if you used nested sets. As Lynn already said the only way we can offer any real help is if you present the entire question, which in this case means providing ddl and sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 2:31 pm
Thanks for the pointers, Lynn!
[font="Courier New"]Parent-Child Mapping Table:
ParentChild
32256504
32256505
32256666
32257777
etc…
Vendors Table (some Vendors here are NOT in parent-child mapping table):
Vendor IDSoldTotal Inventory Value
1011 $291,393.98 $3,353,695.20
2022 $29,308.59 $65,607.55
3033 $684,942.09
4044 $39,089.12 $429,638.92
5055 $25,650.59 $374,750.02
3225(I left some of these blank because I got bored typing in sample values)
6504
6505 $33,878.99
6555
6600
6657 $39,089.12 $429,638.92
6658 $25,650.59 $374,750.02
etc…
RESULT:
Vendor IDSoldTotal Inventory ValueUnsold PctUse Combined Pct on all that DO have a parent-child grouping
3225 $291,393.98 $3,353,695.20 4.2%5.9%
6504 $29,308.59 $65,607.55 44.7%5.9%
6505 $81,148.04 $684,942.09 8.2%5.9%
6657 $39,089.12 $429,638.92 9.1%5.9%
6658 $25,650.59 $374,750.02 6.8%5.9%
Combined $466,590.32 $4,908,633.78 5.9%
1011 $291,393.98 $3,353,695.20 3.5%
2022 $29,308.59 $65,607.55 44.7%
3033 $684,942.09 0.0%
4044 $39,089.12 $429,638.92 9.1%
5055 $25,650.59 $374,750.02 6.8%
3225
6504
650533,878.99100,000.0033.9%
6555
6600[/font]
Sorry this isn't formatted so well, I need to learn how to do that here...this came from an Excel sheet.
April 22, 2013 at 2:41 pm
The point is, I'm applying one calculation to EVERY vendor in the main vendors table...inventory value divided by sales value, to get what percentage of inventory has been sold.
BUT
If the Vendor is IN the "mapping" table so that Vendor also has "child" accounts, then I total up all the sales_value and inventory_value data from each account (vendor and all vendor's "children"), calculate the sales percentage based on the TOTAL of all accounts' inventory divided by the TOTAL of all accounts' sales, and show THAT percentage for the vendor and all the vendors "child" accounts.
Only about 20 Vendors out of about 5000 are in the mapping table, most vendors do not have "child" accounts.
It would be easy if EVERYONE or NO ONE had parent-child accounts...but the fact that they're mixed makes it tricky.
One thing I did think of is making sort of a temp table, showing all Vendors in a pseudo-mapping table; those without actual "children" only map to themselves. Buy I still have to know which ones have to get their totals from multiple accounts and then use the resulting percentage for all those accounts.
My brain is a little sore...
April 22, 2013 at 2:45 pm
I will assume that you still didn't read the article. You are much more likely to get help if you turn that into ddl (create table scripts) and sample data (insert statements).
That way we can just load the data into tables easily and we can spend our time working on your issue instead of working on setting up the problem. From what you posted it looks like a rCTE is the most likely path to a solution.
Please help us to help you by providing enough information to help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 2:50 pm
Hmm..have to see if I can figure out how to do that...I've done 99.9% of my SQL using pretty standard (but creative!) queries from VBA, MS Access, etc, and have only done "real" SQL Server scripting regularly for a few months, so I'm still pretty sloppy with some of the mysterious and secretive ways of doing things in this world... 🙂
April 22, 2013 at 3:33 pm
brian.battles (4/22/2013)
Hmm..have to see if I can figure out how to do that...I've done 99.9% of my SQL using pretty standard (but creative!) queries from VBA, MS Access, etc, and have only done "real" SQL Server scripting regularly for a few months, so I'm still pretty sloppy with some of the mysterious and secretive ways of doing things in this world... 🙂
To script your table, right click on the table in Object Explorer -> Script Table As -> Create To ->
This will create your table script.
Then take your data that you posted and turn it into insert statements.
Select 'HardCodedValue1', 'HardCodedValue2' union all
Select 'HardCodedValue1', 'HardCodedValue2'
Then test your script in a sandbox database. Once you have the kinks worked out it should run and create the table with data.
I could probably guess at your datatypes and cobble together something but it would be a hack at best.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply