How do I get the latest column value from history? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How do I get the latest column value from history?

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-23 : 13:36:36

Hello,

I am using SQL 2000. I have the foll commad and the result set is as follows. I need to get data for 2/22/06 rather than the other dates.

Thanks in advance
sqlnovice123

select * from dbArchive..EMSecurityModelValSaveD where ModelID = 1 AND SecurityID = 60952

20060217 1 NULL 23559 4556 60952 -1.8737706198803314E-2 2006-02-16 00:00:00.000 2006-02-17 04:34:07.213
20060220 1 NULL 23559 4556 60952 -1.8824870098869399E-2 2006-02-17 00:00:00.000 2006-02-18 04:42:49.737
20060221 1 NULL 23559 4556 60952 -1.8973066282962407E-2 2006-02-20 00:00:00.000 2006-02-21 03:10:07.203
20060222 1 NULL 23559 4556 60952 -1.9135545221188051E-2 2006-02-21 00:00:00.000 2006-02-22 04:25:07.217

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 13:40:06

WHERE YourDateColumn >= '2/22/2006' AND YourDateColumn < '2/23/2006'

Tara Kizer
aka tduggan

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-23 : 14:43:07

Tara,
Thanks for your response. If I were to do so programmatically without hardcoding the data, should I then declare a variable for the ArchiveDataDate column and then do the filtering?

Thanks
sqlnovice123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 15:13:14

Yes.

DECLARE @SomeDateVar datetime
SET @SomeDateVar = '02/22/2006'

SELECT...
WHERE YourDateColumn >= @SomeDateVar AND YourDateColumn < DATEADD(d, 1, @SomeDateVar)

Tara Kizer
aka tduggan

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-23 : 17:05:06

Hello,

I tried the following:

DECLARE @ArchiveDataDate int
SET @ArchiveDataDate = 20060222
select * from dbArchive..EMSecurityModelValSaveD where ModelID = 1 AND SecurityID = 60952
and ArchiveDataDate >= @ArchiveDataDate and ArchiveDataDate < DATEADD(d, 1, @ArchiveDataDate)

The column ArchiveDataDate in the schema definition is an INT. I get the following error:

Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.

Thanks in advance
sqlnovice123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 17:10:58

Convert your ArchiveDataDate to datetime. It should not be an int. You'll encounter lots of problems if you stick with int.

Tara Kizer
aka tduggan

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-23 : 23:04:40

Hello,

I tried converting the date column to datetime and still get the same error. The date column is defined as an int in the table. Since the query "select * from dbArchive..EMSecurityModelValSaveD where ModelID = 1 AND SecurityID = 60952" returns 4 rows of data, how can I get data for the previous business day? For example the data returned is current day's edge plus three other day's edge and I would like to get data for 2/22/06 which is the lates previous business day as the other two dates are 2/18/06 and 2/21/06?

ArchiveDataDate ModelID ExposureGroupID RelativeUniverseID SecurityGroupID SecurityID ModelValue DataDate LastUpdate

20060218 1 NULL 23559 4556 60952 -1.8737706198803314E-2 2006-02-16 00:00:00.000 2006-02-17 04:34:07.213
20060221 1 NULL 23559 4556 60952 -1.8824870098869399E-2 2006-02-17 00:00:00.000 2006-02-18 04:42:49.737
20060222 1 NULL 23559 4556 60952 -1.8973066282962407E-2 2006-02-20 00:00:00.000 2006-02-21 03:10:07.203
20060223 1 NULL 23559 4556 60952 -1.9135545221188051E-2 2006-02-21 00:00:00.000 2006-02-22 04:25:07.217

DECLARE @ArchiveDataDate datetime
SET @ArchiveDataDate = '02/22/2006'
select * from intldbArchive..GOSecurityGroupModelValSaveD where ModelID = 1 AND SecurityID = 60952
--select * from OptMod..GO_SecurityGroupModelValues where ModelID = 1 AND SecurityID = 60952
AND convert(datetime, ArchiveDataDate) >= @ArchiveDataDate
AND convert(datetime, ArchiveDataDate) < DATEADD(d, 1, @ArchiveDataDate)

Thanks again in advance!
sqlnovice123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 23:27:59

quote:
"I would like to get data for 2/22/06"

This will give you the record highlighted in red

DECLARE @ArchiveDataDate int
SET @ArchiveDataDate = 20060222

select * 
from   intldbArchive..GOSecurityGroupModelValSaveD 
where  ModelID = 1 
AND    SecurityID = 60952
AND    ArchiveDataDate = @ArchiveDataDate

Your Data :

20060218 1 NULL 23559 4556 60952 -1.8737706198803314E-2 2006-02-16 00:00:00.000 2006-02-17 04:34:07.213
20060221 1 NULL 23559 4556 60952 -1.8824870098869399E-2 2006-02-17 00:00:00.000 2006-02-18 04:42:49.737
20060222 1 NULL 23559 4556 60952 -1.8973066282962407E-2 2006-02-20 00:00:00.000 2006-02-21 03:10:07.203
20060223 1 NULL 23559 4556 60952 -1.9135545221188051E-2 2006-02-21 00:00:00.000 2006-02-22 04:25:07.217

If you need to retrieve records for range of ArchiveDataDate, you will have to convert your integer date to datetime, then use dateadd to calculate the date that you required.
You can use the following method to convert from integer date to datetime and vice versa.

declare
	@intdate	int,
	@dt		datetime
select 	@intdate	= 20060221,
	@dt	= getdate()
select 	date_time = dateadd(day, (@intdate % 100) - 1, dateadd(month, (@intdate % 10000 / 100) - 1, dateadd(year, (@intdate / 10000) - 1900, 0))),
	int_date  = year(@dt) * 10000 + month(@dt) * 100 + day(@dt)

You might also want to create functions to do this

----------------------------------
'KH'
It is inevitable

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-24 : 00:23:54

KH,

Thanks a lot for your response. This is exactly what would be needed. I would store previuos business's day's edge in a temp table called yesterday and the current edge data in a table called today. Since I need to display the previous, current and the difference in a web page, where te difference = (prev edge - curren edge)/(previous edge).

My stored proc should probably have the Model ID, SecurityID, ArchiveDataDate as parameters in addition to other parameters.

Also, if I need to round the values to 4 decimal places, what can I use?

Example, if previous edge value is -1.8973066282962407E-2, I would need to display the ABS value with 4 decimal places. Can I use the ROUND or TRUNCATE function or is there another way to format the number to 4 decimal places?

Thanks in advance!
sqlnovice123
Is it :

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 00:25:37

"The date column is defined as an int in the table"

I would recommend that you review this!

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 00:33:05

"Also, if I need to round the values to 4 decimal places, what can I use?"

select round(ABS, 4)
or
select convert(numeric(14,4), ABS)

----------------------------------
'KH'
It is inevitable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 01:03:15

quote:
Originally posted by tkizer

Yes.

DECLARE @SomeDateVar datetime
SET @SomeDateVar = '02/22/2006'

SELECT...
WHERE YourDateColumn >= @SomeDateVar AND YourDateColumn < DATEADD(d, 1, @SomeDateVar)

Tara Kizer
aka tduggan

To work in all servers regardless of the date setting, I would prefer using universal format

DECLARE @SomeDateVar datetime
SET @SomeDateVar = '20060222' -- or '2006-02-22'

In your example, If the date inputted is 02/12/2006, then the server with dmy format will treat that as 2nd dec, 2006 and server with mdy format will treat as 12th feb, 2006

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-24 : 13:03:40

quote:
Originally posted by madhivanan

quote:
Originally posted by tkizer

Yes.

DECLARE @SomeDateVar datetime
SET @SomeDateVar = '02/22/2006'

SELECT...
WHERE YourDateColumn >= @SomeDateVar AND YourDateColumn < DATEADD(d, 1, @SomeDateVar)

Tara Kizer
aka tduggan

To work in all servers regardless of the date setting, I would prefer using universal format

DECLARE @SomeDateVar datetime
SET @SomeDateVar = '20060222' -- or '2006-02-22'

In your example, If the date inputted is 02/12/2006, then the server with dmy format will treat that as 2nd dec, 2006 and server with mdy format will treat as 12th feb, 2006

Madhivanan

Failing to plan is Planning to fail

I know. It doesn't need to be explained to me. I was answering the question.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources