May 21, 2008 at 10:28 pm
How would a guy go about selecting data out of a table in this situation.
I want to start at the beginning of a table say itemnum 1 in the table and do a query on every item in that table.
When a certain criteria is found I want to select that row into a temp table and continue selecting each row after that into the table also, until another set of criteria is met and then continue on down through the file.
For example if I have rows 1-5000 and row 10 meets the criteria that I want to start writing to a temp table. I would want it to put row 10 into the temp table and then continue with each row after that until it comes to the next criteria say that is found in row 20. So in my temp table I would have rows 10-20, then I would want the query to continue through the table until it finds the correct criteria and start inserting then when it finds the stop criteria stop inserting.
IF this does not make sense please do let me know and I can try to be more clear, what I am asking I don’t think is that hard, however with my limited knowledge I am struggling and Would really appreciate a little help.
Thanks so much in advance!!!
May 21, 2008 at 10:51 pm
i have few questions
1. How many criterias are there?
2. Is it possible that criteria 1 would come again after row 20 say at row 200?
3. What is the main purpose behind doing the required task this way?
May 22, 2008 at 12:27 am
Hi,
Actually, I tried something based on your explanation.
declare @i int
set @i = 0;
while ( @i < = 290)
begin
if ((@i >= 10 ) and (@i <= 20 ))
begin
insert into #temp
select * from HumanResources.Employee where EmployeeID = @i
end
set @i = @i + 1
end
select * from #temp
If you explain your requirement still clearly, I will try to find out the solution for that. Just try this.
May 22, 2008 at 1:03 am
Hi,
Provide us some TableDefinitions along with sample Datas and expected Results from them along with the conditions to match in the Table to help you in querying
Rajesh
May 22, 2008 at 5:41 am
As others have already pointed out, to really answer the question you need to post some structures and some sample code.
But, taking a stab at it, it sure sounds like you're just talking about a regular old query. Don't get hung up on this idea of walking through the data row-by-row. That's not set-based thinking. Think in terms of operating against the entire data set, all at once, or in a couple of passes, but not pushing & pulling data into temporary tables matching each row to some arbitrary value. That's what Jeff Moden calls rbar, Row-By-Agonizing-Row, processing.
Instead, determine that you have a base set of data, your table, that has to meet one piece of criteria, col1 is greater than 10, and another set of criteria, col1 is less than 20. Now you have the beginning of your query. The engine will worry about the row-by-row matches. You worry about arriving at the set:
SELECT *
FROM TableA AS a
WHERE a.Col1>10 AND a.Col1 <20
Then, if you determine that every time there's an odd number in Col1 you need to show the string "Odd" and the column value but every time it's "Even" you need to show that and the column value, again, you adjust this set, not try to walk this set or the original table, row-by-row.
SELECT a.Col1
,CASE WHEN (Col1%2=0) THEN 'Even'
ELSE 'Odd' END AS ColName
FROM TableA AS a
WHERE a.Col1>10 AND a.Col1 <20
No walking through the table. It's a set. Always think about the set that you want to arrive at and how best to arrive at it. Don't think about rows.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 22, 2008 at 11:38 am
First off Let me say thanks for your prompt response, to all who replied. I hope this makes it much more clear what I am trying to accomplish.
The query I was refering to earlier would use the following data, but much much more of it. I would want to start pulling the rows where k1=200050 and k6=9 and then stop when K1!=200050 and k6=9. So for example If I could have a query do what I wanted on this table the first section it would grab would be from itemnum 2930 - 2934 then it would start skipping rows until it came to itemnum 2941 and would then select until it hit itemnum 2943. So the groupings of items I woudl like to see would be in this example
itemnums
2930 - 2934
2941 - 2943
2974 - 2991
2998 - 3001
My start point is the k1=200050 and k6=9
My stop point would be where k1!=200050 and k6=9
I need it to go through the whole table starting and stoping. Please let me know your thoughts.
item- K1 K2 K3 K4 K5 K6 K7 K8 K9
num
2930 200050 0 1001.50 0 20002000045 9 0 0 8290251
2931 105010 0 118.50 0 20002000046 601 0 0 8290251
2932 564857 601 600.00 0 2000047 0 0 0 9210033
2933 987654 868 355.00 0 2000048 0 0 0 44444444
2934 12597 869 165.00 0 20002000049 51 0 0 8290251
2935 10727 0 2399.60 0 20002000050 9 0 0 8290251
2936 105030 0 1369.23 0 20002000051 703 0 0 8290251
2937 200050 2200 67.60 0 20002000052 51 0 0 8290251
2938 852369 56601 962.77 0 2000053 0 0 0 9210070
2939 500050 0 121.30 0 20002000054 373 0 0 8290251
2940 200050 2201 121.30 0 20002000055 51 0 0 8290251
2941 200050 0 106.40 0 20002000056 9 0 0 8290251
2942 654987 121201 40.40 0 2000057 0 0 0 9210033
2943 200070 1402 66.00 0 20002000058 51 0 0 8290251
2944 200070 0 525.00 0 20002000059 9 0 0 8290251
2945 200050 2203 275.20 0 20002000060 51 0 0 8290251
2946 5760001 0 249.80 0 20002000061 500 0 0 56100000
2948 200060 601 41.00 0 20002000063 51 0 0 8290251
2949 200060 602 58.50 0 20002000064 51 0 0 8290251
2950 300110 0 78.20 0 20002000065 145 0 0 8290251
2951 300080 0 199.00 0 20002000066 109 0 0 8290251
2952 105030 0 40.40 0 20002000067 703 0 0 8290251
2953 200060 603 26.10 0 20002000068 51 0 0 8290251
2954 200080 1592 32.50 0 20002000069 51 0 0 8290251
2955 200080 1591 100.00 0 20002000070 51 0 0 8290251
2956 400070 0 500.00 0 20002000071 209 0 0 8290251
2957 902565 0 500.00 0 20002000072 145 0 0 8290251
2958 400050 0 1000.00 0 20002000073 209 0 0 8290251
2959 95162847 35687 1000.00 0 2000074 0 0 0 44444444
2960 300050 0 120.00 0 20002000075 109 0 0 8290251
2961 200070 1400 116.60 0 20002000076 51 0 0 8290251
2962 105030 0 3.40 0 20002000077 703 0 0 8290251
2963 105010 0 75.00 0 20002000078 601 0 0 8290251
2964 300060 0 75.00 0 20002000079 145 0 0 8290251
2965 300070 0 40.00 0 20002000080 109 0 0 8290251
2966 1045001 0 40.00 0 20002000081 500 0 0 56100000
2967 348228 0 219.95 0 20002000082 373 0 0 8290251
2968 200070 1403 219.95 0 20002000083 51 0 0 8290251
2969 352810 0 197.20 0 20002000084 375 0 0 8290251
2970 200080 1593 197.20 0 20002000085 51 0 0 8290251
2974 200050 0 375.25 0 20002000006 9 0 0 8290251
2975 105030 0 30.30 0 20002000007 703 0 0 8290251
2976 200080 1595 111.30 0 20002000008 51 0 0 8290251
2977 200060 606 77.25 0 20002000009 51 0 0 8290251
2978 200070 1401 156.40 0 20002000010 51 0 0 8290251
2979 400080 0 2500.00 0 20002000011 209 0 0 8290251
2980 1234567 25161 2500.00 0 2000012 0 0 0 44444444
2981 400060 0 250.00 0 20002000013 209 0 0 8290251
2982 5760001 0 250.00 0 20002000014 500 0 0 8290251
2983 300080 0 200.00 0 20002000015 109 0 0 8290251
2984 56897412 7899 45.00 0 2000016 0 0 0 44444444
2985 57891532 1699 155.00 0 2000017 0 0 0 44444444
2986 300070 0 80.00 0 20002000018 109 0 0 8290251
2987 56897412 7898 80.00 0 2000019 0 0 0 44444444
2988 300060 0 100.00 0 20002000020 109 0 0 8290251
2989 105030 0 100.00 0 20002000021 703 0 0 8290251
2990 105010 0 25.00 0 20002000022 601 0 0 8290251
2991 300050 0 25.00 0 20002000023 145 0 0 8290251
2992 200070 0 310.30 0 20002000024 9 0 0 8290251
2993 105030 0 103.30 0 20002000025 703 0 0 8290251
2994 200060 600 54.10 0 20002000026 51 0 0 8290251
2995 200060 604 33.30 0 20002000027 51 0 0 8290251
2996 200050 2205 44.60 0 20002000028 51 0 0 8290251
2997 200080 1594 75.00 0 20002000029 51 0 0 8290251
2998 200050 0 225.00 0 20002000030 9 0 0 8290251
2999 105030 0 57.30 0 20002000031 703 0 0 8290251
3000 200070 1407 11.50 0 20002000032 51 0 0 8290251
3001 200080 1597 156.20 0 20002000033 51 0 0 8290251
3002 200080 0 633.20 0 20002000034 9 0 0 8290251
3003 105030 0 23.40 0 20002000035 703 0 0 8290251
3005 200070 1405 101.20 0 20002000037 51 0 0 8290251
3006 200050 2202 25.30 0 20002000038 51 0 0 8290251
May 22, 2008 at 11:48 am
SQL will only return a entire recordset. If you need to inspect some column in each row and do something with the column. You will need to use the FETCH method. Below is an example.
DECLARE @company varchar(5), @program varchar(5)
DECLARE personnel_cursor CURSOR FOR
SELECT company ,program FROM HITSInventoryPersonnel
ORDER BY company
OPEN personnel_cursor
-- Perform the first fetch.
FETCH NEXT FROM personnel_cursor
INTO @company, @program
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
If @company = 'AFL'
Begin
update HITSInventoryPersonnel set program='AFL'
where current of personnel_cursor -- update current fetched row
End
If @company = 'HITS'
Begin
update HITSInventoryPersonnel set program='HITS'
where current of personnel_cursor -- update current fetched row
End
FETCH NEXT FROM personnel_cursor
INTO @company, @program
END
CLOSE personnel_cursor
DEALLOCATE personnel_cursor
May 22, 2008 at 12:17 pm
Or, as has been sugested previously - you can do it in a set-based fashion.
this should do pretty well, especially when compared to Cursor methods. It does rely on making sure that your sequences are well formed (so you'd have to have perfectly alternating "starts and ends". Two starts or 2 ends in a row would lead to junk).
--build some test data
drop table items
create table items(itemnum int ,K1 int,K2 int,K3 numeric(18,9),K4 int,K5 bigint,K6 int,K7 int,K8 int,K9 bigint)
insert items(itemnum,K1,K2,K3,K4,K5, K6,K7,K8,K9)
select 2930,200050,0,1001.50,0,20002000045,9,0,0,8290251 Union all
select 2931,105010,0,118.50,0,20002000046,601,0,0,8290251 Union all
select 2932,564857,601,600.00,0,2000047,0,0,0,9210033 Union all
select 2933,987654,868,355.00,0,2000048,0,0,0,44444444 Union all
select 2934,12597,869,165.00,0,20002000049,51,0,0,8290251 Union all
select 2935,10727,0,2399.60,0,20002000050,9,0,0,8290251 Union all
select 2936,105030,0,1369.23,0,20002000051,703,0,0,8290251 Union all
select 2937,200050,2200,67.60,0,20002000052,51,0,0,8290251 Union all
select 2938,852369,56601,962.77,0,2000053,0,0,0,9210070 Union all
select 2939,500050,0,121.30,0,20002000054,373,0,0,8290251 Union all
select 2940,200050,2201,121.30,0,20002000055,51,0,0,8290251 Union all
select 2941,200050,0,106.40,0,20002000056,9,0,0,8290251 Union all
select 2942,654987,121201,40.40,0,2000057,0,0,0,9210033 Union all
select 2943,200070,1402,66.00,0,20002000058,51,0,0,8290251 Union all
select 2944,200070,0,525.00,0,20002000059,9,0,0,8290251 Union all
select 2945,200050,2203,275.20,0,20002000060,51,0,0,8290251 Union all
select 2946,5760001,0,249.80,0,20002000061,500,0,0,56100000 Union all
select 2948,200060,601,41.00,0,20002000063,51,0,0,8290251 Union all
select 2949,200060,602,58.50,0,20002000064,51,0,0,8290251 Union all
select 2950,300110,0,78.20,0,20002000065,145,0,0,8290251 Union all
select 2951,300080,0,199.00,0,20002000066,109,0,0,8290251 Union all
select 2952,105030,0,40.40,0,20002000067,703,0,0,8290251 Union all
select 2953,200060,603,26.10,0,20002000068,51,0,0,8290251 Union all
select 2954,200080,1592,32.50,0,20002000069,51,0,0,8290251 Union all
select 2955,200080,1591,100.00,0,20002000070,51,0,0,8290251 Union all
select 2956,400070,0,500.00,0,20002000071,209,0,0,8290251 Union all
select 2957,902565,0,500.00,0,20002000072,145,0,0,8290251 Union all
select 2958,400050,0,1000.00,0,20002000073,209,0,0,8290251 Union all
select 2959,95162847,35687,1000.00,0,2000074,0,0,0,44444444 Union all
select 2960,300050,0,120.00,0,20002000075,109,0,0,8290251 Union all
select 2961,200070,1400,116.60,0,20002000076,51,0,0,8290251 Union all
select 2962,105030,0,3.40,0,20002000077,703,0,0,8290251 Union all
select 2963,105010,0,75.00,0,20002000078,601,0,0,8290251 Union all
select 2964,300060,0,75.00,0,20002000079,145,0,0,8290251 Union all
select 2965,300070,0,40.00,0,20002000080,109,0,0,8290251 Union all
select 2966,1045001,0,40.00,0,20002000081,500,0,0,56100000 Union all
select 2967,348228,0,219.95,0,20002000082,373,0,0,8290251 Union all
select 2968,200070,1403,219.95,0,20002000083,51,0,0,8290251 Union all
select 2969,352810,0,197.20,0,20002000084,375,0,0,8290251 Union all
select 2970,200080,1593,197.20,0,20002000085,51,0,0,8290251 Union all
select 2974,200050,0,375.25,0,20002000006,9,0,0,8290251 Union all
select 2975,105030,0,30.30,0,20002000007,703,0,0,8290251 Union all
select 2976,200080,1595,111.30,0,20002000008,51,0,0,8290251 Union all
select 2977,200060,606,77.25,0,20002000009,51,0,0,8290251 Union all
select 2978,200070,1401,156.40,0,20002000010,51,0,0,8290251 Union all
select 2979,400080,0,2500.00,0,20002000011,209,0,0,8290251 Union all
select 2980,1234567,25161,2500.00,0,2000012,0,0,0,44444444 Union all
select 2981,400060,0,250.00,0,20002000013,209,0,0,8290251 Union all
select 2982,5760001,0,250.00,0,20002000014,500,0,0,8290251 Union all
select 2983,300080,0,200.00,0,20002000015,109,0,0,8290251 Union all
select 2984,56897412,7899,45.00,0,2000016,0,0,0,44444444 Union all
select 2985,57891532,1699,155.00,0,2000017,0,0,0,44444444 Union all
select 2986,300070,0,80.00,0,20002000018,109,0,0,8290251 Union all
select 2987,56897412,7898,80.00,0,2000019,0,0,0,44444444 Union all
select 2988,300060,0,100.00,0,20002000020,109,0,0,8290251 Union all
select 2989,105030,0,100.00,0,20002000021,703,0,0,8290251 Union all
select 2990,105010,0,25.00,0,20002000022,601,0,0,8290251 Union all
select 2991,300050,0,25.00,0,20002000023,145,0,0,8290251 Union all
select 2992,200070,0,310.30,0,20002000024,9,0,0,8290251 Union all
select 2993,105030,0,103.30,0,20002000025,703,0,0,8290251 Union all
select 2994,200060,600,54.10,0,20002000026,51,0,0,8290251 Union all
select 2995,200060,604,33.30,0,20002000027,51,0,0,8290251 Union all
select 2996,200050,2205,44.60,0,20002000028,51,0,0,8290251 Union all
select 2997,200080,1594,75.00,0,20002000029,51,0,0,8290251 Union all
select 2998,200050,0,225.00,0,20002000030,9,0,0,8290251 Union all
select 2999,105030,0,57.30,0,20002000031,703,0,0,8290251 Union all
select 3000,200070,1407,11.50,0,20002000032,51,0,0,8290251 Union all
select 3001,200080,1597,156.20,0,20002000033,51,0,0,8290251 Union all
select 3002,200080,0,633.20,0,20002000034,9,0,0,8290251 Union all
select 3003,105030,0,23.40,0,20002000035,703,0,0,8290251 Union all
select 3005,200070,1405,101.20,0,20002000037,51,0,0,8290251 Union all
select 3006,200050,2202,25.30,0,20002000038,51,0,0,8290251
--actual query starts here
;with startrangecte as (
select row_number() OVER (order by itemnum) RN,
itemnum
from items
where k1=200050 and k6=9),
Endrangecte as (
select row_number() OVER (order by itemnum) RN,
itemnum
from items
where k1!=200050 and k6=9)
select startrangecte.rn seq,
items.*
from startrangecte
inner join endrangeCTE on startrangecte.rn=endrangeCTE.rn
join items on items.itemnum>=startrangecte.itemnum and items.itemnum<endrangeCTE.itemnum
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 22, 2008 at 12:33 pm
Instead of manually creating the table import it using dts or ssis.
May 22, 2008 at 1:09 pm
Richter,
Stay away from the cursors/loops solutions and stick with the set based methods that Grant and Matt have posted. If you can, post back some feedback so we all know if you've been helped.
Another question I would ask is, what are you going to do with it once you have it in a temp table? You may not even need the temp table at all.
May 22, 2008 at 3:01 pm
I'll go one further, use Matt's solution. That looks good from where I sit.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 22, 2008 at 3:15 pm
Guys this is great, it works perfect in 2005, however it turns out I am going to need it to work in 2000, is there a simple conversion to make it work there?
May 22, 2008 at 4:29 pm
RICHTER (5/22/2008)
Guys this is great, it works perfect in 2005, however it turns out I am going to need it to work in 2000, is there a simple conversion to make it work there?
gets a lot less obvious - but it can be done. 2000 solution at the bottom:
--build some test data
drop table #ranges
drop table items
create table items(itemnum int ,K1 int,K2 int,K3 numeric(18,9),K4 int,K5 bigint,K6 int,K7 int,K8 int,K9 bigint)
insert items(itemnum,K1,K2,K3,K4,K5, K6,K7,K8,K9)
select 2930,200050,0,1001.50,0,20002000045,9,0,0,8290251 Union all
select 2931,105010,0,118.50,0,20002000046,601,0,0,8290251 Union all
select 2932,564857,601,600.00,0,2000047,0,0,0,9210033 Union all
select 2933,987654,868,355.00,0,2000048,0,0,0,44444444 Union all
select 2934,12597,869,165.00,0,20002000049,51,0,0,8290251 Union all
select 2935,10727,0,2399.60,0,20002000050,9,0,0,8290251 Union all
select 2936,105030,0,1369.23,0,20002000051,703,0,0,8290251 Union all
select 2937,200050,2200,67.60,0,20002000052,51,0,0,8290251 Union all
select 2938,852369,56601,962.77,0,2000053,0,0,0,9210070 Union all
select 2939,500050,0,121.30,0,20002000054,373,0,0,8290251 Union all
select 2940,200050,2201,121.30,0,20002000055,51,0,0,8290251 Union all
select 2941,200050,0,106.40,0,20002000056,9,0,0,8290251 Union all
select 2942,654987,121201,40.40,0,2000057,0,0,0,9210033 Union all
select 2943,200070,1402,66.00,0,20002000058,51,0,0,8290251 Union all
select 2944,200070,0,525.00,0,20002000059,9,0,0,8290251 Union all
select 2945,200050,2203,275.20,0,20002000060,51,0,0,8290251 Union all
select 2946,5760001,0,249.80,0,20002000061,8,0,0,56100000 Union all
select 2948,200060,601,41.00,0,20002000063,51,0,0,8290251 Union all
select 2949,200060,602,58.50,0,20002000064,51,0,0,8290251 Union all
select 2950,300110,0,78.20,0,20002000065,145,0,0,8290251 Union all
select 2951,300080,0,199.00,0,20002000066,109,0,0,8290251 Union all
select 2952,105030,0,40.40,0,20002000067,703,0,0,8290251 Union all
select 2953,200060,603,26.10,0,20002000068,51,0,0,8290251 Union all
select 2954,200080,1592,32.50,0,20002000069,51,0,0,8290251 Union all
select 2955,200080,1591,100.00,0,20002000070,51,0,0,8290251 Union all
select 2956,400070,0,500.00,0,20002000071,209,0,0,8290251 Union all
select 2957,902565,0,500.00,0,20002000072,145,0,0,8290251 Union all
select 2958,400050,0,1000.00,0,20002000073,209,0,0,8290251 Union all
select 2959,95162847,35687,1000.00,0,2000074,0,0,0,44444444 Union all
select 2960,300050,0,120.00,0,20002000075,109,0,0,8290251 Union all
select 2961,200070,1400,116.60,0,20002000076,51,0,0,8290251 Union all
select 2962,105030,0,3.40,0,20002000077,703,0,0,8290251 Union all
select 2963,105010,0,75.00,0,20002000078,601,0,0,8290251 Union all
select 2964,300060,0,75.00,0,20002000079,145,0,0,8290251 Union all
select 2965,300070,0,40.00,0,20002000080,109,0,0,8290251 Union all
select 2966,1045001,0,40.00,0,20002000081,500,0,0,56100000 Union all
select 2967,348228,0,219.95,0,20002000082,373,0,0,8290251 Union all
select 2968,200070,1403,219.95,0,20002000083,51,0,0,8290251 Union all
select 2969,352810,0,197.20,0,20002000084,375,0,0,8290251 Union all
select 2970,200080,1593,197.20,0,20002000085,51,0,0,8290251 Union all
select 2974,200050,0,375.25,0,20002000006,9,0,0,8290251 Union all
select 2975,105030,0,30.30,0,20002000007,703,0,0,8290251 Union all
select 2976,200080,1595,111.30,0,20002000008,51,0,0,8290251 Union all
select 2977,200060,606,77.25,0,20002000009,51,0,0,8290251 Union all
select 2978,200070,1401,156.40,0,20002000010,51,0,0,8290251 Union all
select 2979,400080,0,2500.00,0,20002000011,209,0,0,8290251 Union all
select 2980,1234567,25161,2500.00,0,2000012,0,0,0,44444444 Union all
select 2981,400060,0,250.00,0,20002000013,209,0,0,8290251 Union all
select 2982,5760001,0,250.00,0,20002000014,500,0,0,8290251 Union all
select 2983,300080,0,200.00,0,20002000015,109,0,0,8290251 Union all
select 2984,56897412,7899,45.00,0,2000016,0,0,0,44444444 Union all
select 2985,57891532,1699,155.00,0,2000017,0,0,0,44444444 Union all
select 2986,300070,0,80.00,0,20002000018,109,0,0,8290251 Union all
select 2987,56897412,7898,80.00,0,2000019,0,0,0,44444444 Union all
select 2988,300060,0,100.00,0,20002000020,109,0,0,8290251 Union all
select 2989,105030,0,100.00,0,20002000021,703,0,0,8290251 Union all
select 2990,105010,0,25.00,0,20002000022,601,0,0,8290251 Union all
select 2991,300050,0,25.00,0,20002000023,145,0,0,8290251 Union all
select 2992,200070,0,310.30,0,20002000024,9,0,0,8290251 Union all
select 2993,105030,0,103.30,0,20002000025,703,0,0,8290251 Union all
select 2994,200060,600,54.10,0,20002000026,51,0,0,8290251 Union all
select 2995,200060,604,33.30,0,20002000027,51,0,0,8290251 Union all
select 2996,200050,2205,44.60,0,20002000028,51,0,0,8290251 Union all
select 2997,200080,1594,75.00,0,20002000029,51,0,0,8290251 Union all
select 2998,200050,0,225.00,0,20002000030,9,0,0,8290251 Union all
select 2999,105030,0,57.30,0,20002000031,703,0,0,8290251 Union all
select 3000,200070,1407,11.50,0,20002000032,51,0,0,8290251 Union all
select 3001,200080,1597,156.20,0,20002000033,51,0,0,8290251 Union all
select 3002,200080,0,633.20,0,20002000034,9,0,0,8290251 Union all
select 3003,105030,0,23.40,0,20002000035,703,0,0,8290251 Union all
select 3005,200070,1405,101.20,0,20002000037,51,0,0,8290251 Union all
select 3006,200050,2202,25.30,0,20002000038,51,0,0,8290251
--actual query starts here
;with startrangecte as (
select row_number() OVER (order by itemnum) RN,
itemnum
from items
where k1=200050 and k6=9),
Endrangecte as (
select row_number() OVER (order by itemnum) RN,
itemnum
from items
where k1!=200050 and k6=9)
select startrangecte.rn seq,
items.*
from startrangecte
inner join endrangeCTE on startrangecte.rn=endrangeCTE.rn
join items on items.itemnum>=startrangecte.itemnum and items.itemnum<endrangeCTE.itemnum
--SQL Server 2000 edition
select Itemnum,
case when k1=200050 then 1 else 0 end start,
cast(0 as int) seq,
cast(0 as int) ignore
into #ranges
from items where k6=9
create unique clustered index uci_rng on #ranges(itemnum)
declare @prevrow int
set @prevrow =0;
declare @seq int
set @seq=0;
declare @dummyseq int
declare @dummy bit
update #ranges
set @seq=seq=case when start=1 then @seq+1
when start=0 then @seq
else null end,
@dummyseq=ignore= case when start=@prevrow then 1 else 0 end ,
@prevrow=start,
@dummyseq=@seq,
@dummy=@prevrow
from #ranges with (index(uci_rng),tablockx)
select items.*, rng.seq
from items
join (select seq,
min(itemnum) startnum,
max(itemnum) endnum
from #ranges
where ignore=0
group by seq) rng
on items.itemnum>=rng.startnum and items.itemnum<rng.endnum
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply