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

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 11:28:19

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..

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 11:52:23

[code]
[code]
DECLARE @INTL_NO int

SET @INTL_NO = 15

SELECT t.SCHEMEID,
t.INSTLNO,
t.INSTLNO AS INSTL_TO,
COALESCE(t1.INSTLNO,@INTL_NO) AS INTFROM,
t.ROI
FROM TBL_INTRATE t
OUTER APPLY (SELECT TOP 1 INSTLNO
FROM TBL_INTRATE
WHERE SCHEMEID = t.SCHEMEID
AND ROI > t.ROI
ORDER BY ROI )t1
WHERE t.INSTLNO < @INTL_NO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 12:56:20

can it be more simpler

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 13:02:30

what do you mean by that? whats the complexity in current suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 13:23:44

i amd begginer nad dont'have 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

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 13:35:18

the output is not as per requirement

i need

SCHEMEID INSTLNO ROI
1 1 20
1 4 22
1 8 25
1 10 26
1 12 28

to

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

and query returns

SCHEMEID INSTLNO INSTL_TO INTFROM ROI
1 1 1 4 20
1 4 4 8 22
1 8 8 10 25
1 10 10 12 26
1 12 12 15 28

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 13:53:21

Make a slight change to Visakh's query like this:

DECLARE @INTL_NO int

SET @INTL_NO = 15

SELECT t.SCHEMEID,
t.INSTLNO,
t.INSTLNO AS INSTL_TO,
COALESCE(t1.INSTLNO-MIN(t.INSTLNO) OVER (PARTITION BY t.SCHEMEID),@INTL_NO) AS INTFROM,
t.ROI  
FROM TBL_INTRATE
OUTER APPLY (SELECT TOP 1 INSTLNO
             FROM TBL_INTRATE
             WHERE SCHEMEID = t.SCHEMEID 
             AND ROI > t.ROI 
             ORDER BY ROI )t1
WHERE t.INSTLNO < @INTL_NO

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 13:57:19

YES THIS IS PERFECT BUT CAN IT BE IN CTE ..

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 14:04:15

quote:
Originally posted by pnpsql

YES THIS IS PERFECT BUT CAN IT BE IN CTE ..

challenge everything

I suppose you could, for example like shown below. But I suspect Visakh's query is more efficient and perhaps easier to understand. In this context, you can think of the OUTER APPLY as a LEFT JOIN. So unless there is a compelling reason not to use the OUTER APPLY, intuitively my preference would be the OUTER APPLY.

DECLARE @INTL_NO int

SET @INTL_NO = 15

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY SCHEMEID ORDER BY INSTLNO) AS RN
	FROM TBL_INTRATE
)
SELECT
	t.SCHEMEID,
	t.INSTLNO,
	t.INSTLNO AS INSTL_TO,
	COALESCE(t1.INSTLNO-MIN(t.INSTLNO) OVER (PARTITION BY t.SCHEMEID),@INTL_NO) AS INTFROM,
	t.ROI
FROM
	cte t
	LEFT JOIN cte t1 ON t1.RN = t.RN+1

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 23:52:30

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-05 : 23:59:07

thanks it works , but if in case the first instlno is not start with 1 like if it start with 3 , i need to consider it as 1.

TABLE TBL_INTRATE

SCHEMEID INSTLNO ROI
1 2 20
1 4 22
1 8 25
1 10 26
1 12 28

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 06:40:22

Replace the MIN function that I added to Visakh's query with a 1.

...
	COALESCE(t1.INSTLNO-MIN(t.INSTLNO) OVER (PARTITION BY t.SCHEMEID),@INTL_NO) AS INTFROM,
...

to this:

...
	COALESCE(t1.INSTLNO-1,@INTL_NO) AS INTFROM,
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources