you have already been given the answer to your last (and prior questions) - if they are above your head then consider hiring a professional (as you have been advised...

you made it clear that you do not wish the job to run on the first of the month if it it is on a Sunday - but what do...

spam - exact copy of https://www.reddit.com/r/aws/comments/100je4m/step_functions_trigger_rule/

what mention of weekends? there is nothing on the original OP post that mentions...

what mention of weekends? there is nothing on the original OP post that mentions it neither...

To be absolutely clear, you want a:

2) unique clustered [index] on (tenant,year,id)

And if you partition the data (which typically wouldn't be necessary here), partition it...

don't worry about it.

thanks Jeff.

regarding the "performance and cartesian joins" - splitting the CTE to insert the BASE dates onto a temp table and then joining that temp table onto the main table...

• This reply was modified 3 years, 5 months ago by frederico_fonseca. Reason: add machine spec

To be absolutely clear, you want a:

2) unique clustered [index] on (tenant,year,id)

And if you partition the data (which typically wouldn't be necessary here), partition it by (tenant,...

I added 1000 "sets" of 3 groups, each with 13 dates.  The Cartesian products persisted in the execution plan, each of them doing reads of 39,078,039 rows.

Too bad because,...

@frederico_fonseca I had the same idea as you initially, but your solution will not work either. What if there were 20 groups instead of 3? What if the gaps...

in order to split a string like that into individual columns you have to first split into rows then aggregate onto columns

sample code below -  function DelimitedSplit8K_LEAD is from Erikur...

@jeff-moden - did a small change on original code to "correct" one duplicated entry that was on both group 2 and 3 (darn time part messing up)

• This reply was modified 3 years, 5 months ago by frederico_fonseca.
• This reply was modified 3 years, 5 months ago by frederico_fonseca.

This is a delayed message as I...

my attempt at this.

Do note that the 5 days interval is partially misleading as to the final result because of the time part.

,(7 ,'2017-11-17 09:38:00.000') --Group 2 starts here

,(7 ,'2017-11-12...

• This reply was modified 3 years, 5 months ago by frederico_fonseca. Reason: change to remove an error - lines changed tagged with " -- CHANGE to line below"