Table Valued Function

Question

Table Valued Function

forrest77

SSC Veteran

Points: 241
More actions
April 24, 2015 at 12:16 pm

#307341

Hi, I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique. Open to other suggestions. Thanks
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

Viewing 15 posts - 1 through 15 (of 16 total)

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

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

forrest77 (4/24/2015)
Hi, I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique. Open to other suggestions. Thanks
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

DO NOT use a cursor for selecting data. A table valued function with a cursor inside is about the worst possible construct you can come up with for performance.

To get unique rows all you need is DISTINCT.

_______________________________________________________________

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/

forrest77 SSC Veteran Points: 241 More actions · Answer 2

Hi, I tried the distinct and it did not work. the issue I'm having is when I join on this table to create a report, I get multiple records as opposed to one record.

Lowell SSC Guru Points: 323504 More actions · Answer 3

forrest77 (4/24/2015)
Hi, I tried the distinct and it did not work. the issue I'm having is when I join on this table to create a report, I get multiple records as opposed to one record.

show your query, and explain what you mean by multiple records;

your sample data from your first post has no duplicates, for example.

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!

forrest77 SSC Veteran Points: 241 More actions · Answer 4

SELECT Empid,

STUFF (

(

SELECT ',' + CAST(chDate AS VARCHAR(30))

FROM tableFunc SubQuery

WHERE SubQuery.Empid = A.Empid

FOR XML PATH('')

),

1,1,'') AS ChDateList,

[Site],

userID,

initsal,

finsal,

note

FROM tableFunc A

GROUP BY Empid,[Site],userID,initsal,finsal,note

Yes, there are no dupplicates and the above query confirm that. the finsal attribue has the same value. In a report, how can I tell sql to return only the most recent record (chDate).

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

forrest77 (4/24/2015)
SELECT Empid,
STUFF (
(
SELECT ',' + CAST(chDate AS VARCHAR(30))
FROM tableFunc SubQuery
WHERE SubQuery.Empid = A.Empid
FOR XML PATH('')
),
1,1,'') AS ChDateList,
[Site],
userID,
initsal,
finsal,
note
FROM tableFunc A
GROUP BY Empid,[Site],userID,initsal,finsal,note
Yes, there are no dupplicates and the above query confirm that. the finsal attribue has the same value. In a report, how can I tell sql to return only the most recent record (chDate).

Whenever you want only the most recent row you would use a select top 1 and Order by YourDateColumn

_______________________________________________________________

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I think you are making this harder than it actually is. Could you post the DDL (CREATE TABLE statement) for the tables involved, some SAMPLE data for both tables using INSERT INTO statements, and the expected results based on the sample data you provide.

forrest77 SSC Veteran Points: 241 More actions · Answer 7

CREATE FUNCTION dbo.ADMC_DDL_GetUniqueEditEdit()

RETURNS @EditLog TABLE(

Id INT NOT NULL

,EditDate DATETIME NOT NULL

,PlanId INT NOT NULL

,TransId INT NOT NULL

,EntityId INT NOT NULL

,QtyPrior FLOAT NULL

,QtyAfter FLOAT NOT NULL

,Description VARCHAR(255) NULL

,NotText NVARCHAR(max) NULL

)

AS

BEGIN

INSERT INTO @EditLog

(Id

,EditDate

,PlanId

,TransId

,EntityId

,QtyPrior

,QtyAfter

,Description

,NotText)

SELECT EL.Id

,MAX(EL.EditDate)

,MAX(EL.PlanId)

,MAX(EL.TransId)

,MAX(EL.EntityId)

,MAX(EL.QtyPrior)

,MAX(EL.QtyAfter)

,MAX(EL.Description)

,MAX(EL.NoteText)

FROM dbo.EditLog AS EL

GROUP BY EL.Id

RETURN;

END

GO

forrest77 SSC Veteran Points: 241 More actions · Answer 8

here is some some records. all are same records but the edit time are different. I would like to have a cursor or something else that can through the table entirely and return the most recent edits.

IdEditDatePlanIdTransIdEntityIdQtyPriorQtyAfterDescriptionNoteText

837652015-04-23 19:42:19.51715206353236810.6578947368421050.789473684210526EDITchange from 18 to 25

837662015-04-23 19:42:19.64315206353250810.6578947368421050.789473684210526EDITchange from 18 to 25

837672015-04-23 19:42:19.74015206353209810.6578947368421050.789473684210526EDITchange from 18 to 25

837682015-04-23 19:42:19.88315206353218310.6578947368421050.789473684210526EDITchange from 18 to 25

837692015-04-23 19:42:20.00015206353191410.6578947368421050.789473684210526EDITchange from 18 to 25

Lowell SSC Guru Points: 323504 More actions · Answer 9

most recent edit no matter what(so one row, period) or most recent by EntityId?

your current logic groups by GROUP BY EL.Id, which is probably the Identity() column of the table, so it effectively doesn't group anything at all.

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!

forrest77 SSC Veteran Points: 241 More actions · Answer 10

Thanks Lowell, please advise what will be the best field(s) to group by.

forrest77 SSC Veteran Points: 241 More actions · Answer 11

Thank Lowell, please advise what will be the best field(s) to group by.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

forrest77 (4/24/2015)
CREATE FUNCTION dbo.ADMC_DDL_GetUniqueEditEdit()
RETURNS @EditLog TABLE(
Id INT NOT NULL
,EditDate DATETIME NOT NULL
,PlanId INT NOT NULL
,TransId INT NOT NULL
,EntityId INT NOT NULL
,QtyPrior FLOAT NULL
,QtyAfter FLOAT NOT NULL
,Description VARCHAR(255) NULL
,NotText NVARCHAR(max) NULL
)
AS
BEGIN
INSERT INTO @EditLog
(Id
,EditDate
,PlanId
,TransId
,EntityId
,QtyPrior
,QtyAfter
,Description
,NotText)
SELECT EL.Id
,MAX(EL.EditDate)
,MAX(EL.PlanId)
,MAX(EL.TransId)
,MAX(EL.EntityId)
,MAX(EL.QtyPrior)
,MAX(EL.QtyAfter)
,MAX(EL.Description)
,MAX(EL.NoteText)
FROM dbo.EditLog AS EL
GROUP BY EL.Id
RETURN;
END
GO

Well, this is the function you use, but it isn't the table (or tables) from which the data is pulled.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

forrest77 (4/24/2015)
here is some some records. all are same records but the edit time are different. I would like to have a cursor or something else that can through the table entirely and return the most recent edits.
IdEditDatePlanIdTransIdEntityIdQtyPriorQtyAfterDescriptionNoteText
837652015-04-23 19:42:19.51715206353236810.6578947368421050.789473684210526EDITchange from 18 to 25
837662015-04-23 19:42:19.64315206353250810.6578947368421050.789473684210526EDITchange from 18 to 25
837672015-04-23 19:42:19.74015206353209810.6578947368421050.789473684210526EDITchange from 18 to 25
837682015-04-23 19:42:19.88315206353218310.6578947368421050.789473684210526EDITchange from 18 to 25
837692015-04-23 19:42:20.00015206353191410.6578947368421050.789473684210526EDITchange from 18 to 25

And this doesn't look like sample data in the form of INSERT INTO statements that could be cut and pasted into SSMS to populate tables, of which there has been no DDL posted to do the same.

Please read the first article I reference below in my signature block regarding asking for help. It will walk you through the what and how of what you should post to get the best possible answers to you question plus get tested code in return.

Also, don't forget to post the expected results based on the sample data you provide.

forrest77 SSC Veteran Points: 241 More actions · Answer 14

Lynn, this is the table.

CREATE TABLE [dbo].[EditLog](

[Id] [INT] IDENTITY(1,1) NOT NULL,

[EditDate] [DATETIME] NOT NULL,

[PlanId] [INT] NOT NULL,

[TransId] [INT] NOT NULL,

[EntityId] [INT] NOT NULL,

[QtyPrior] [FLOAT] NULL,

[QtyAfter] [FLOAT] NOT NULL,

[Description] [VARCHAR](255) NULL,

[NoteText] [NVARCHAR](MAX) NULL,

CONSTRAINT [PK_EditLog] PRIMARY KEY NONCLUSTERED

Here is some sample data