SELECT a, MAX(CAST(b AS varchar(2)) + c) 
FROM (VALUES 
('A', 1, 'a'),
('A', 1, 'b'),
('A', 1, 'c'),
('A', 2, 'a'),
('B', 1, ''),
('B', 1, 'a'),
('B', 1, 'b'),
('B', 2,...

• This reply was modified 4 years, 3 months ago by  ChrisM@Work.

SELECT a, MAX(CAST(b AS varchar(2)) + c) 
FROM (VALUES 
('A', 1, 'a'),
('A', 1, 'b'),
('A', 1, 'c'),
('A', 2, 'a'),
('B', 1, ''),
('B', 1, 'a'),
('B', 1, 'b'),
('B', 2, ''),
('C', 1,...

If the symbols were the other way around it could be a graphic of a euphemism for falling over.

Your result set is dependant upon an order  yet your source data has no column defining this order. Is there such a column?

If so, can you provide a script to...

Thanks for response. So if I understand it correct from the above ....

one block of insert for locations and another for individuals

INSERT into dbo.Locations ...SELECT ....FROM dbo.HouseholdTemp WHERE <conditions>...

Sure. I think the logic of this is about right - it's hard to tell because there are so many IF blocks in the code:

INSERT INTO dbo.Locations...

You're populating these tables

dbo.Locations

dbo.Individuals

dbo.Households

dbo.ListTypes

dbo.HouseholdMailings

from this table: dbo.HouseholdTemp

You're working on one row at a time in a loop - this is why it's so slow. If you can rewrite it to...

Glenn Berry's suite of diagnostic queries is a great place to start:

https://glennsqlperformance.com/2020/02/04/sql-server-diagnostic-information-queries-for-february-2020/

The query could be without the cte

select
  t1.id, cast(t1.startdate as date) startdate, cast(t1.enddate as date) enddate, cast(td.enddate as date) range_end_dt
from
  dbo.test_1 t1
 cross apply
...

Thank you for your reply and help, this is working but I cannot understand why we are multiplying with 2* to the row number for e and s columns...

Here's a little sample data generator for anyone wishing to play along:

IF OBJECT_ID('TEMPDB..#test1') IS NOT NULL DROP TABLE #test1
SELECT RowID = ROW_NUMBER() OVER(ORDER BY ID, Startdate, Enddate),...

Thank you for your reply and help, this is working but I cannot understand why we are multiplying with 2* to the row number for e and s columns...

https://www.sqlservercentral.com/forums/topic/consolidate-overlapping-date-periods

SELECT ID, StartDate = MIN(ts), EndDate = MAX(ts)
FROM (
SELECT ID, ts, Snapper = (ROW_NUMBER() OVER(ORDER BY ID, ts)+1)/2
FROM (
SELECT *, se = ROW_NUMBER() OVER (PARTITION BY...

Those indexes on EffectiveDate are encouraging SQL Server to implement a triangular join, more about that in a minute. If you've added the EffectiveDate index specifically for this comparison between...

• This reply was modified 4 years, 6 months ago by  ChrisM@Work.

My apologies, but unfortunately, I am going to need more details.  Below is my stored procedure (at least the select portion causing this issue)

SELECT              
   ...