Select between dates and with initial values

Question

I need to make a graph from a log. The log entries are not in regular intervals.

I would like to select rows between dates along with what the values were immediately before the start date (that is, from whenever the immediatly preceeding log was entered).

So, let's say:

table Foo has id and value columns,

table Bar has id, foo_id, and value columns, and

table BarLog has id, foo_id, bar_id, bar_value and timestamp.

So there can be many Bars for one Foo.

I need all rows from BarLog for all Bars given some foo_id between, say, 07/01/2012 and 07/31/2012 and the value (row) for each Bar as it was on 07/01/2012.

Hope that made sense, if not, I'll try to clarify.

EDIT (above left for context):

Let's simplify this down another step. If I have a table with two foreign keys, fk_a and fk_b, and a timestamp, how can I get the most recent rows with a given fk_a and a distict fk_b.

As suggested, here's an example.

+----+------+------+-------------+
| id | fk_a | fk_b |  timestamp  |
+----+------+------+-------------+
| 1  | 1    | 1    | 01-JUL-2012 |
| 2  | 2    | 2    | 02-JUL-2012 |
| 3  | 1    | 1    | 04-JUL-2012 |
| 4  | 2    | 2    | 05-JUL-2012 |
| 5  | 1    | 3    | 07-JUL-2012 |
+----+------+------+-------------+

Given a fk_a of 1, I would want rows 3 and 5. So looking only at rows 1, 3, and 5 (those with fk_a of 1), get the most recent of each fk_b (where row 3 is more recent than row 1 for fk_b=1).

Thanks again.

[How do _you_](http://mattgemmell.com/2008/12/08/what-have-you-tried/) think you should be doing this? — Ben, Aug 20 '12 at 21:05
Ben, obviously, I don't know, I can't do a ORDER BY and TOP/LIMIT because I don't know how many, my only solution is iterating with TOP 1, but this means multiple queries. — Chad Schouggins, Aug 21 '12 at 14:01

Roberto Navarro · Answer 1 · 2012-08-20T21:58:59.680

0

Are you looking for something like this?

        SELECT bl.bar_value, timestamp
  FROM foo f, bar b, barlog bl
 WHERE     f.id = b.id
       AND b.foo_id = bl.foo_id
       AND timestamp BETWEEN '01-JUL-2012' AND '31-JUL-2012'
       AND b.foo_id = :enter_value_here
ORDER BY timestamp DESC

Use the :enter_value_here to add the foo_id you need the data for...

What plotting tool are you using? You can take the data-set and push it into excel for plotting..in any case, hopefully the query above can get you closer to what you're trying to do.

edited Aug 20 '12 at 21:58

answered Aug 20 '12 at 21:46

Roberto Navarro

948
4
16

Thanks, but this is only half the answer, it's the values from immediately before the start date that I'm having issues with. And I'm using ASP.NET's built in charting framework. – Chad Schouggins Aug 21 '12 at 14:08

score 0 · Answer 2 · answered Aug 20 '12 at 22:31

For a dense set, create a date table and run the following query:

DECLARE @StartDate datetime

SET @StartDate = '2012-01-01'

SELECT f.ID as foo_id, b.bar_id, f.Value, GetDate() as DateStamp
FROM Foo f
inner join Bar b on f.id = b.foo_id
WHERE /*enter criteria for bar selection*/
UNION ALL
SELECT f.ID as foo_id, b.bar_id, f.Value, GetDate() as DateStamp
FROM (
    SELECT MAX(bl.timestamp) as bl_timestamp, bl.bar_id as bar_id
    FROM Dates d 
    INNER JOIN BarLog bl on bl.timestamp < d.Date
    WHERE /*enter criteria for bar selection*/
    GROUP BY bl.bar_id
) as pi
INNER JOIN BarLog bl on pi.bar_id = bl.bar_id and bl.timestamp = pi.bl_timestamp
WHERE d.Day_Of_Month = 1 and d.Date between @StartDate and getDate()
  AND /*enter criteria for bar selection*/

The date table can be something like http://it.toolbox.com/wiki/index.php/Create_a_Time_Dimension_/_Date_Table or could be created temporarily each query by:

CREATE TABLE #Dates ([Date] datetime, Day_Of_Month int)

DECLARE @cDate datetime
SET @cDate = @StartDate

WHILE @cDate < getdate()
BEGIN
    INSERT INTO #Dates (Date, Day_Of_Month)
    SELECT @cDate, Datepart(d, @cdate)

    SET @cDate = DATEADD(m, 1 + DATEDIFF(m, 0, @cdate), 0)
END

with a DROP TABLE #Dates sitting after the select.

This query will return:

Foo_ID, Bar_ID, Value at datestamp, Datestamp

with the datestamps incrementing by 1 month at a time.

score 0 · Accepted Answer · edited May 23 '17 at 12:12

Finally found this question which had what I was looking for. Basically just joining with a grouped select. So the answer for my edit would be something like

SELECT * FROM SomeTable a
JOIN (
    SELECT fk_b, MAX(timestamp) as latest
    FROM SomeTable
    GROUP BY fk_b
) b
ON a.id = b.id
WHERE a.fk_a = @someIdA

Which would return the latest of each distinct fk_b with a specified fk_a

The original question would just be a union of this with a simple get between dates

Select between dates and with initial values

3 Answers3