select intervals

[tommot82]

Hi,

I am using a MSSQL database, and I am trying to select records from a db based on a 15 minute interval. The Date is recorded as a millisecond epoch time (stored as a numeric) which I round down to the nearest 15 minute window within the hour:

SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime, Value
FROM TBL1
WHERE ID = 'ID1'
AND RawDateAndTime BETWEEN 1243810800000 AND 1244415000000

My problem is that sometimes records may not exist and i want to return an empty/null row for that missing record somehow, but i am very new to SQL and i have no idea where to start on the query.

Can anyone offer me any pointers?

 

[markros]

select T.ID, D.flooredDate, D.rawdateandtime, D.Value from (select distinct ID from Tbl1) T LEFT JOIN (
SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime, Value
FROM TBL1
WHERE ID = 'ID1'
AND RawDateAndTime BETWEEN 1243810800000 AND 1244415000000) D
on T.ID = D.ID

something like this

 

[bborissov]

SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime, Value
FROM TBL1
WHERE ID = 'ID1'
AND RawDateAndTime BETWEEN 1243810800000 AND 1244415000000
UNION
SELECT NULL AS ID,
       NULL AS flooredDate,
       NULL AS rawdateandtime,
       NULL AS Value
FROM TBL1
WHERE NOT EXISTS(SELECT ID
                        FROM TBL1
                 WHERE ID = 'ID1'
                 AND RawDateAndTime BETWEEN 1243810800000 AND 1244415000000)

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tommot82]

Thanks for the reply markros,

I am trying to understand your query and i believe you are trying to output each id where a floored rawdateandtime value exists between the two given date values.

however i think i have explained myself badly.

What i meant was: supposing my table had rows corresponding to data collected at 08:05,08:17,08:49

I would like to return 4 rows for the hour 08:00-09:00 corresponding to each quarter of an hour in the hour, but with a null/blank value for the third row corresponding to half past the hour as no record exists for this quarter.

i hope this makes more sense.

 

[markros]

My query was supposed to do this for this specific interval you posted.

How to actually change your original query to pull data for each 15 minutes - that's a bit tricky and I can not figure this out right from the top of my head - and too lazy (as usual) to play in SSMS.

 

[tommot82]

makros: ah ok, i understand whats going on there now. Unfortunately the interval i have is a large interval spanning at least a day. Thankyou for your input though, it may well help me figure this out.

bborissov: I understand your query (which is a miracle for me) and i see what you are trying but the NOT EXISTS part has the same problem as makros's solution, it implies taht the period i am selecting between is a 15 minute wondow and it will infact be MUCH larger than that and i am trying to return 15 minute segments.

I really do appreciate you both trying to help me though, so thanks

I guess what I am after is some sort of function which lets you iterate in 15 minute segments and replaces nulls of no record is found.

 

[gmmastros]

Can you post some sample data and expected results?

Usually.... to fill in missing data, you need to join with a table that does have all the data in it.

By posting some sample data, it will be easier to understand, and easier to give advice.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Maybe if you post some example data and what you want as a result from that data it will be easier for me to understand what you want

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

We need to create a temp table with 15 minutes intervals and then join with it, I would think.

I believe it's your forte.

 

[tommot82]

ok below is a COMPLETE three hours of example data. If you were to run my original query against this it would return 12 rows, one for each of the 15 minute intervals between midnight and 02:59 (on the 01/06/2009).


My problem occurs when one of these rows is missing, and i need to return some sort of identifyable blank row. With the date string intact but with a NULL value for example.

ORIGINAL QUERY:

SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime, Value
FROM TBL1
AND RawDateAndTime BETWEEN 1243810896000 AND 1243821696000

SAMPLE DATA:

id,rawdateandtime,value,DATESTRING_NOT_IN_THE_DB
foo1,1243810896000,10,01/06/2009 00:01:36
foo1,1243811796000,5,01/06/2009 00:16:36
foo1,1243812696000,10,01/06/2009 00:31:36
foo1,1243813596000,5,01/06/2009 00:46:36
foo1,1243814496000,8,01/06/2009 01:01:36
foo1,1243815396000,7,01/06/2009 01:16:36
foo1,1243816296000,6,01/06/2009 01:31:36
foo1,1243817196000,5,01/06/2009 01:46:36
foo1,1243818096000,4,01/06/2009 02:01:36
foo1,1243818996000,1,01/06/2009 02:16:36
foo1,1243819896000,2,01/06/2009 02:31:36
foo1,1243820796000,3,01/06/2009 02:46:36

NOTE: i have included an extra column giving the date in a readible format to assist. This does not exist in the database.

many many many thanks for everyones help

 

[tommot82]

sorry my query should have also included ID = 'foo1'

SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime, Value
FROM TBL1
WHERE ID = 'foo1'
AND RawDateAndTime BETWEEN 1243810896000 AND 1243821696000

 

[bborissov]

NOT TESTED PROPERLY!!!!

--- Preparing Test data, you don't need this
DECLARE @Test TABLE (id varchar(10), rawdateandtime bigint)
INSERT INTO @Test VALUES('foo1',1243810896000)
INSERT INTO @Test VALUES('foo1',1243811796000)
INSERT INTO @Test VALUES('foo1',1243812696000)
INSERT INTO @Test VALUES('foo1',1243813596000)
INSERT INTO @Test VALUES('foo1',1243814496000)
INSERT INTO @Test VALUES('foo1',1243815396000)
INSERT INTO @Test VALUES('foo1',1243816296000)
INSERT INTO @Test VALUES('foo1',1243817196000)
INSERT INTO @Test VALUES('foo1',1243818096000)
INSERT INTO @Test VALUES('foo1',1243818996000)
INSERT INTO @Test VALUES('foo1',1243819896000)
INSERT INTO @Test VALUES('foo1',1243820796000)
---- End of preparing

--- Try to create a table variable with ALL periods
--- You need it
DECLARE @NewTest TABLE (id varchar(10) NULL, rawdateandtime bigint)
DECLARE @StartTime bigint
DECLARE @EndTime bigint
SET @StartTime = floor(1243810896000/900000)*900000
SET @EndTime   = floor(1243821696000/900000)*900000
WHILE @StartTime <= @EndTime
      BEGIN
           INSERT INTO @NewTest VALUES (NULL, @StartTime)
           SET @StartTime = @StartTime + 900000
      END
--- End


--- And now the actual query
--- Just change @Test to your actual table name

SELECT ID, floor(rawdateandtime/900000)*900000 as flooredDate, rawdateandtime
FROM @Test
WHERE RawDateAndTime BETWEEN 1243810896000 AND 1243821696000
UNION 
SELECT Newtst.ID, Newtst.rawdateandtime as flooredDate, Newtst.rawdateandtime
FROM @NewTest Newtst
LEFT JOIN @Test tst ON newtst.rawdateandtime = floor(Tst.rawdateandtime/900000)*900000 
WHERE tst.Id IS NULL

Again NOT tested properly esp. in the WHILE loop.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tommot82]

Guys this is brilliant,

its not quite what i needed, but it is better than that because i had to go fix it and i learnt something!

Thanks alot. Really appreciate it.