Write a sql for updating data based on time

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

SQL Server 2005 Forums

Transact-SQL (2005)

Write a sql for updating data based on time

Author

Topic

lulu2792
Starting Member

12 Posts

Posted - 2010-05-05 : 22:34:49

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 22:47:26

[code]
declare @Realtime table
(
Symbol varchar(3),
[Date] datetime,
Value int
)

declare @EOD table
(
Symbol varchar(3),
[Date] datetime,
Value int
)

insert into @Realtime
select 'ABC', '1/3/2009 03:05:01', 327 union all -- this day is not existed in EOD -> inserting
select 'BBC', '1/3/2009 03:05:01', 458 union all -- this day is not existed in EOD -> inserting
select 'ABC', '1/2/2009 03:05:01', 326 union all -- this day is new -> updating
select 'BBC', '1/2/2009 03:05:01', 454 union all -- this day is new -> updating
select 'ABC', '1/2/2009 02:05:01', 323 union all
select 'BBC', '1/2/2009 02:05:01', 453 union all
select 'ABC', '1/2/2009 01:05:01', 313 union all
select 'BBC', '1/2/2009 01:05:01', 423

insert into @EOD
select 'ABC', '1/2/2009 02:05:01', 323 union all
select 'BBC', '1/2/2009 02:05:01 ', 453

-- Query (Update)
update e
set [Date] = r.[Date],
Value = r.Value
from (
select Symbol, [Date], Value,
Dte = dateadd(day, datediff(day, 0, [Date]), 0)
from @EOD
) e
inner join
(
select Symbol, [Date], Value,
Dte = dateadd(day, datediff(day, 0, [Date]), 0),
row_no = row_number() over (partition by Symbol, dateadd(day, datediff(day, 0, [Date]), 0) order by [Date] desc)
from @Realtime
) r on e.Symbol = r.Symbol
and e.Dte = r.Dte
where r.row_no = 1

-- Query (Insert)
insert into @EOD (Symbol, [Date], Value)
select Symbol, [Date], Value
from (
select Symbol, [Date], Value,
Dte = dateadd(day, datediff(day, 0, [Date]), 0),
row_no = row_number() over (partition by Symbol, dateadd(day, datediff(day, 0, [Date]), 0) order by [Date] desc)
from @Realtime
) r
where r.row_no = 1
and not exists
(
select *
from @EOD x
where x.Symbol = r.Symbol
and dateadd(day, datediff(day, 0, x.[Date]), 0) = r.Dte
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-05 : 22:59:26

Ok. Thank khtan. Run well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 23:06:12

if you do notice, this query actually gives you the EOD, you can create a view out of it


select	Symbol, [Date], Value
from	(
		select	Symbol, [Date], Value, 
			-- Dte	= dateadd(day, datediff(day, 0, [Date]), 0),
			row_no = row_number() over (partition by Symbol, dateadd(day, datediff(day, 0, [Date]), 0) order by [Date] desc)
		from	@Realtime
	) r
where	r.row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 00:03:19

I have a change in my question. To optimize, I only want to get the last data from Realtime table of each symbol.
Ex:
---Realtime 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
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

I will get the last data of Realtime for each symbol:


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

And compare with EOD data, because '1/3/2009 03:05:01' is new and the day is not existing in EOD table -> inserting. And if '1/3/2009 03:05:01' is new and the day is existing in EOD table -> updating.

Please help me. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 00:08:30

just remove the dateadd-datediff from the "partition by"


select	Symbol, [Date], Value
from	(
		select	Symbol, [Date], Value, 
			row_no = row_number() over (partition by Symbol, dateadd(day, datediff(day, 0, [Date]), 0) order by [Date] desc)
		from	@Realtime
	) r
where	r.row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 00:12:46

[code]
-- Query (Update)
update e
set [Date] = r.[Date],
Value = r.Value
from (
select Symbol, [Date], Value
-- Dte = dateadd(day, datediff(day, 0, [Date]), 0)
from @EOD
) e
inner join
(
select Symbol, [Date], Value,
-- Dte = dateadd(day, datediff(day, 0, [Date]), 0),
row_no = row_number() over (partition by Symbol --, dateadd(day, datediff(day, 0, [Date]), 0)
order by [Date] desc)
from @Realtime
) r on e.Symbol = r.Symbol
-- and e.Dte = r.Dte
where r.row_no = 1

-- Query (Insert)
insert into @EOD (Symbol, [Date], Value)
select Symbol, [Date], Value
from (
select Symbol, [Date], Value,
-- Dte = dateadd(day, datediff(day, 0, [Date]), 0),
row_no = row_number() over (partition by Symbol --, dateadd(day, datediff(day, 0, [Date]), 0)
order by [Date] desc)
from @Realtime
) r
where r.row_no = 1
and not exists
(
select *
from @EOD x
where x.Symbol = r.Symbol
-- and dateadd(day, datediff(day, 0, x.[Date]), 0) = r.Dte
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 00:23:38

Wrong, the result is not correct. Your sql returns:


ABC	2009-01-03 03:05:01.000	327
BBC	2009-01-03 03:05:01.000	458

I don't know why your sql remove data of old day. My requirement is:


ABC	2009-01-03 03:05:01.000	327
BBC	2009-01-03 03:05:01.000	458
ABC	2009-01-02 02:05:01.000	323
BBC	2009-01-02 02:05:01.000	453

Please help me. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 00:27:07

quote:
Originally posted by lulu2792

Wrong, the result is not correct. Your sql returns:

ABC 2009-01-03 03:05:01.000 327
BBC 2009-01-03 03:05:01.000 458

I don't know why your sql remove data of old day. My requirement is:

ABC 2009-01-03 03:05:01.000 327
BBC 2009-01-03 03:05:01.000 458
ABC 2009-01-02 02:05:01.000 323
BBC 2009-01-02 02:05:01.000 453

Please help me. Thanks.

Then what do you mean by this ?

quote:
I have a change in my question. To optimize, I only want to get the last data from Realtime table of each symbol.

quote:

I will get the last data of Realtime for each symbol:


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

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 00:30:51

I'm sorry for my bad english. "I have a change in my question. To optimize, I only want to get the last data from Realtime table of each symbol." mean that: I don't need to check all data in Realtime. I only need to check the last data of Realtime for each symbol and compare with EOD. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 00:38:32

you mean don't update the existing data in EOD ? Just Insert new record into EOD ?

use the query i posted on 05/05/2010 : 22:47:26 and remove the UPDATE query

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 00:49:31

No no. Also updating & inserting. But I only interest in the last data of each symbol in Realtime table. Inserting or Updating is depending on EOD table.
Example: I get the last data of symbol 'ABC' from Realtime data:


ABC     1/3/2009 03:05:01   327

I check the the last data of symbol 'ABC' from EOD table:


ABC     1/2/2009 03:05:01   327

because '1/3/2009 03:05:01' is newer '1/2/2009 03:05:01' and the date '1/3/2009' != '1/2/2009' -> inserting.

If the the last data of symbol 'ABC' from EOD table:


ABC     1/3/2009 02:05:01   327

because '1/3/2009 03:05:01' is newer '1/3/2009 02:05:01' and the date '1/3/2009' == '1/3/2009' -> updating.

Do similar for all symbols.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 04:44:15

[code]
DECLARE @Realtime TABLE
(
Symbol varchar(3),
[Date] datetime,
Value int
)

DECLARE @EOD TABLE
(
Symbol varchar(3),
[Date] datetime,
Value int
)

INSERT INTO @Realtime
SELECT 'ABC', '1/3/2009 03:05:01', 327 UNION ALL -- this day is not existed in EOD -> inserting
SELECT 'BBC', '1/3/2009 03:05:01', 458 UNION ALL -- this day is not existed in EOD -> inserting
SELECT 'ABC', '1/2/2009 03:05:01', 326 UNION ALL -- this day is new -> updating
SELECT 'BBC', '1/2/2009 03:05:01', 454 UNION ALL -- this day is new -> updating
SELECT 'ABC', '1/2/2009 02:05:01', 323 UNION ALL
SELECT 'BBC', '1/2/2009 02:05:01', 453 UNION ALL
SELECT 'ABC', '1/2/2009 01:05:01', 313 UNION ALL
SELECT 'BBC', '1/2/2009 01:05:01', 423

INSERT INTO @EOD
SELECT 'ABC', '1/2/2009 02:05:01', 323 UNION ALL
SELECT 'BBC', '1/2/2009 02:05:01 ', 453

-- Query (UPDATE)
UPDATE e
SET [Date] = r.[Date],
Value = r.Value
FROM (
SELECT Symbol, [Date], Value,
dte = DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0),
row_no = row_number() OVER (PARTITION BY Symbol ORDER BY [Date] DESC)
FROM @EOD
) e
INNER JOIN
(
SELECT Symbol, [Date], Value,
dte = DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0),
row_no = row_number() OVER (PARTITION BY Symbol ORDER BY [Date] DESC)
FROM @Realtime
) r ON e.Symbol = r.Symbol
AND e.dte = r.dte
WHERE e.row_no = 1
AND r.row_no = 1
AND (
e.[Date] <> r.[Date]
OR e.[Value] <> r.[Value]
)

-- Query (INSERT)
INSERT INTO @EOD (Symbol, [Date], Value)
SELECT Symbol, [Date], Value
FROM (
SELECT Symbol, [Date], Value,
row_no = row_number() OVER (PARTITION BY Symbol ORDER BY [Date] DESC)
FROM @Realtime
) r
WHERE r.row_no = 1
AND NOT EXISTS
(
SELECT *
FROM @EOD x
WHERE x.Symbol = r.Symbol
AND x.[Date] = r.[Date]
)

SELECT *
FROM @EOD

/*
Symbol Date Value
------ ------------------------------------------------------ -----------
ABC 2009-01-02 02:05:01.000 323
BBC 2009-01-02 02:05:01.000 453
ABC 2009-01-03 03:05:01.000 327
BBC 2009-01-03 03:05:01.000 458

(4 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 05:58:55

Thank sir. This is the best site for my beginning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 06:01:10

you are welcome