I have managed to resolve this issue (it was keeping me up at night!)

I can hardly believe the solution and do not understand why it works, but it does. It...

What is the error message you receive?

Like a COALESCE with 10 arguments, for example. Why...

CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?

IIF is part of T-SQL and is more succinct than CASE, and...

That sounds like your entire process.

Doesn't your SSIS package contain multiple steps?...

• This reply was modified 4 years, 1 month ago by Phil Parkin. Reason: Fix typo

Indeed, but there is no ELSE in there, so we have to make assumptions about what...

all this web service is to process each route one by one for stops and students, and street corners. After one route, process another one. It repeats same process...

Would agree - except the solution doesn't work for dates where month is greater than 4

I read the requirement several times and ended up shrugging my shoulders and sending in...

Have you analysed execution logs to determine where the slowness occurs?

If you are hoping for a coded solution, please take the time to provide your sample data in a form which we can paste into SSMS and execute.

Frederico provided some.

String Concat missing, ur trying to concat a string and an integer

DECLARE @frdate date = '01-01-2021'DECLARE 
@ytdfrDate date
if Month(@frDate) < 4begin
SET @ytdfrDate = '01/04/' + cast(dateadd(y,-1,@frdate)...

Can you provide some consumable data, please? That is, some CREATE TABLE and INSERT statements which we can paste into SSMS and execute?

Also, please explain the link between Invoice and...

I don't think that we are in a position to take you much further with this task....

It would help if you would provide consumable data next time (check my code to see how this is done).

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
 ...

• This reply was modified 4 years, 1 month ago by Phil Parkin. Reason: Slight code improvement