March 24, 2005 at 12:59 pm
We collect data into our database every minute. But data will not be comin at some point of time due to the application problems.
say We started collecting data @ 10 am and data collected without faliure upto 12:46 pm and data is not collected for next 10 mins and again data is collected there onwards...
IN my reporting I wanted to show each minute's data
say
time qty
10:00:00 1
10:01:00 4
10:02:00 3
..
...
upto
12:45:00 4
12:46:00 0
12:47:00 0
12:49:00 0
...
...
12:55:00 7
12:56:00 8
and from 12:45 to 12:55 we have no data collected. But still I wanted to show those minutes with no data.
How can i do implement this in my reporting.
Thanks.
March 24, 2005 at 1:53 pm
Hello Sahana,
One possible way to solve the problem to build a table containing the timestamps in the first column, then do a left outer join to the actual data table. A simple example might look like the following:
SET NOCOUNT ON
create table timeTable (MinuteTimeStamp SmallDateTime)
GO
DECLARE @Hour INT,
@Minute INT,
@Time SmallDateTime
SET @Time = '00:00'
WHILE @Time <= '00:59'
BEGIN
INSERT INTO timeTable (MinuteTimeStamp) SELECT @Time
SET @Time = DATEADD(MINUTE,1,@Time)
END
CREATE TABLE dataTable (MinuteTimeStamp SmallDateTime,
DataReading INT)
INSERT INTO dataTable (MinuteTimeStamp, DataReading)
SELECT '00:01', 5 UNION
SELECT '00:02', 6 UNION
SELECT '00:03', 7 UNION
SELECT '00:04', 3 UNION
SELECT '00:09', 2 UNION
SELECT '00:10', 1
select CONVERT(VARCHAR,TT.MinuteTimeStamp,8),
DT.DataReading
from TimeTable TT
left outer join DataTable DT on DT.MinuteTimeStamp = TT.MinuteTimeStamp
drop table timeTable
DROP TABLE dataTable
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply