Solved: Update from previous entry

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 2000 Forums

Transact-SQL (2000)

Solved: Update from previous entry

Author

Topic

pcock
Starting Member

12 Posts

Posted - 2009-06-24 : 01:36:39

[code]
select meterid, datetimeins, datetimerem,
case when DATEPART(yyyy , datetimeins ) = 2009
and DATEPART(mm , datetimeins ) = 1
and DATEPART(dd , datetimeins ) = 1 then datetimeins else null end as newdatetimeins
from dbo.CashBoxDataImport
order by datetimeread
[/code]
gives me a list similar to the following.

[code]
meter_id, datetimeins, datetimerem, newdatetimeins
1001 2009-02-11 07:53:00.000 2009-02-18 08:05:00.000 NULL
1012 2009-02-11 07:55:00.000 2009-02-18 08:09:00.000 NULL
1001 2009-02-18 08:10:00.000 2009-02-25 08:05:00.000 NULL
1012 2009-02-18 08:09:00.000 2009-02-25 08:07:00.000 NULL
1012 2009-02-25 08:07:00.000 2009-03-04 08:20:00.000 NULL
1001 2009-01-01 00:00:00.000 2009-03-04 08:16:00.000 2009-01-01 00:00:00.000
1012 2009-03-04 08:20:00.000 2009-03-11 07:39:00.000 NULL

[/code]
When there's a power failure the meter logs in the first day of the year on field datetimeins,
(eg, datetimeins on the 5th row should have been >= 2009-02-25 08:05:00.000 and not 2009-01-01 00:00:00.000)

What is the best way to fix this? I have over 400 meters with this problem, so I can't really update the values manually.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 01:43:12

quote:
eg, datetimeins on the 5th row should have been >= 2009-02-25 08:05:00.000 and not 2009-01-01 00:00:00.000

How do you get the should have been datetime "2009-02-25 08:05:00.000" ?

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

pcock
Starting Member

12 Posts

Posted - 2009-06-24 : 01:49:30

the last time there was any activity on meter 1001 was 2009-02-25 08:05:00.000 (row 3)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 01:54:09

[code]
select meterid, datetimeins, datetimerem,
case when datetimeins = '20090101'
then (select max(datetimerem) from dbo.CashBoxDataImport x
where x.meterid = d.meterid and x.datetimerem < d.datetimerem)
else null
end as newdatetimeins
from dbo.CashBoxDataImport d
order by datetimeread[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 01:57:10

quote:
When there's a power failure the meter logs in the first day of the year on field datetimeins

I assumed that the the meter log of 1st day of the year is without time, then you can just simply use

case when datetimeins  = '20090101'

or if you want to make it more dynamic, this will give you 1st day of current year

dateadd(year, datediff(year, 0, getdate()), 0)

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

pcock
Starting Member

12 Posts

Posted - 2009-06-24 : 02:14:36

quote:

Originally posted by khtan


select meterid, datetimeins, datetimerem,
       case when datetimeins  = '20090101'
            then (select max(datetimerem) from dbo.CashBoxDataImport x 
                  where  x.meterid = d.meterid and x.datetimerem < d.datetimerem)
            else null 
            end as newdatetimeins
from   dbo.CashBoxDataImport d
order by datetimeread

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

Thank you khtan :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources