Oh, also, you can put it in one case statement. If [DTNF logic] is on the list, then column 2 of list, else [DTNF logic].
Otherwise you’re just bifurcating it in that you have to put the “else” part into the logic that builds the list. You’re not getting rid of that logic… you’re simply moving it to a different part of the process in an inefficient way.
Personally I’d do it with
[DTNF logic] = field
If field in list then replace with column 2
That’s a little faster.
Seriously? You think that using a row by row function (I believe SQL Server 2008 was the first) saves you 335 minutes rather than do a simple join and table lookup (to a small table). Using a function in most cases just saves you writing a few lines of code, virtually nothing in run time.
I’ve already said, go ahead use the function. I’ve proposed a solution which you have improved with your beloved dayofweek - still waiting to hear your suggestion that doesn’t use a date table.
Lol, making a play on “Murder will out”. Mrs G and I watch enough murder mysteries to be prime suspects 
DATENAME goes back at least to 2002, and no, that’s not what I said. (I didn’t use SQL prior to 2002, so I really have no idea how long it had been around prior to that.). DAYOFWEEK is easier syntax than DATENAME, and you may well be right about 2008 for DAYOFWEEK.
That 400 minute code was sloppy in ways too numerous to list, but one of them was certainly dates. There was complex and wholly unnecessary logic to determine whether the last day of February was the 28th or 29th that repeated in several places. I don’t think they cared about days of the week or business days, so it wasn’t the exact same issue… just needlessly sloppy coding when SQL has an end of month feature that will figure out February 28 or 29 for you. They’d also coded which months had 31 and 30 days… 
I will give credit that their insane February 28 vs 29 logic DID return the correct answer… including for the year 2100, which is divisible by 4 but NOT a leap year. It checked whether the year was divisible by 4, 100, and 400 and then made the correct 28 vs 29 choice based on the answers. But once again, EOMONTH replaced about 12 lines of code with 7 characters.
So in summary, I proposed a series of steps that has run for me in the past within seconds. It included a small date lookup table with two columns, the code for which can be easily be found on the internet (and quickly reduced to one column with the additional function). But because I proposed that extra column, you think I’m the guy who writes spaghetti February 29 code that runs for 400 minutes. Got it.
I’m pretty strongly biased against unnecessary lookups, no question.
Also pretty strongly opposed to hiding logic in tables. From an audit standpoint I’d rather be able to follow right in the code what’s going on. The less referencing tables the better.
I agree. But the code will need to determine which dates are business days. I can’t see how you can determine that without a lookup, particularly if business days are user defined.
And I agree with this.
The table will have to be proofread by a contingent of interested parties.
Depending on the business in question.
I think you should look for an official list of dates that the US Equity Markets have been open. That is the documentation required.
I mean, you COULD code in logic to figure out all of the observed holidays and avoid lookup tables altogether.
BUT… that’s such a hassle, and subject to change at the whim of an executive (July 4 is a Tuesday this year? Eh, let’s give everyone the 3rd too, just to be nice!) that I wouldn’t go to that length to avoid a table.
At that point I think it’s easier to just maintain a list of the observed holidays where you can easily add in an extra.
But maintaining the prior business day for every day is too unwieldy IMO.
Not really. It’s a one-and-done thing for the past. And, won’t need to update the future until a whim (or a catastrophe) occurs.
Also, SG specifically stated “US Equity Market Days.” So, look them up, document the source, and done.
Ok, well I disagree but I’m tired of arguing about it.
Equity Market days are less likely to change going forward than employer holidays, but they can: see Juneteenth.
I’ve said how I’d do it, but… not my circus, not my monkeys.
How do you disagree? SG has US Equity Market Dates as a field and wants to create a field for the prior market date.
The solution is to find all the “US Equity Market holidays,” and not worry about any specific company’s holidays, because SG is not worried about it.
I get that you think your solution with its assumptions will work here, cuz twig, but it won’t work here for this problem.
SG: make a table, have it double-checked, document the sources, fix as necessary for catastrophic events that closed the market.
Fucking easy.
So I am tracking the “daily” fund returns on our fund admin system. Each day (after the first for a particular fund) rolls from the previous. So supposing we didn’t trade on a particular day that we should have, I would ascribe the growth over the period to the whole multi-day period, even if there were no information for intermediate period. Missing trades on a fund-by-fund basis would make the date table/date matching fund dependent. But fortunately, I have clean (enough) data that this isn’t a problem.
Source table is a heap, no indexes, nothing special. 18 fields, I think 131 bytes per record, 39.6M records, 6691 distinct valdates
WITH VD (ValDate) AS
(SELECT DISTINCT valdate FROM source ),
VDO (Valdate, OrderNum) AS
(SELECT ValDate, RANK() OVER (ORDER BY ValDate) AS OrderNum FROM VD),
VDP (ValDate, OrderNum) AS
(SELECT ValDate, OrderNum + 1 AS OrderNum FROM VDO)
SELECT VDO.ValDate AS CurrDate, VDP.ValDate AS PriorDate FROM
VDO INNER JOIN VDP ON VDO.OrderNum = VDP.OrderNum
ORDER BY CurrDate
Runs in about 2 seconds (didn’t use timer just info on info bar). Gives me 6690 records (No prior on first record, but that’s fine for me).
Sounds like a waste of time and energy. But, someone wants it, so first thing to do is to prove that it’s a waste of time. Unless you’re hourly – then waste all the time you can until that someone figures it out on their own.
What you’ve done is get all the US Equity Market Dates from your database. This should be OK to use.
Line them up in Excel.
Paste them one column over and one row down.
Upload Table.
Done.
Not pretty, but Done.
I use the dailies to compute longer period returns. Can’t just use ME/QE/YE values since dividends occur off-period.
But even if I didn’t have the requisite curiosity and access to the data, someone up the chain wants the results.
And I did consider the Excel method outlined. Rank() approach entirely inside SQL seemed like less work ITLR. Insert appropriate xkcd.com comic.
This should be easy, but…SQL…
Here’s my code:
SELECT SUM(StatReserve) as StatReserve
FROM [db]
WHERE [Company] = 'ABC'
AND [PlanCode] IN ('123','456','789')
GROUP BY [Company]…but I don’t want to actually group by company. I want to group by a made up variable, call it PRODUCT.
In this case, PlanCodes ‘123’, ‘456’, & ‘789’ make up the PRODUCT “UL” so the results should look something like this:
| PRODUCT | StatReserve |
|---|---|
| UL | 666.66 |
How many PRODUCTS do you have?
For a few, use a CASE:
SELECT
CASE WHEN [PlanCode] IN (‘123’,‘456’,‘789’) THEN ‘UL’
WHEN…(other plan codes and their Product)
ELSE…
END as PRODUCT,
SUM(StatReserve) as StatReserve
FROM [db]
INNER JOIN
WHERE [Company] = ‘ABC’
– AND [PlanCode] IN (‘123’,‘456’,‘789’)
GROUP BY PRODUCT
For hundreds, make a table, upload it to your server (if you’re allowed to), called PlanToProd (heh-heh):
SELECT
A.PRODUCT,
SUM(B.StatReserve) as StatReserve
FROM [db].B
INNER JOIN PlanToProd A
ON A.PlanCode = B.PlanCode
WHERE [Company] = ‘ABC’
GROUP BY A.PRODUCT
Just one.
Thank you for your suggested solution. To my credit (?) I had tried that already but get this lovely message: “Invalid column name ‘Product’.”
Wait, do I need to put that case when else end statement into the GROUP BY statement?
Yes…yes, I do. Now all is right with the world.