3

I am looking for some help to create an efficient SQL query (SQL Server 2012) that will identify the latest records (date) for all the items in the table (table 2 below) on each of a list of given dates (table 1 below). Here are the tables and sample data set. The tables are rather huge (600 dates, 5K+ data entries each for 6000 items).

table 1: qdate:

QUERY_DATES
03/03/2015   
03/04/2015  
04/09/2015  
06/12/2015  
05/05/2016  

table 2: svalue:

ITEM  DATA_ENTRY_DATE     VALUE 
1     03/02/2014          24  
1     03/02/2015          22  
1     04/04/2015          100  
1     03/09/2016          102  
2     03/02/2015          -25  

Required SQL Result:

ITEM QUERY_DATES  VALUE   DATA_ENTRY_DATE  
1    03/03/2015    22     03/02/2015   
1    03/04/2015    22     03/02/2015   
1    04/09/2015   100     04/04/2015  
1    06/12/2015   100     04/04/2015  
1    05/05/2016   102     03/09/2016  
2    03/03/2015   -25     03/02/2015   
2    03/04/2015   -25     03/02/2015   
2    04/09/2015   -25     03/02/2015  
2    06/12/2015   -25     03/02/2015  
2    05/05/2016   -25     03/02/2016  
  • Wasn't sure of your rule. Tried to clarify in an edit. Please revert if I erred. – Michael Green May 7 '16 at 10:55
4

I think this is what you want:

SELECT 
    i.item,
    q.query_dates,
    s.value,
    s.data_entry_date
FROM
    qdate AS q
  CROSS JOIN
    ( SELECT DISTINCT item
      FROM svalue
    ) AS i
  OUTER APPLY
    ( SELECT TOP (1) s.value, s.data_entry_date
      FROM svalue AS s
      WHERE s.item = i.item
        AND s.data_entry_date <= q.query_dates
      ORDER BY s.data_entry_date DESC
   ) AS s 
ORDER BY 
    item, 
    query_dates ;

Now about efficiency, I doubt there is way to make this blazing fast with a big table. Even with the rather small tables (600 dates, 6K items), you still want a result set of 3.6M rows.

An index on (item, data_entry_date) INCLUDE (value) will probably help a lot but still, it's 3 million rows of results. Are you going to display them? Or save them in another table for further analysis?

If there is another table with items, it would also be probably better to replace the i derived table with that table.

  • Agreed about the index. And I don't see a great way for the system to scale. – Max Vernon May 6 '16 at 20:18
2

Since you didn't, I created a test-bed:

USE tempdb;

IF OBJECT_ID('dbo.qdate') IS NOT NULL
DROP TABLE dbo.qdate;
CREATE TABLE dbo.qdate
(
    QueryDate DATETIME NOT NULL
        CONSTRAINT PK_qdate
        PRIMARY KEY
        CLUSTERED
);

IF OBJECT_ID('dbo.svalue') IS NOT NULL
DROP TABLE dbo.svalue;
CREATE TABLE dbo.svalue
(
    Item INT NOT NULL
    , DataEntryDate DATETIME NOT NULL
    , DataEntryValue INT NOT NULL
);

INSERT INTO dbo.qdate (QueryDate)
VALUES ('2015-03-03T00:00:00')
    , ('2015-03-04T00:00:00')
    , ('2015-04-09T00:00:00')
    , ('2015-06-12T00:00:00')
    , ('2016-05-05T00:00:00');

INSERT INTO dbo.svalue(Item, DataEntryDate, DataEntryValue)
VALUES (1, '2014-03-02T00:00:00', 24)
    , (1, '2015-03-02T00:00:00', 22)
    , (1, '2015-04-04T00:00:00', 100)
    , (1, '2016-03-09T00:00:00', 102)
    , (2, '2015-03-02T00:00:00', -25);

The following query returns the results you requested:

;WITH src AS 
(
    SELECT DISTINCT qd.QueryDate
        , sv.Item
    FROM dbo.svalue sv
        , dbo.qdate qd
)
, src2 AS
(
SELECT s.Item
    , s.QueryDate
    , sv.DataEntryValue
    , sv.DataEntryDate
    , rn1 = ROW_NUMBER() OVER (PARTITION BY s.Item, s.QueryDate 
                          ORDER BY sv.DataEntryDate DESC)
FROM src s
    LEFT JOIN dbo.svalue sv ON s.Item = sv.Item
        AND s.QueryDate >= sv.DataEntryDate
)
SELECT *
FROM src2
WHERE src2.rn1 = 1

The first CTE, src, multiplies the query dates by the number of items. As @ypercube said in his answer, this will very quickly get out of control as the quantity of both increase.

The second CTE uses the ROW_NUMBER() aggregate to partition by the item and querydate to allow the main query to only pull the most recent svalue row for each row in qdate.


Another method:

SELECT sv.Item
    , q.QueryDate
    , sv.DataEntryValue     
    , sv.DataEntryDate
FROM dbo.qdate q
OUTER APPLY (SELECT sv.*
            , rn = ROW_NUMBER() OVER (PARTITION BY sv.item 
                                      ORDER BY sv.DataEntryDate DESC)
        FROM dbo.svalue sv
        WHERE sv.DataEntryDate <= q.QueryDate
    ) sv
WHERE sv.rn = 1
ORDER BY q.QueryDate;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.