query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-12 : 01:46:00

Dear experts,
I've a long query, which is returning from_no and to_no as well as some other columns....

the result is like this

1001.0000 1500
3001.0000 3500
1.0000 01000
1.0000 1000
1.0000 1000
1.0000 1000
1.0000 1000
1.0000 1000
1.0000 1000
1001.0000 02000
1001.0000 02000
1001.0000 2000
1001.0000 2000
1001.0000 2000
1001.0000 2000
1001.0000 2000
2001.0000 3000
2001.0000 3000
2501.0000 03500
3001.0000 4000
3501.0000 06500
3501.0000 4500
4001.0000 05000
4001.0000 5000
5001.0000 6000
5001.0000 6000
6701.0000 07700
7001.0000 8000
7501.0000 008500
7501.0000 08500
8001.0000 09000
8501.0000 09500
10501.0000 11500
12501.0000 13500
13501.0000 14500
14001.0000 15000
24501.0000 25500
34501.0000 35500
104001.0000 105000
135001.0000 136000
155501.0000 156500
157001.0000 158000
3751.0000 4800
1001.0000 2500
3501.0000 5000
5001.0000 06500
6001.0000 7500
13001.0000 14500
1.0000 2000
1.0000 2000
1.0000 2000
1.0000 2000
1501.0000 3500
2001.0000 04000
2501.0000 4500
4001.0000 6000
4501.0000 6500
6001.0000 8000
7501.0000 9500
8001.0000 10000
14501.0000 16500
14501.0000 16500
37501.0000 39500
73001.0000 75000
73001.0000 75000
73001.0000 75000
1.0000 2500
1001.0000 03500
1001.0000 03500
2001.0000 4500
3501.0000 06000
7701.0000 10200
8501.0000 11000
12001.0000 14500
35001.0000 37500
35501.0000 38000
56501.0000 59000
60001.0000 62500
79501.0000 82000
87501.0000 90000
138801.0000 141300
1.0000 01000
393001.0000 396000
72751.0000 76200
32501.0000 36000
24001.0000 28000
43001.0000 47000
146301.0000 150300
1.0000 5000
2501.0000 07500
2501.0000 7500
3501.0000 08500
6001.0000 11000
11501.0000 16500
14001.0000 19000
27001.0000 32000
27501.0000 32500
32501.0000 37500
32501.0000 37500
38001.0000 43000
55501.0000 60500
56501.0000 61500
62501.0000 67500
76201.0000 81200
76501.0000 81500
81201.0000 86200
90001.0000 95000
95001.0000 100000
99501.0000 104500
136001.0000 141000
141301.0000 146300
217501.0000 222500
222501.0000 227500
245801.0000 250800
251501.0000 256500
377001.0000 382000
377001.0000 382000
388001.0000 393000
423501.0000 428500
483501.0000 488500
561001.0000 566000
662501.0000 667500
991001.0000 996000
608001.0000 614000
57201.0000 64700
641001.0000 648500
913301.0000 920800
143601.0000 151100
165001.0000 173500
165001.0000 173500
1.0000 6100
23001.0000 33000
23001.0000 33000
23001.0000 33000
23001.0000 33000
26001.0000 36000
26001.0000 36000
37501.0000 47500
37501.0000 47500
45501.0000 55500
64701.0000 74700
75001.0000 85000
75001.0000 85000
76001.0000 86000
81501.0000 91500
108601.0000 118600
253601.0000 263600
320013.0000 330012
320501.0000 330500
386001.0000 396000
614001.0000 624000
996001.0000 999900
10001.0000 20000
120001.0000 130000
120001.0000 130000
20001.0000 32500
263601.0000 276100
49001.0000 64000
64001.0000 79000
283001.0000 298000
283001.0000 298000
413001.0000 428000
830001.0000 845000
173501.0000 191000
173501.0000 191000
5001.0000 25000
12001.0000 32000
17001.0000 37000
17001.0000 37000
61001.0000 81000
184001.0000 204000
184001.0000 204000
188001.0000 208000
188001.0000 208000
231001.0000 251000
231001.0000 251000
285601.0000 305600
285601.0000 305600
285601.0000 305600
25001.0000 50000
25001.0000 50000
25001.0000 50000
25001.0000 50000
25001.0000 50000
25001.0000 50000
118601.0000 143600
151101.0000 176100
151101.0000 176100
1.0000 30000
1.0000 30000
966501.0000 999900
966501.0000 999900
1.0000 10000
1.0000 10000
584501.0000 619500
382001.0000 422000
382001.0000 422000
488501.0000 528500
566001.0000 606000
898001.0000 938000
898001.0000 938000
20001.0000 66600
20001.0000 66600
60001.0000 110000
428001.0000 478000
444001.0000 494000
605501.0000 645000
675001.0000 685500
783401.0000 833400
783401.0000 833400
783401.0000 833400
176101.0000 226100
916501.0000 966500
916501.0000 966500
916501.0000 966500
619501.0000 689500
845001.0000 915000
298001.0000 378000
298001.0000 378000
291501.0000 391500
291501.0000 391500
966501.0000 999900
1.0000 66600
752601.0000 999900
1.0000 120000
130001.0000 152700
752601.0000 999900
1.0000 152700

now my problem is.........

if the difference between the from_no and to_no is greater than 10,000 then for every 10,000 the record should be splitted into another record...

suppose one record is 2,000 to 22,000 then
ex: 2000-12000,
12001-22000

it should split the record like that....

thank you in advance....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:52:54

[code]
declare @table table
(
from_no int,
to_no int
)
insert into @table
select 2000, 22000 union all
select 3000, 23001 union all
select 3001, 3500

select t.from_no,
t.to_no,
new_from = from_no + ((n * 10000) + 1),
new_to = case when n < no_rows - 1
then from_no + ((n + 1) * 10000)
else to_no
end
from (
select from_no, to_no,
diff = to_no - from_no,
no_rows = (to_no - from_no) / 10000 + case when (to_no - from_no) % 10000 <> 0 then 1 else 0 end
from @table
) t
left join
(
select n = 0 union all
select n = 1 union all
select n = 2 union all
select n = 3 union all
select n = 4
) n
on n < no_rows
[/code]

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-12 : 03:01:33

Thank you very much for the query KH,
actually i've taken the values as an example.....
but at runtime,i've to take the values from that table....
and those both are varchar values...
please consider me and tell me the query for the runtime values

thank you very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 03:06:06

quote:
but at runtime,i've to take the values from that table...

Just replace the @table with your query. Or insert the result of your query into a temp table or table variable

quote:
those both are varchar values...

well, then you have to convert it to numeric. use convert()

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-12 : 04:09:50

Dear KH,
after replacing my query in the place of @table,I'm getting error.

my query is like this.

SELECT DISTINCT VIEWC.Org_Id Company_Id,VIEWC.Name Company_Name,VIEWB.Org_Id Branch_Id,
VIEWB.Name Branch_Name,SALOD.COLUMN004 SO_Number,SALOD.COLUMN040 PO_No,SALOD.COLUMN041 PO_DATE ,
PROCG.COLUMN004 Job_Description,SALIT.COLUMN008 Totalnoof_Leaves,--DNDTL.COLUMN003EE Totalnoof_Books,
SALPF.COLUMN001EE Serial_No, SALPF.COLUMN002EE From_No,SALPF.COLUMN003EE To_No,VIEW2.CODE Item_Code,
VIEW2.NAME Item_Name,SALOD.COLUMN001 SO_Id,
vcust1.column001 Customer_ID, VIEW1.column003 CUSTOMER_NAME,vcust1.column003 CUSTOMER_NAME1
FROM QATABLE049 SALOD
INNER JOIN QATABLE052 SALIT ON SALIT.COLUMN002=SALOD.COLUMN001
INNER JOIN VITEM VIEW2 ON VIEW2.ITEM_ID=SALIT.COLUMN004
INNER JOIN QATABLE038 VIEW1 ON VIEW1.COLUMN001=SALOD.COLUMN008
LEFT JOIN YBTABLE079 PMSCI ON PMSCI.COLUMNB01=SALOD.COLUMN001
LEFT JOIN YBTABLE080 PMSCH ON PMSCH.COLUMN002=PMSCI.COLUMN001
LEFT JOIN YBTABLE052 PROCG ON PROCG.COLUMN007=PMSCH.COLUMN001
--INNER JOIN MATABLE108 DNOTE ON DNOTE.COLUMNB01=SALOD.COLUMN001
--INNER JOIN MATABLE110 DNDTL ON DNDTL.COLUMN002=DNOTE.COLUMN001
INNER JOIN QATABLE143 SALPF ON SALPF.COLUMN004=SALOD.COLUMN001 AND SALPF.COLUMN005=SALIT.COLUMN004
INNER join QATABLE038 vcust1 on vcust1.COLUMNB01=SALPF.COLUMNB03
INNER JOIN vOrganization VIEWB ON VIEWB.ORG_ID = SALOD.COLUMN002
INNER JOIN vOrganization VIEWC ON VIEWC.Org_Id = VIEWB.Top_Parent
where SALOD.COLUMN001='ab375a93-a0cb-494d-9601-e86838edfa4a' and vcust1.column001='01841c25-fc69-40b6-a55a-c547b40d8fd9'

please guide me

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 04:15:14

try the temp table approach


create table #temp (from_no int, to_no int)
insert into #temp (from_no, to_no)
select <from column>, <to column>
from
(
     <your query here>
) t

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-12 : 04:46:46

Dear KH,
now i'm getting the from_no,to_no only....
is it not possible to get the result in the desierd result....

means....to check the difference between the to numbers, and if it is greater than 10000, it has to take first 10000 , then second 10000....like that

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 05:03:52

didn't my query did that ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources