drop table if exists #sale_order;
drop table if exists #sale;
drop table if exists #sale_result;
go

create table #sale_order(
    id int identity primary key
    ,item_id...

Why not update the 'sale' table directly?  Do the 'sale' table and 'sale_result' table always have the same # of rows?  If so, could this be implemented by making 'sale'...

Is it correct: rows with NULL ship_date in the 'sale' table are created before the corresponding ship_date in the sale_order table?  Units always ship in the quantity ordered?

That's nice!

The way this is set up isn't straightforward because there's no way to join to the duplicated rows in sale WHERE ship_date is null.  I re-jiggered the script to not...

• This reply was modified 5 years, 8 months ago by Steve Collins.

This is simpler

declare
  @start_dt              datetime='2019-12-02',
  @end_dt      ...

The dbo.daterange function, which is awesome, is described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

declare
  @start_dt              datetime='2019-12-03',
  @end_dt        ...

This sql gives me 'deja view' because the OP has posted many questions on SSC.  ahmed_elbarbary.2010 please recognize the answers in some way.  I posted an answer a few months...

The other constraint on the size of unique keys is the number of columns, which I believe is 16.  Bigint is only 8 bytes.  So to the extent all of...

select stuff((select concat(' ', TeamName, ',', concat(TeamName, 'Date')) 
from #Teams for xml path ('')), 1, 1, '');

O the question is for 2012.  Sorry, it needs the xml trick.  The above is 2017+

select string_agg(concat(TeamName, ',', concat(TeamName, 'Date')), ',') from #Teams;

Because the start date, end date combinations aren't uniformly spanning 2 years the first part of the script creates the year bands using a tally table.  Then the StudentData is...

This is very tricky.  I'm this far and my @$%@$ pivot isn't work for some reason and I can't figure out why.  If (or when) it works my plan is/was...

• This reply was modified 5 years, 8 months ago by Steve Collins.