Help needed - 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

Help needed

Author

Topic

anil kumar
Starting Member

2 Posts

Posted - 2010-08-03 : 08:38:46

hello, everyone
Help needed in query
i have a table which have three field

tabId tabPrtId tabSlab
1 11 50
2 11 100
3 11 150
4 11 200

tabId Is table id it is key also
tabPrtId Is Party id in table
tabSlab Is slab value

now i want result like

tabPrtId slabFrom slabTo
11 50 100
11 100 150
11 150 200

means

for a particular party id i want to create slabs range -- from and to

thanks & Regards

Anil Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-03 : 08:43:56

Which version of SQL Server are you using?

N 56°04'39.26"
E 12°55'05.63"

anil kumar
Starting Member

2 Posts

Posted - 2010-08-03 : 09:33:39

Sir I am using ms sql server 2000

thanks & regards

AnilKumar

anil kumar

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-03 : 11:09:42

[code]declare @t table(tabId int,tabPrtId int,tabSlab int);
insert @t values(10, 11, 50)
insert @t values(20, 11, 100)
insert @t values(30, 11, 150)
insert @t values(40, 11, 200);

SELECT t1.tabPrtId, t1.tabSlab AS slabFrom,
(SELECT tabSlab
FROM @t
WHERE tabId = (SELECT MIN(tabId) FROM @t
WHERE tabId > t1.tabId
AND tabPrtId = 11)) slabTo
FROM @t t1
WHERE tabPrtId = 11
AND EXISTS
(SELECT *
FROM @t
WHERE tabId > t1.tabId
AND tabPrtId = 11);

/*
tabPrtId slabFrom slabTo
----------- ----------- -----------
11 50 100
11 100 150
11 150 200
*/[/code]

Sachin.Nand

2937 Posts

Posted - 2010-08-03 : 12:20:49

[code]
declare @t table(tabId int,tabPrtId int,tabSlab int);
insert @t values(1, 11, 50)
insert @t values(2, 11, 100)
insert @t values(3, 11, 150)
insert @t values(4, 11, 200);

select * from @t

select
t1.tabPrtId,
t2.tabSlab as SlabFrom,
t1.tabSlab as SlabTo
from @t t1 inner join @t t2 on t1.tabId=t2.tabId +1 and t1.tabPrtId=t2.tabPrtId
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-03 : 19:57:29

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

CREATE TABLE Tabs -- whatever a Tab is
(tab_id INTEGER NOT NULL PRIMARY,
tab_prt_id INTEGER NOT NULL,
tab_slab INTEGER NOT NULL);

SELECT T1.tab_prt_id,
T1.tab_slab AS source_tab_slab, T2.tab_slab AS dest_tab_slab
FROM Tabs AS T1,
LEFT OUTER JOIN
Tabs AS T2
ON T1.tab_prt_id = T2.tab_prt_id
AND T1.tab_id +1 = T2.tab_id;

This assumes tab_id is a sequence.

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources