Write a sql for updating data based on time

Question

Hello everyone,

Because I am new with SQL Server and T-SQL, so I will need your help.

I have 2 table: Realtime and EOD. To understand my question, I give example data for 2 tables:

---Realtime table---

Symbol     Date            Value
ABC     1/3/2009 03:05:01   327   // this day is not existed in EOD -> inserting
BBC     1/3/2009 03:05:01   458   // this day is not existed in EOD -> inserting
ABC     1/2/2009 03:05:01   326   // this day is new -> updating
BBC     1/2/2009 03:05:01   454   // this day is new -> updating
ABC     1/2/2009 02:05:01   323
BBC     1/2/2009 02:05:01   453
ABC     1/2/2009 01:05:01   313
BBC     1/2/2009 01:05:01   423

---EOD table---

Symbol     Date            Value
ABC     1/2/2009 02:05:01   323
BBC     1/2/2009 02:05:01   453

I will need to create a store procedure to update value of symbols. If data in day of a symbol is new (compare between Realtime & EOD), it will update value and date for EOD at that day if existing, otherwise inserting.

And store will update EOD table with new data:

---EOD table---

Symbol     Date            Value
ABC     1/3/2009 03:05:01   327
BBC     1/3/2009 03:05:01   458
ABC     1/2/2009 03:05:01   326
BBC     1/2/2009 03:05:01   454

P/S: I use SQL Server 2005. And I have a similar answered question at here: http://stackoverflow.com/questions/2726369/help-to-the-way-to-write-a-query-for-the-requirement

Please help me. Thanks.

Answer 1

Here is the code that makes a complete EOD table from Realtime table (tested on MSSQL 2008, but should work on 2005 as well).

SELECT Symbol, Date, Value FROM
(
    SELECT *, ROW_NUMBER() OVER(
        PARTITION BY symbol, DATEPART(yy, date), DATEPART(y, date) 
        ORDER BY date desc) AS ord
    FROM Realtime
) AS t
WHERE ord = 1

Depending on what the exact requirements are, you might tweak it to make it work in the incremental manner.

Answer 2

2 steps:

UPDATE      EOD
SET         EOD.Value = REALTIME.Value
FROM        EOD
INNER JOIN  REALTIME
ON          EOD.Symbol = REALTIME.Symbol
AND         EOD.Date = REALTIME.Date

INSERT INTO EOD ( Symbol , Date , Value )
SELECT        REALTIME.Symbol
            , REALTIME.Date
            , REALTIME.Value
FROM        EOD
LEFT JOIN   REALTIME
ON          EOD.Symbol = REALTIME.Symbol
AND         EOD.Date = REALTIME.Date
WHERE       EOD.Symbol IS NULL
AND         EOD.Date IS NULL

This assumes that you have some sort of uniqueness on (Symbol, Date) within both tables.

One additional comment is that the inclusion of dates is problematic, particularly with the time component included. I'd suggest that you CAST or CONVERT all of your date values to something which excludes the time portion. I would use CONVERT(varchar(11), EOD.Date, 106), to convert the date to something without a date component. See below:

UPDATE      EOD
SET         EOD.Value = REALTIME.Value
FROM        EOD
INNER JOIN  REALTIME
ON          EOD.Symbol = REALTIME.Symbol
AND         CONVERT(varchar(11), EOD.Date, 106) = CONVERT(varchar(11), REALTIME.Date, 106)

INSERT INTO EOD ( Symbol , Date , Value )
SELECT        REALTIME.Symbol
            , CONVERT(varchar(11), REALTIME.Date, 106)
            , REALTIME.Value
FROM        EOD
LEFT JOIN   REALTIME
ON          EOD.Symbol = REALTIME.Symbol
AND         CONVERT(varchar(11), EOD.Date, 106) = CONVERT(varchar(11), REALTIME.Date, 106)
WHERE       EOD.Symbol IS NULL
AND         EOD.Date IS NULL