Quastion about stoed procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

Quastion about stoed procedure

Author

Topic

omergld
Starting Member

4 Posts

Posted - 2007-06-22 : 02:47:04

hi,
i have this stored pocedure:
(
@sdate datetime,
@edate datetime,
@grp nvarchar(20),
@store nvarchar(20),
@comp nvarchar(20)
)
AS
(
SELECT DISTINCT Data_Time, FileName, SFOpt, [Group], Store, Computer
FROM User_Record
WHERE (Data_Time >= CONVERT(DATETIME, @sdate, 102)) AND (Data_Time <= CONVERT(DATETIME, @edate, 102)) AND ([Group] = @grp) AND (Store = @store)
AND (Computer = @comp)
i tried to add to that stored procedure order by filename but it cause erro,why is that?
omer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 02:50:10

quote:
i tried to add to that stored procedure order by filename but it cause erro,why is that?

Can you show me where do you add it ?

And what is the data type for column Data_Time ?

KH
[spoiler]Time is always against us[/spoiler]

omergld
Starting Member

4 Posts

Posted - 2007-06-22 : 03:25:36

hi,
i tried to add the order by statment at the end of the procedue after the where(i did it with sql server wizard)
data_time type is datetime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 03:33:44

[code]CREATE PROCEDURE your_sp
(
@sdate datetime,
@edate datetime,
@grp nvarchar(20),
@store nvarchar(20),
@comp nvarchar(20)
)
AS
BEGIN
SELECT DISTINCT
Data_Time, [FileName], SFOpt, [Group], Store, Computer
FROM User_Record
WHERE Data_time >= @sdate
AND Data_time < DATEADD(DAY, 1, @edate)
AND [Group] = @grp
AND Store = @store
AND Computer = @comp
ORDER BY [FileName]
END[/code]

KH
[spoiler]Time is always against us[/spoiler]

omergld
Starting Member

4 Posts

Posted - 2007-06-22 : 04:04:36

there are some rows that have the same filename,i want to select only the row with the latest time
i tried DISTINCT but it select all the rows with the file and not only one with the latest time,how can i do it?
thanks for the help

efscl
Starting Member

4 Posts

Posted - 2007-06-22 : 08:57:22

distinct is working over the whole record set - so any column has to be the same that a row ist "distinct".

Maybe this helps: select without the date and make a group by on the rest - so you get only one - than merge/join this with a select date and order by date ...

dont know if this works ... ;o)

omergld
Starting Member

4 Posts

Posted - 2007-06-22 : 11:09:56

hi,
i need to select the filename only once and with the latest time(when the same filename appers moe than once)
any ideas?
omer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources