May 25, 2011 at 8:25 am
I'm still not sure exactly what you are looking for, but here are some thoughts I had that might be close, or at least generate some feedback to put me on the right path. I know these don't "run", I'm just looking to convey the ideas.
--Create a grid showing customers and the number of orders
--they have placed each month for the last X years
Customers
LEFT JOIN OrderHeader
RIGHT JOIN (Tally Table query creating all the months required)
(could also be written Customer CROSS JOIN Tally LEFT JOIN Header
to eliminate having both LEFT/RIGHT together)
--Aggregate an aggregate, average total customer sales
AVG(CustomerTotalSales)
FROM (
SUM(OrderTotal) CustomerTotalSales
FROM OrderHeader
GROUP BY Customer) DerivedTable
Thanks,
Chad
May 25, 2011 at 11:05 am
As far as what you're describing in the original post, I'd probably use an Outer Apply from A to B, or to a temp table that held the aggregated data from B. A warehouse with the data pre-aggregated would be even better.
But you're not asking for technical data or suggestions. You're asking for business cases.
The best one I've had for something that would require this kind of query complexity is when data in a single report needs to include aggregations on multiple dimensions, and you need that on rows you can't Group By.
If, for example, you need a list of all orders placed during X timespan in an e-commerce solution (so you can't aggregate by types of orders or anything like that), and you need each order to list aggregated data, like min and max inventory of the items ordered during that time span, and percentage of inventory used by that order, and deviation from average time-to-fulfill, all on the same report. You can't use a simple Group By to get the data, because it'll include "OrderNumber" in the Group By, and that's unique, so that'll break the overall aggregates, etc.
Is that the kind of situation you're running into here, Brandie? Or similar enough to allow an article to be written without proprietary data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 25, 2011 at 12:30 pm
By golly, Gus. You may have struck paydirt.
Lemme play with that scenario and I'll let you know if I need something else.
Thanks!
May 25, 2011 at 12:47 pm
Brandie Tarvin (5/24/2011)
I've seen this on the forums before, and have in fact experienced it several times myself. I want to write up an article, but I'm having trouble translating my reasons why (proprietary information conflict) into reasons why it happens in general.You have two tables. Table A has every record in existence. Table B only has a subset of Table A's records. You need to return a result that has the ID of all records in Table A, with extra columns from Table B that tell you statuses or stuff.
But for X reason, you can't do an OUTER JOIN from Table A to Table B. Your FROM clause has to start from Table B because you need to do fun, weird calculation type stuff or find Max records or something. So you have to start from B then go back to A and get the rest of the records.
Has anyone else dealt with this type of coding? If so, can you give me your reasons (generic or not) for why you did this?
Please note: I am NOT looking for code. I have code. I need business cases for which to explain the code.
Table A - all calls incoming to the Help Desk
Table B - all calls out from the Help Desk
How long was the incoming call?
For all out going calls, what was the delay in getting back to the user?
Multiple scenarios could be derived from such a model.
Based on the length of incoming calls, you might try and predict the outgoing responses. i.e. 20% of calls of this length would generate a callback of 15 minutes.
Greg E
May 25, 2011 at 12:49 pm
Oh... That's a good one too, Greg. Thanks.
May 31, 2011 at 7:02 am
We actually have something like this happening.
They try to predict staffing needs by looking for patterns.
May 31, 2011 at 7:27 am
Greg Edwards-268690 (5/31/2011)
We actually have something like this happening.They try to predict staffing needs by looking for patterns.
Really? What kind of patterns are they looking for? Patient volume, drug dispensing, testing?
May 31, 2011 at 9:33 am
Actually a Mfg company.
It might surprise some to find that Mfg Companies can be very challenging BI users.
Order Volumes - any there any days that are heavier volume?
What hours of the day show more volume?
How many calls are answered in particular timeframes? i.e. <10 seconds, <20 sec, etc.
Call duration by person.
Type of inquiry.
Lots of different mining opportunities.
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply