mercredi 30 mai 2012 14:39
My data has timestamp, but every timestamp field will be the same for the return results of my stored proc. If I am returning up to fifty records to the calling webservice, I dont want to return the same timestamp fifty times. But if I run a query before/after to see what the timestamp used was, it would be right most of the time, but not all of the time.
Here is the stored proc, so far and the table.
USE [TW_Cloud] GO /****** Object: StoredProcedure [dbo].[MetroGetLatestUp] Script Date: 05/30/2012 07:25:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Daniel -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MetroGetLatestUp] -- Add the parameters for the stored procedure here @Sensitivity float = 0.25, @timeBack int = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @eventHorizonDate datetime declare @olderDate datetime SELECT @eventHorizonDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' ORDER BY EntryTime DESC) --SELECT @laterDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' AND EntryTime <> @eventHorizonDate ORDER BY EntryTime DESC) select @olderdate = (select top (1) entrytime from (select top(2 + @timeback) entryTime from mean where ticker = 'spy' order by entrytime desc)t order by entrytime asc) select * from (select t1.Ticker,t1.NEWS, (select News from mean t2 join quotecount200 on quotecount200.ticker = t1.Ticker and quotecount200.mycount > 190 where entrytime = @eventHorizonDate and t2.ticker = t1.Ticker)-t1.news as diff from mean t1 where entrytime = @olderdate) X WHERE (diff >.1 + @sensitivity) and ticker in (select ticker from mean where --news <1 --and news > -1 --and entrytime >@olderdate and entrytime <= @eventhorizondate) order by (diff ) desc -- declare @earlyDate datetime -- declare @laterDate datetime -- SELECT @earlyDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' ORDER BY EntryTime DESC) --SELECT @laterDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' AND EntryTime <> @earlyDate ORDER BY EntryTime DESC) -- select @laterdate = (select top (1) entrytime from (select top(2) entryTime from mean where ticker = 'spy' order by entrytime desc)t order by entrytime asc) --print @earlydate --print @laterdate END GO USE [TW_Cloud] GO /****** Object: Table [dbo].[Mean] Script Date: 05/30/2012 07:40:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Mean]( [MeanId] [int] IDENTITY(1,1) NOT NULL, [Ticker] [varchar](10) NOT NULL, [Price] [money] NOT NULL, [EntryTime] [datetime] NOT NULL, [News] [float] NULL, [ActualPrice] [money] NULL, CONSTRAINT [PK_Mean] PRIMARY KEY CLUSTERED ( [MeanId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UK_Mean_EntryTime_Ticker] UNIQUE NONCLUSTERED ( [EntryTime] ASC, [Ticker] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Toutes les réponses
mercredi 30 mai 2012 14:45Without sample data it is almost impossible to guess. Post a concise and complete example.
mercredi 30 mai 2012 15:12
Please convert Your Time Stamp to required format and then try with distinct key word.
This is assumption answer.Please explain with sample data and expected Output
With Thanks and Regards
mercredi 30 mai 2012 15:17
Do you have a link by chance on how to prepare sample data correctly, to be submitted?
mercredi 30 mai 2012 15:21A concise and complete example consist of a table declaration, either a temporary table or table variable (or more of them). Appropriate insert statements filling it with sample data. And your (failing) query and your desired output. In your special case of an stored procedure you don't the create procedure part, refactor it to a simple query..
mercredi 30 mai 2012 15:21
As mentioned by the other replies here, without seeing your sample data , our suggestions are ONLY a guess.
The data type of your "timestamp" columns is datetime. The datetime datatype has precision/accuracy of 300 milliseconds only. This means if you have had multiple rows inserted within a 300 ms timeframe, they will all have the same value in the EntryTime column - which is of datetime data type - http://msdn.microsoft.com/en-us/library/ms187819.aspx
This could possibly be the reason for the intermittent nature of the issue. This is ONLY a guess since there is no sample data in your post.
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
- Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator mercredi 30 mai 2012 15:28
mercredi 30 mai 2012 15:28
All I was saying was I dont know how to save the sample data in such a way as to make it easy for others to recreate. The last time I did it, I was told I did it wrong, and required extra effort on the part of the person who was helping me. I guess when I said in my previous message, "Do you have a link by chance on how to prepare sample data correctly, to be submitted? " that I wasnt clear.
In any case, I have a number of TSQL problems and have decided to hire someone for a few hours, rather than spend days trying to do it myself. So, I guess I can say, "Nevermind" though I would still like to know how to post sample data.
Oh, the timestamp for my records only change every five minutes. I dont have a query that has a problem, because I dont return the timestamp. I wanted to know if there was a way to return it, without returning it fifty times. In otherwords, I probably should have said "Is it possible to return a scalar value along with my regular data returned, in c#."
In fact, maybe I will repost that as my question(g), though I am still going to hire someone.
mercredi 30 mai 2012 15:35
Something like this:
Declare @tvTable Table ( MeanId int, Ticker varchar(10), Price money, EntryTime datetime, News float, ActualPrice money ) Insert @tvTable Select 1, 'ticker01', 1.05, '2012-01-01', 1.12345, 2.10 UNION ALL Select 2, 'ticker02', 4.05, '2012-02-01', 5.4321, 12.10 Select * From @tvTable
Basically, provide the table definition in the form of a temp table or table variable, and then give us some insert statements into that table. The data doesn't have to be "real", just has to make sense for the definition and query.
The OP in this thread gave table definition as a table variable, the insert statements, and the desired output of the query they needed help with.
- Modifié dgjohnson mercredi 30 mai 2012 15:39
mercredi 30 mai 2012 15:39You can do that: Use an additional output parameter for your stored procedure.
mercredi 30 mai 2012 15:41
On my next question I will be sure and do that.
On this question, I was able to deduce the answer on my own though, so I might as well post it.
I can return a single value, by adding another
and then in the C# code I do this
Read.NextRecord() //advances to the next result set
- Marqué comme réponse danielsn mercredi 30 mai 2012 15:41
mercredi 30 mai 2012 15:43Modérateur
If you install SSMS Tools Pack (assuming you're using SQL Server 2008/R2), it has an option to generate insert scripts for the table.
Take a look here
Alternatively, Brad Schulz posted a script you can use to generate insert statements, but as usual I'm having a hard time searching for that blog - spent already 10 minutes - no cigar.
For every expert, there is an equal and opposite expert. - Becker's Law
- Marqué comme réponse danielsn mercredi 30 mai 2012 15:53
mercredi 30 mai 2012 15:43Consider using an output parameter instead..
mercredi 30 mai 2012 15:53thanks Naomi