Need to create a Scalar Valued Function

Question

Post reply

Need to create a Scalar Valued Function

sql1411

Ten Centuries

Points: 1390
More actions
February 28, 2013 at 11:05 am

#394282

Hi ,
I need to write a Scalar-valued function ( I guess) to get the highest status for each member on a given day if there are more than one status entered for that particular member on that particular day.
Below is the very simple scenario:
-- 1. Create Agent Status History
GO
CREATE TABLE [dbo].[AgentStatusHistory](
[AgentID] [int] NOT NULL,
[QualifyDate] [datetime] NULL,
[Status] [varchar] (10)
) ON [PRIMARY]
GO
-- 2. INSERT some data into AgentStatusHistory table
GO
INSERT INTO dbo.AgentStatusHistory (AgentID, QualifyDate, Status)
SELECT 101, '2010-02-01 00:00:00', 'LOW'
UNION ALL
SELECT 101, '2011-05-01 00:00:00', 'MEDIUM'
UNION ALL
SELECT 101, '2011-05-01 00:00:00', 'HIGH'
UNION ALL
SELECT 102, '2012-22-25 00:00:00', 'LOW'
UNION ALL
SELECT 102, '2012-22-25 00:00:00', 'MEDIUM'
UNION ALL
SELECT 102, '2012-22-25 00:00:00', 'HIGH'
GO
From the above example, if I execute the below SQL query..
SELECT * FROM dbo.AgentStatusHistory
ORDER BY AgentID, QualifyDate;
here is what I need to see in the result set…
•AgentID, QualifyDate, Status
•101, '2010-05-01 00:00:00', 'HIGH'
•101, '201-02-01 00:00:00', 'LOW'
•102, '2012-22-25 00:00:00', 'HIGH'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

Great job on supplying the DDL and Sample data;

probably a copy paste error, but two of the dates have the month "22", so i couldn't get it loaded all teh way.

offhand, i'd think that this will get you what you are asking, without using a function:

SELECT * FROM (

SELECT ROW_NUMBER() OVER(PARTITION BY AgentID,QualifyDate

ORDER BY CASE

WHEN Status='LOW'

THEN 1

WHEN Status='MEDIUM'

THEN 2

WHEN Status='HIGH'

THEN 3

ELSE 0

END DESC ) AS RW,

* FROM AgentStatusHistory) MyAlias WHERE RW=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql1411 Ten Centuries Points: 1390 More actions · Answer 2

Hi Lowell,

Thanks for your response. This is what I needed. However, is it possible to embed this logic to create a scalar valued function so that I can use these AgentIDs for lot of other different purposes??

Thanks again.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

sql1411 (2/28/2013)
Hi Lowell,
Thanks for your response. This is what I needed. However, is it possible to embed this logic to create a scalar valued function so that I can use these AgentIDs for lot of other different purposes??
Thanks again.

You think you want a scalar function but I think you will find the performance is far from acceptable. Given the simplicity of this you should instead create it as a view, or an iTVF (inline table valued function) if it needs parameters. The performance will be far superior and it is easier to use.

_______________________________________________________________

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/

sql1411 Ten Centuries Points: 1390 More actions · Answer 4

Thanks for the suggestion. I never thought about that 🙂