sql where exists clause - 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

sql where exists clause

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-03-31 : 22:34:14

group id is passed as in parameter
I am getting the required output from both the query, but it does full table scan I want to write using EXIST which will be better performance wise, can some one help me in doing so?

For a particular group we've list of enrl_Premium_amt, if any of the enrl_Premium_amt is >0 then I want to return 'Y' else 'N'


ALTER FUNCTION udf_PastEnroll (@Group_id int) 
RETURNS TABLE
AS RETURN(
SELECT TOP 1 CASE WHEN enrl_Premium_amt > 0 THEN 'Y' ELSE 'N' END AS Indicator
FROM dbo.Enrollment E WHERE E.Group_id= @Group_id
        ORDER BY enrl_Premium_amt DESC
--OR
SELECT CASE WHEN sum(enrl_Premium_amt)>0 THEN 'Y' ELSE 'N' END AS Indicator
FROM dbo.Enrollment E WHERE E.Group_id= @Group_id
	GROUP BY E.group_id

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-03-31 : 23:34:25

How to change this to using exists clause to avoid full table scan

SELECT CASE WHEN sum(enrl_Premium_amt)>0 THEN 'Y' ELSE 'N' END AS Indicator
FROM dbo.Enrollment E WHERE E.Group_id= @Group_id
GROUP BY E.group_id

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-03-31 : 23:54:41

is there anything missing in above question, am i confusing?

-Neil

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-01 : 01:08:46

Please post the DDL of your table (Including Indexes, and constraints).
The question depends on the table schema.
And then, It seems that you need index on Group_id and include enrl_Premium_amt.

-------------------------------------
From Japan
Sorry, my English ability is limited.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-04-01 : 05:55:49

CREATE TABLE [dbo].[Enrollment](
[Enroll_id] [int] NOT NULL,
[Group_id] [int] NOT NULL,
[Mem_id] [int] NOT NULL,
[Car_id] [int] NOT NULL,
[enrl_Premium_amt] [decimal](12, 2) NOT NULL,
CONSTRAINT [idx_01] PRIMARY KEY CLUSTERED
(
[Enroll_id] ASC,
[Group_id] ASC
)

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-04-01 : 09:40:50

is any more info needed am i missing something

-Neil

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-01 : 10:31:59

aakcase, try below...


;WITH CTE AS
(
SELECT @Group_id,SUM(Enrl_premium_amt) AS enrl_premium_amt
FROM dbo.Enrollment
WHERE E.Group_id = @Group_id
)
SELECT CASE WHEN enrl_premium_amt >0 THEN 'Y' ELSE 'N'
FROM CTE

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-04-01 : 10:55:17

Thanks this is used in tabled valued function to return Y or N, in the above case it is will do a full scan

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources