April 24, 2015 at 12:16 pm
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
April 24, 2015 at 12:21 pm
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. ThanksEmpidChDateSiteuseridinitsal 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/
April 24, 2015 at 12:33 pm
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.
April 24, 2015 at 12:42 pm
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
April 24, 2015 at 12:53 pm
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).
April 24, 2015 at 12:59 pm
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/
April 24, 2015 at 1:28 pm
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.
April 24, 2015 at 2:29 pm
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
April 24, 2015 at 2:39 pm
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
April 24, 2015 at 2:46 pm
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
April 24, 2015 at 2:53 pm
Thanks Lowell, please advise what will be the best field(s) to group by.
April 24, 2015 at 2:54 pm
Thank Lowell, please advise what will be the best field(s) to group by.
April 24, 2015 at 3:07 pm
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.
April 24, 2015 at 3:10 pm
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.
April 24, 2015 at 3:11 pm
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
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply