December 10, 2009 at 3:51 am
I know how people love T-SQL puzzles, had a more complex request come through, I thought I had it cracked, but then missed part of the output required (Its more complex than it looks).
Rules: A Single query must satisfy scenario 1 & 2. (i.e. a single select statement to get output to satisfy both conditions)
: Code can be SQL 2005 Compatible.
: Cursors are banned, Temp Tables are banned.
I have included some T-SQL Code at the end to save setting up values for testing, proving output.
All that is missing is the solution T-SQL Query that satisfies both conditions.
Add start_ids / end_ids - based on row_limit without
duplicates (can overlap where required to ensure range covered).
(Can use any SQL 2005 compatible feature)
Scenario 1: (Row_limit in this case = 160)
Setup Data:
Table: Destination (has the following data)
Start_IDEnd_ID
-900-800
-800-700
-700-600
-600-500
-500-400
-200-100
Table: Source (Contains the ID Range, which has the following data)
ID
-1000
... (1 row all the way to -1)
-1
Get the following output: (by using source table/row_limit/destination setup values)
Table: Destination
Start_IDEnd_ID
-1000-840
-900-800
-800-700
-700-600
-600-500
-500-400
-400-240
-240-80
-200-100
-10060
Scenario 2: (Row_limit in this case = 140)
Setup Data:
Table: Destination (has the following data)
Start_IDEnd_ID
-500-400
-200-100
0100
400500
Table: Source (Contains the ID Range, which has the following data)
ID
-500
... (1 row all the way to -1)
+499
Get the following output:
Table: Destination
Start_IDEnd_ID
-500-400
-200-100
-10040
0100
100240
240380
380520
400500
--------------------------------------------------------------------------------------------------------------
Helpfull T-SQL Code:
/*Setup Data for the scenarios, to make coding the puzzle easier.*/
DECLARE @destination_scenario1 TABLE (start_id INT,end_id INT)
DECLARE @destination_scenario2 TABLE (start_id INT,end_id INT)
DECLARE @row_limit_scenario1 INT
DECLARE @row_limit_Scenario2 INT
DECLARE @source_scenario1 TABLE(id INT)
DECLARE @source_scenario2 TABLE(id INT)
SELECT @row_limit_scenario1 =160 , @row_limit_Scenario2=140
INSERT INTO @destination_scenario1 (start_id,end_id)
SELECT -900 start_id, -800 end_id
UNION
SELECT -800, -700
UNION
SELECT -700,-600
UNION
SELECT -600, -500
UNION
SELECT -500,-400
UNION
SELECT -200,-100
INSERT INTO @destination_scenario2 (start_id,end_id)
SELECT -500 start_id, -400 end_id
UNION
SELECT -200,-100
UNION
SELECT 0,100
UNION
SELECT 400,500
-- Add data for the scenarios, to make life easier.
;
WITH autonumbering ( row )
as ( SELECT 1 row
UNION all
SELECT a.[row]+1
FROM autonumbering a
WHERE a.[row]<1000 )
INSERT INTO @source_scenario1 ( id )
SELECT -1*a1.[row] id
FROM autonumbering a1
OPTION ( MAXRECURSION 1000 ) ;
INSERT INTO @source_scenario2
( id )
SELECT id+500 FROM @source_scenario1
-- Show all test_data/scenario_data at start.
SELECT 'Source_Scenario1',* FROM @source_scenario1
SELECT 'Source_Scenario2',* FROM @source_scenario2
SELECT 'Destination_Scenario1',* FROM @destination_scenario1
SELECT 'Destination_Scenario2',* FROM @destination_scenario2
/*SETUP Expected Output Results for the two scenarios*/
DECLARE @Output_scenario1 TABLE (start_id int,end_id int)
DECLARE @Output_scenario2 TABLE (start_id int,end_id int)
INSERT INTO @Output_scenario1 ( start_id,end_id )
SELECT start_id,end_id FROM @destination_scenario1
UNION
SELECT -1000,-840
UNION
SELECT -400,-240
UNION
SELECT -240,-80
UNION
SELECT -100,60
INSERT INTO @Output_scenario2 ( start_id,end_id )
SELECT start_id,end_id FROM @destination_scenario2
UNION
SELECT -400,-260
UNION
SELECT -260,-120
UNION
SELECT -100,40
UNION
SELECT 100,240
UNION
SELECT 240,380
UNION
SELECT 380,520
-- Show all expected result outputs
SELECT 'Output_Scenario: 1', * FROM @Output_scenario1
SELECT 'Output_Scenario: 2', * FROM @Output_scenario2
December 10, 2009 at 5:12 am
Can you explain why...
1. -700 -600 is not inserted into @destination_scenario1?
2. -400 -260 and -260 -120 are not expected outputs for scenario 2
Thanks 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
December 10, 2009 at 5:18 am
Here's my first stab in the meanwhile...
; with
t1 as (SELECT *, row_number() over (order by start_id) drow FROM @destination_scenario1)
, t2 as (select id, max(drow) as drow from @source_scenario1 a left outer join t1 b on a.id >= b.end_id group by id)
, t3 as (select *, row_number() over (partition by drow order by id) as row from t2)
select start_id, end_id from t1 union all
select id, id+@row_limit_scenario1 from t3 a where row % @row_limit_scenario1 = 1 and not exists (select * from t1 where start_id <= a.id+1 and a.id+1 <= end_id)
order by start_id
Change scenario1 to scenario2 as required.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
December 10, 2009 at 5:22 am
Well spotted Ryan, just modified the origonal post.
1. -700 -600 is not inserted into @destination_scenario1?
--> Yes: it should be there, well spotted Ryan.
2. -400 -260 and -260 -120 are not expected outputs for scenario 2
: -400 -260
-260 -120
--> Well spotted, they should be there.
I've just updated the "helpful SQL code"
December 10, 2009 at 5:24 am
Nice one Ryan, looks like a great piece of code- fits perfectly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply