Journal Marxist Hacker 42's Journal: Transact SQL- inventing fake records efficiently 3
This is going to seem downright blasphemous to some data scientists, but it is what I'm being asked to do in support of a Power BI Report and some weird behavior on the part of the business.
Basically, I have a list of results from tests, and am being asked to invent a new category of results for estimated number of tests when the total number of tests is less than the recorded estimate.
I already have two recordsets- the list of real results, and the categorized list of the difference between Real and Estimate.
I'm sure I could, with a double while loop and a cursor, simply loop through the recordset and then run a while loop
But there has to be a way to do this with set operations.
Basically, what I need is something like:
Insert Into Results (Result, EndDate-1hour, category, subcategory) Select Result, Enddate-1hour, category, subcategory for records loop NumberofRecords=EstimateDelta
But even with 21 years of experience in SQL, I don't know quite how to do that.
Re: (Score:1)
It is super sketchy. It's "we did x number of tests but estimated y number of tests, if xy can you show the difference as "estimated but not run" tests so that we know how much more we need to do"?
Oh, and then to boot, the estimate is only a total by fk1 and fk2, and needs to be applied across a time dimension fk3.
I did come up with a super sketchy answer involving a temp table with 3000 negative keys, but I don't think they're going to be entirely happy with the answer (one team has been overestimating th
Re: (Score:1)
Final Answer (database names changed to protect the innocent):
Truncate Table dbo.TestEventEstimatesHistory
Print 'TestEventsEstimatesHistory insert'