[Edit] This is pretty much the same as Phil's code in the other thread with same name.

with improper_cte(FirstName, LastName, DOB, ID) as (
    select...

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

Instead of writing a query to do this crosstab would it be possible to go back to whatever code landed you at those 2 rows with different ID's in the...

Here's something close but not matching because maybe there's an issue with the example results?  Could you explain for LID='L005' and EID='E002' how ALLO='21.00' and DAYSS='6.00' and then the RL...

• This reply was modified 5 years, 2 months ago by Steve Collins. Reason: Added ORDER BY and simplified query

A framework-ish way could be to use the awkward DATETIMEFROMPARTS and its 7 required parameters.

declare
  @today        [varchar](12) = '44:10:27.766',...

select c1.*
from #categories1 c1
where not exists(select 1
                 from #categories2 c2
 ...

These 2 queries produce the output you're looking for.  It should be said this is not a properly normalized data model.  There seemingly are "non-modelled" relationship(s) in the data which...

Nicely done The Dixie Flatline 🙂

What about checking @@rowcount?  This way 0 would only show up if there are no rows

SELECTCount(InvNo) AS RecordCount, BatchID
FROMBatchOrders
GROUP BY BatchID;
if @@ROWCOUNT=0
    SELECT 0,...

COUNT doesn't return 0 because you've also requested BatchID in the SELECT list.  The FROM clause is evaluated first and there are no rows.  On its own COUNT will always...

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

Ok, Category appears to not be a reserved word and appears in white.  Period and Value are both blue.  So it's brackets, no brackets, and brackets instead of brackets, brackets,...

To subtract the values the rows would need to be joined in some way.  Since maybe some Category(ies) is/are not always present for each Period you could try OUTER APPLY...

Like what Mr Brian Gale described you could try a combination of LEAD and LAG functions

with lead_lag_cte as (
    select *,
   ...

Interesting news.  Thank you.  Please reach out if there's anything I could do that might help.  Regarding the forum there was never a UI built so it's currently DDL, SQL...

That's really awesome Steve.  I know a lot of people are going to be happy you've picked up the ball with this and in this way too.  That's awesome.  It's...

It was very quick, and I knew about it yesterday, but didn't really do much other than give a vote to try. Still amazed it happened.

Comments, suggestions,...