August 3, 2010 at 6:45 am
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
August 4, 2010 at 3:21 am
select
t1.tabPrtId
, t1.tabSlab
, t2.tabSlab
from
@t t1
left join @t t2 on t2.tabid = t1.tabid + 1 and t2.tabPrtId = t1.tabPrtId
or
select
t1.tabPrtId
, t1.tabSlab
, min(t2.tabSlab)
from
@t t1
left join @t t2 on t2.tabid > t1.tabid and t2.tabPrtId = t1.tabPrtId
group by
t1.tabPrtId
, t1.tabSlab
I Have Nine Lives You Have One Only
THINK!
August 5, 2010 at 1:11 am
create table #temp1 (tabId int primary key, tabPrtId int , tabSlab int)
insert into #temp1 values(1 ,11 ,50)
insert into #temp1 values(2 ,11 ,100)
insert into #temp1 values(3 ,11 ,150)
insert into #temp1 values(4 ,11 ,200)
select t1.tabPrtId, t1.tabSlab , t2.tabSlab
from #temp1 t1 left join #temp1 t2
on t2.tabid = t1.tabid + 1
and t2.tabPrtId = t1.tabPrtId
where t2.tabSlab is not null
August 6, 2010 at 6:38 am
Watch out for gaps in tabId! Hence the use of the Row_number function.
Whoops just realised this is a SQL Server 7,2000 post. This script will only work on 2005 up.
WITH cte (tabId, tabPrtId, tabSlab)
AS (SELECT 1, 11, 50
UNION ALL SELECT 3, 11, 100
UNION ALL SELECT 7, 11, 150
UNION ALL SELECT 28, 11, 200
UNION ALL SELECT 30, 15, 70
UNION ALL SELECT 37, 15, 230
UNION ALL SELECT 58, 15, 420),
cte2 (row, tabprtid, tabslab)
AS (SELECT Row_number() OVER (ORDER BY tabid),
tabprtid,
tabslab
FROM cte)
SELECT x.tabPrtId,
x.tabSlab AS slabFrom,
z.tabSlab AS slabTo
FROM cte2 AS x
OUTER APPLY (SELECT row,
tabPrtId,
tabSlab
FROM cte2
WHERE row = x.row + 1
AND tabPrtId = x.tabPrtId) AS z
WHERE NOT z.tabSlab IS NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply