April 27, 2012 at 2:50 am
Hi all,
I have a table with this structure :
CREATE TABLE dbo.tblStuffPriceHistory
(
sphId INT IDENTITY(1, 1)
NOT NULL ,
sphStfId INT NOT NULL ,
sphAsk REAL NULL ,
sphLow REAL NULL ,
sphHigh REAL NULL ,
sphChange REAL NULL ,
sphChangePrcent REAL NULL ,
CONSTRAINT PK_tblStuffPriceHistory PRIMARY KEY CLUSTERED ( sphId ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]
this table fills every one minute from multiple sources so I have about 1440 records daily for each stuff (sphStfId ).
In my web application that shows price swing chart for each stuff with this options :
-Today
-Last three days
-Last week
-Last month
I create this procedure for binding small amount of data to this chart by generating random number :
ALTER PROCEDURE [jir].[spShowStuffSwingByType]
(
@stfId INT ,
@typId SMALLINT
)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @StartDate DATE ,
@tempDateDiff TINYINT ,
@Criteria VARCHAR(10)
SET @tempDateDiff = CASE @typId
WHEN 500 THEN 1 --Today
WHEN 501 THEN 3 --Last three days
WHEN 502 THEN 7 --Last week
WHEN 504 THEN 30 --Last month
END
SET @StartDate = DATEADD(DAY, -1 * @tempDateDiff, GETDATE())
DECLARE @Upper TINYINT ;
DECLARE @lower TINYINT ;
SET @Upper = CASE @typId
WHEN 500 THEN 5
WHEN 501 THEN 20
WHEN 502 THEN 20
WHEN 503 THEN 20
END
SET @lower = CASE @typId
WHEN 500 THEN 0
WHEN 501 THEN 10
WHEN 502 THEN 15
WHEN 503 THEN 15
END
SET @Criteria = '%'
+ CAST(ROUND(( ( @Upper - @lower - 1 ) * RAND() + @lower ), 0) AS VARCHAR(2))
SELECT ISNULL(sphAsk, sphPrice) AS stcPrice ,
sphHigh stcMax ,
sphLow stcMin ,
ISNULL(sphAsk, sphPrice) stcAvg ,
sphChange stcChange ,
sphChangePrcent stcChangePercent ,
sphCreateDate stcPriceDate
FROM dbo.tblStuffPriceHistory WITH ( NOLOCK )
INNER JOIN dbo.tblJringiMapping WITH ( NOLOCK ) ON jmpStfId = sphStfId
AND jmpSourceId = sphSourceId
WHERE sphCreateDate BETWEEN @StartDate AND GETDATE()
AND sphId LIKE @Criteria
AND sphStfId = @stfId
ORDER BY sphCreateDate
END
but I have large amount of data yet. anyone has better suggestion ?
my goal is reaching to about 50 records in every chart type
April 27, 2012 at 6:55 am
I don't understand your issue. Can you please clarify?
Maybe including some sample data and an expected output based on the sample data would help clarity.
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply