Medians, Quartiles, Quintiles, Deciles, Percentiles. Moreover these measures are reported grouped by any number of columns (and I have never seen this capability anywhere else). In this context, the use of a cursor is mandatory!
Median as a measure of central tendency is okay when calculated in SQL Server. But as the median alone is not terribly useful, I generally prefer the use of a specialized software called Prophet or simply download the data to Excel for any statistical stuff I do. Because, as you have implied the code will become messy and might require a cursor (actually I never have thought about calculating the things you've mentioned in SQL Server)
As for gdefi:
I agree, each one to his taste.
I know Excel's limitation pretty well
I didn't mean I download hundreds of thousands of rows. The degree of accuracy doesn't grow significantly when you look at the whole population or take a sample beyond of say 30+ observations. That's quite handable for Excel.
As for Excel's habit of giving wrong answers. I made the experience that nobody's really interested in precise numbers as long as the wanted trend is there
In fact, try to explain to a marketing oriented, and therefore visually thinking CEO concepts like confidence intervals, standard error of the sample mean or finite population correction factors. At a best he doesn't listen to you. I know because I've tried (once)
Sorry, forgot to add that when I mean I use Excel I didn't mean that I also use Excel's built-in functions, which I agree are very limited. We use a third-party function library.
We're drifting off-topic.
Last one from me. When we run projections on the whole portfolio of our insured persons we deal with tens of millions of records. Yes, records, not rows, because none of the systems we've looked at utilizes a database but rather small flat files on the filesystem. These are highly specialized systems for asset liability management. Nobody expects response time <1 second. Or even one hour. Usually we start the run before we leave and it finishes somewhere during the night, so the results are available the next morning. What I mean to say, sometimes one is better of thinking off-database and consider the use of a dedicated software package.
Just a small question:
How are you handling the different datatypes (eg. int, datetime money etc..) what you application would require?
Either you will use several columns of each required datatype (it means a lot of NULL values within your table or you will have to cast or convert the string value to the required datatype.
What are you doing with these lookups? Are you modelling business processes and/or enforcing referential integrity? Or are you populating drop downs?
You simply cannot sensibly combine all objects (OO sense, but not necessarily strict) into one table. You could not combine int and char types. Would you mingle apples and cats?
Are you populating a series of text drop downs, but not validating real relationships? I suppose commingling would be OK. But as soons as you want to validate, you're going back to the object sense and they should be separate.
Drop downs and lookups are relationships-light. As soons as you want real relationships, combined lookup and code tables are a nightmare.
Even if the DRI is reasonable, what's worse a bunch of tables and relationshisp tieing to one table, or a bunch of tables tieing to their own tables? I think people make too much of additional tables. Additional, cross-the-diagram relationships are worse. A decent modeling tool will allow you to have diagrams omitting low importance tables, so you won't be bothered by too many tables and their lookup tables.