July 5, 2012 at 11:36 am
I HAVE A TABLE LIKE
TABLE TBL_INTRATE
SCHEMEID INSTLNO ROI
1 1 20
1 4 22
1 8 25
1 10 26
1 12 28
THERE IS A PROC THAT TAKES INSLTLNO AS INPUT AND ON THE BASIS OF
VALUE OF INSTL NO AND TABLE TBL_INTRATE I NEED TO DRAW A TEMP TABLE
LIKE
TABLE @VTBL_INTRATE
SCHEMEID INSTLNO INSTL_TO INTFROM ROI
1 1 1 3 20
1 4 4 7 22
1 8 8 9 25
1 10 10 11 26
1 12 12 15 28
IN THIS CASE THE VALUE OF INTL NO IS 15. THE LAST ROW OF TABLE TBL_INTRATE
SHOWS ROI FOR 12 TILL LAST DEPENDS THE VALUES IS ENTERD.
PLS HELP..
July 5, 2012 at 11:48 am
Please post the ddl, and the data insertion script.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 11:54 am
CREATE TABLE TBL_INTRATE
( SCHEMEID NUMERIC(10),
INSTLNO NUMERIC(10)
ROI NUMERIC (5)
)
INSERT INTO TBL_INTRATE (SCHEMEID ,INSTLNO , ROI )
VALUES (1, 1, 20)
GO
INSERT INTO TBL_INTRATE (SCHEMEID ,INSTLNO , ROI )
VALUES (1, 4, 22)
GO
INSERT INTO TBL_INTRATE (SCHEMEID ,INSTLNO , ROI )
VALUES (1, 8, 25)
GO
INSERT INTO TBL_INTRATE (SCHEMEID ,INSTLNO , ROI )
VALUES (1, 10, 26)
DECLARE @VTBL_INTRATE TABLE (
SCHEMEID NUMERIC(10) ,
INSTLNOFROM NUMERIC(10) ,
INTRATE NUMERIC(10,2) ,
V_FROM_INST NUMERIC(10),
V_TO_INST NUMERIC(10)
)
July 5, 2012 at 11:58 am
Something like thiss?
DECLARE @InstlNo INT = 15
DECLARE @TBL_INTRATE TABLE
(
SCHEMEID INT
,INSTLNO INT
,ROI INT
)
INSERT INTO @TBL_INTRATE
SELECT 1, 1, 20
UNION ALL SELECT 1, 4, 22
UNION ALL SELECT 1, 8, 25
UNION ALL SELECT 1, 10, 26
UNION ALL SELECT 1, 12, 28
; WITH CTE AS
(
SELECT TI.SCHEMEID , TI.INSTLNO , TI.ROI
,RN = ROW_NUMBER() OVER (PARTITION BY TI.SCHEMEID ORDER BY TI.INSTLNO)
FROM @TBL_INTRATE TI
)
SELECT OtrTbl.SCHEMEID
,OtrTbl.INSTLNO
,V_FROM_INST = OtrTbl.INSTLNO
,V_TO_INST = ISNULL ( (CrsApp.INSTLNO - 1) , @InstlNo)
,OtrTbl.ROI
FROM CTE OtrTbl
OUTER APPLY ( SELECT *
FROM CTE InrTbl
WHERE ( OtrTbl.SCHEMEID = InrTbl.SCHEMEID
AND OtrTbl.RN + 1 = InrTbl.RN)
) CrsApp
July 5, 2012 at 12:00 pm
i am begginer and knowlede of cross apply , if it can be done by cte it is more helpfuly for me to understand and modify as per my need . pls help
July 5, 2012 at 12:01 pm
drop table tbl_intrate
go
create table tbl_intrate(schm_id int,instno int,roi float)
go
insert into tbl_intrate
values
(1, 1, 20),
(1, 4, 22),
(1, 8, 25),
(1, 10, 26),
(1, 12, 28 )
go
select * from tbl_intrate
go
drop proc myproc
go
create proc myproc
(
@instno int
)
as
begin
select ti.schm_id,ti.instno instno_from,isnull(tica.instno_to,@instno) instno_to,ti.roi
from tbl_intrate ti
cross apply ( select MIN(instno) -1 as instno_to from tbl_intrate tica
where ti.schm_id= tica.schm_id
and ti.instno < tica.instno
and tica.instno <= @instno
) as tica
where ti.instno <= @instno
end
go
exec myproc @instno = 15
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 12:07 pm
pnpsql (7/5/2012)
i am begginer and knowlede of cross apply , if it can be done by cte it is more helpfuly for me to understand and modify as per my need . pls help
Paul White wrote a set of two great articles about using and understanding APPLY. Take look at them here.
Understanding and Using APPLY (Part 1) [/url]
Understanding and Using APPLY (Part 2) [/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply