@Vérace Thanks for the explanation, I'll try a safer option next time :) At least the one you went for (дякую, друже) was perfectly colloquial and appropriate for the occasion, well done!
@Vérace Wow, your answer is a lot more detailed than mine. Thanks for sharing! And-uh – I gcónaí sásta, teacht arís! (hope there's no insult in there somewhere)
@JOSHW Hey Josh. Your update seems to mention all the points I consider most relevant to the problem. I have one question though. Do you think #1 should specifically talk about rows where PLAN_LENGTH=12? My understanding is that in the end you want to process all the plans according to their lengths
@Vérace Fiancees tend to be right in these matters, so by all means make sure she's happy first :) I'm fine with whatever time you find convenient. I'm keeping an eye on the question anyway, and if you notify me of the change, that's even better, so it's all fine by me
I was struggling with syntax a bit, so maybe some things could be expressed better, but that query shows best what I actually was trying to do, it's just that T-SQL is a little lacking to make the result as elegant.
Anyway, here's something for dessert – a Postgres solution. Mine was essentially an attempt to emulate it, as much as absence of a generate_series option in T-SQL allowed me
The edit may need to be drastic and that's what makes me doubt whether I can do it correctly, both in terms of really improving the question and in terms of keeping the OP happy about their own effort invested into it (I mean, I wouldn't want to discard too much if it can be helped).
@Vérace Again, I would be more at ease if we also made the question clearer. I don't mind voting right now, I'd just prefer to vote for an already improved version of the question. I'm not sure how to go about it correctly if I were to do it myself. Even if I were sure enough that my synopsis, as the OP put it, would be clear enough for most readers, I still don't know how to incorporate it, that is, how much of the original description should be left, and would it need to be rearranged etc.
@Vérace I think the condition in #5 was more correct, because it resulted in only one row for 75491773,AMT00004, which matches the OP's expected result. The condition in #6 gave you two.
In other words, one of the uses of CROSS APPLY for me (and I think for many other people as well) is to generate calculated columns like that, so that you can reference the columns multiple times in the query, like in this case I'm using the result of the expression both in the SELECT and in the WHERE. If I didn't need to use it twice, I'd just calculate it in the SELECT, like in my last fiddle.
I'm using CROSS APPLY for it because I'm referencing the date more than once, that's basically the main reason. Without it the query would look like this
You know, I think our solutions aren't totally unlike each other. There are differences in filtering but I can see that fundamentally our approaches are similar
@Vérace I'm happy with that. We can also use this chat. In my estimation, it's just about making sure the phrasing is clear and unambiguous. But if you mean you want to rewrite the entire post and want my opinion, and so that's why you want to post it somewhere where it can be seen in full – no problem.
@Vérace Editing the question to make the problem clearer for everyone would make the task easier, I think. I wouldn't want people to rely just on our word. I think for now we should wait till the OP responds. Then we'll see what needs to be done.
I'd be happy to edit the description myself, it's just that sometimes I come up with wordings that may not work as well for others as they do for my non-English native mind. So I'd be more happy if you did it yourself, and if/when you do, feel free to borrow from my summary as much as you see fit in case you find any bits of it helpful.