A query working with time series

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A query working with time series

Author

Topic

niko79542
Starting Member

7 Posts

Posted - 2012-09-06 : 09:28:30

Hi,

I am looking for a query. My question concerns just 3 columns, which are all in the same table. I am a SQL beginner (I have taken 1 class in college), so I am not sure if it is possible to solve this in SQL...but I have some ideas.

Anywhere, here is the type of data I am dealing with. Let’s call my table tableX.

tableX
identifier (PK, varchar(50), not null)
Date (PK, date, not null)
Value (real, null)

--|| identifier || date || value
1 || 1 || 2012-09-04 || NULL
2 || 1 || 2012-09-03 || NULL
3 || 1 || 2012-09-02 || 25.5
4 || 1 || 2012-09-01 || NULL
5 || 1 || 2012-08-30 || NULL
6 || 1 || 2012-08-25 || NULL
7 || 1 || 2012-08-24 || NULL
8 || 1 || 2012-08-23 || 60
9 || 2 || 2012-09-04 || 30
10 || 2 || 2012-09-03 || 300
11 || 2 || 2012-09-02 || NULL
12 || 2 || 2012-09-01 || NULL
13 || 2 || 2012-08-31 || NULL
14 || 2 || 2012-08-30 || NULL
15 || 2 || 2012-08-29 || NULL
16 || 2 || 2012-08-28 || 3000
17 || 3 || 2012-09-04 || NULL
18 || 3 || 2012-08-31 || 5
19 || 3 || 2012-08-30 || 1
20 || 3 || 2012-08-20 || 3
21 || 3 || 2012-08-19 || 4
22 || 3 || 2012-08-16 || 10
23 || 3 || 2012-08-15 || 9
24 || 3 || 2012-08-14 || 11

So I have a handful of identifiers in this table, but pretend the only identifiers are 1, 2, and 3 for simplicity. Data is not stored ascending by identifier and descending by date, but this is the query I did to see the above data.

Select identifier, date, value
From tableX
Order by identifier asc, date desc

Problem:
We are not fed a record for every day. Additionally, sometimes we are fed records which are simply NULL. Every identifier has at least two records with a value.
I am interested in selecting identifiers which have only one non-null value where date > 2012-08-31
So I want the query to return identifier 1 because there are four records after 2012-08-31, and only one of them has a non-null value (2012-09-02 is 25.5).
I do not want to select identifier 2 because there are two records after 2012-08-31 with a value (2012-09-04 is 30, 2012-09-03 is 300). I do not want identifier 3 because there are no records after 2012-08-31 with a value.

Along with identifier, I would like a column of the difference of the date of the most recent value and the date of the second most recent value. For identifier 1, I want to return 2012-09-02 minus 2012-08-23, because those are the two most recent records with non-null values. The format of this column is not impotant. A substitute for this column could just be to display the date of the second most recent value (2012-08-23). That would be fine.

A nice finishing touch would be to sort ascending by this calculated ‘date’ column.

Another helpful column would be the difference of the two values. so for identifier 1, it would be 25.5-60 or -34.5. Again, not necessary, but ideas for this would be helpful.

Expected result:

--|| identifier || date || difference in values
1 || 1 || 2012-08-23 || -34.5

Thx,
Nick

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-06 : 17:49:42

Maybe this will help you get going:

DECLARE @tableX TABLE 
(
    identifier int not null,
    Date date not null,
    Value real null
)
INSERT @tableX
VALUES
(1, ' 2012-09-04 ', NULL),
(1, ' 2012-09-03 ', NULL),
(1, ' 2012-09-02 ', 25.5),
(1, ' 2012-09-01 ', NULL),
(1, ' 2012-08-30 ', NULL),
(1, ' 2012-08-25 ', NULL),
(1, ' 2012-08-24 ', NULL),
(1, ' 2012-08-23 ', 60),
(2, ' 2012-09-04 ', 30),
( 2, ' 2012-09-03 ', 300),
( 2, ' 2012-09-02 ', NULL),
( 2, ' 2012-09-01 ', NULL),
( 2, ' 2012-08-31 ', NULL),
( 2, ' 2012-08-30 ', NULL),
( 2, ' 2012-08-29 ', NULL),
( 2, ' 2012-08-28 ', 3000),
( 3, ' 2012-09-04 ', NULL),
( 3, ' 2012-08-31 ', 5),
( 3, ' 2012-08-30 ', 1),
( 3, ' 2012-08-20 ', 3),
( 3, ' 2012-08-19 ', 4),
( 3, ' 2012-08-16 ', 10),
( 3, ' 2012-08-15 ', 9),
( 3, ' 2012-08-14 ', 11)

;WITH cte AS
(
    SELECT 
        X.identifier,
        X.Date,
        X.Value,
        ROW_NUMBER() OVER (PARTITION BY x.identifier ORDER BY date) AS RowNum
    FROM 
        @tableX AS X
    INNER JOIN
    (
        SELECT identifier
        FROM @tableX
        WHERE Date > '2012-08-31'
        AND Value IS NOT NULL
        GROUP BY identifier
        HAVING COUNT(*) = 1
    ) AS A
    ON X.identifier = A.identifier
    WHERE X.Value IS NOT NULL
)

SELECT 
    A.identifier,
    A.Date
    ,B.value - A.Value AS DifferenceInValues
FROM 
    cte AS A
INNER JOIN
    cte AS B
    ON A.RowNum = B.RowNum - 1

niko79542
Starting Member

7 Posts

Posted - 2012-09-07 : 10:07:40

Thank you for taking the time to read and answer my question. I have not run it yet, but the logic makes sense.

I did not know how to query within a query.

Nick

niko79542
Starting Member

7 Posts

Posted - 2012-09-11 : 09:17:58

Heres the query which worked. Note I added two lines at the end. If there is more than one identifier with only 1 non null value > 2012-8-31, then you must join by identifier as well in order to get results pertaining to one and only one identifier.

It also must be limited to results where b.date is > 2012-08-31, because if there are identifiers with 2+ non null values in their whole history, we only want ones where of course, b.date is > 2012-08-31.

Thanks,
Nick

---------------------------------------

DECLARE @tableX TABLE
(
identifier int not null,
Date date not null,
Value real null
)
INSERT @tableX
VALUES
(1, ' 2012-09-04 ', NULL),
(1, ' 2012-09-03 ', NULL),
(1, ' 2012-09-02 ', 25.5),
(1, ' 2012-09-01 ', NULL),
(1, ' 2012-08-30 ', NULL),
(1, ' 2012-08-25 ', NULL),
(1, ' 2012-08-24 ', NULL),
(1, ' 2012-08-23 ', 60),
(2, ' 2012-09-04 ', 30),
( 2, ' 2012-09-03 ', 300),
( 2, ' 2012-09-02 ', NULL),
( 2, ' 2012-09-01 ', NULL),
( 2, ' 2012-08-31 ', NULL),
( 2, ' 2012-08-30 ', NULL),
( 2, ' 2012-08-29 ', NULL),
( 2, ' 2012-08-28 ', 3000),
( 3, ' 2012-09-04 ', NULL),
( 3, ' 2012-08-31 ', 5),
( 3, ' 2012-08-30 ', 1),
( 3, ' 2012-08-20 ', 3),
( 3, ' 2012-08-19 ', 4),
( 3, ' 2012-08-16 ', 10),
( 3, ' 2012-08-15 ', 9),
( 3, ' 2012-08-14 ', 11)

;WITH cte AS
(
SELECT
X.identifier,
X.Date,
X.Value,
ROW_NUMBER() OVER (PARTITION BY x.identifier ORDER BY date) AS RowNum
FROM
@tableX AS X
INNER JOIN
(
SELECT identifier
FROM @tableX
WHERE Date > '2012-08-31'
AND Value IS NOT NULL
GROUP BY identifier
HAVING COUNT(*) = 1
) AS A
ON X.identifier = A.identifier
WHERE X.Value IS NOT NULL
)

SELECT
A.identifier,
A.Date
,B.value - A.Value AS DifferenceInValues
FROM
cte AS A
INNER JOIN
cte AS B
ON A.RowNum = B.RowNum - 1
and A.identifier = B.identifier
where Date > '2012-08-31'

niko79542
Starting Member

7 Posts

Posted - 2012-09-11 : 23:02:00

DECLARE @tableX TABLE
(
identifier int not null,
Date date not null,
Value real null
)
INSERT @tableX
VALUES
(1, '2012-09-04', NULL),
(1, '2012-09-03', NULL),
(1, ' 2012-09-02', 25.5),
(1, ' 2012-09-01', NULL),
(1, ' 2012-08-30', NULL),
(1, ' 2012-08-25', NULL),
(1, ' 2012-08-24', NULL),
(1, ' 2012-08-23', 60),
(2, ' 2012-09-04', 30),
( 2, ' 2012-09-03', 300),
( 2, ' 2012-09-02', NULL),
( 2, ' 2012-09-01', NULL),
( 2, ' 2012-08-31', NULL),
( 2, ' 2012-08-30', NULL),
( 2, ' 2012-08-29', NULL),
( 2, ' 2012-08-28', 3000),
( 3, ' 2012-09-04', NULL),
( 3, ' 2012-08-31', 5),
( 3, ' 2012-08-30', 1),
( 3, ' 2012-08-20', 3),
( 3, ' 2012-08-19', 4),
( 3, ' 2012-08-16', 10),
( 3, ' 2012-08-15', 9),
( 3, ' 2012-08-14 ', 11)

;WITH cte AS
(
SELECT
X.identifier,
X.Date,
X.Value
FROM @tableX AS X
WHERE X.Value IS NOT NULL
and X.Date > '2012-08-31'
and X.identifier in (
SELECT identifier
FROM @tableX
WHERE Date > '2012-08-31'
AND Value IS NOT NULL
GROUP BY identifier
HAVING COUNT(*) = 1
)
)

SELECT
A.identifier,
A.Date,
A.Value AS DifferenceInValues
FROM
cte AS A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources