question

Miiimy7-1570 avatar image
0 Votes"
Miiimy7-1570 asked Miiimy7-1570 commented

date issues in t-sql

Hello,
I can't find the relation between those 2 columns, can you guys help me?


82461-image.png


sql-server-transact-sql
image.png (65.9 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so, the secondValueA changes everytime the time changes.. here i'm collecting data every 30 secs
thank you

0 Votes 0 ·

i tried this (SELECT DATEDIFF(ms, (select sqlserver_start_time from
sys.dm_os_sys_info), '2021-03-29 14:10:07.159')) but ...

0 Votes 0 ·

these are data from Performance Monitor

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Miiimy7-1570 commented

It seems possible to identify an approximate relation between date and SecondValueA. Check an example:

 declare @date as datetime2 = cast('2021-03-29 14:12:37.163' as datetime2)
 declare @secondValueA bigint
    
 declare @x1 datetime2 = cast('2021-03-29 14:10:07.159' as datetime2)
 declare @x2 datetime2 = cast('2021-03-29 14:15:37.162' as datetime2)
 declare @y1 bigint = cast(-232845330 as bigint)
 declare @y2 bigint = cast(-1227785454 as bigint) + cast(0x100000000 as bigint)
    
 set @secondValueA = @y1 + (@y2 - @y1) * datediff_big(mcs, @x1, @date) / datediff_big(mcs, @x1, @x2)
 if @secondValueA > 0x7FFFFFFF set @secondValueA -= cast(0x100000000 as bigint)
    
 select @date as [date], @secondValueA as SecondValueA

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hello!
thank you but is there a way to find 'SecondValueA' with 'date'?
The thing is i collect data every 30 sec, and what i'm searching for is one function f(date) = SecondValueA.

0 Votes 0 ·

The first line is a sample input date. Then the code evaluates the value of SecondValueA for this date. Do you need the inverse calculation (getting date from given SecondValueA)?

0 Votes 0 ·

yes but you're also using x1,x2,y1,y2. It seems like you needed these values to obtain SecondValueA for the given date. I don't understand but thank you though

0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered Miiimy7-1570 commented

Hi @Miiimy7-1570,

Every 30 seconds SecondvalueA increases by about 299930364:

     select 67219244-(-2328453330)
     select 367172064-67219244
     select 667144628-367172064
     select 967261916-667144628
     select 1267192280-967261916

82606-image.png

I'm not sure if the relationship between the two columns you are looking for is a direct link between the data between the two columns.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (5.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hello!

thank you but what about negative values? i'm looking for a function f(date) = secondValueA

0 Votes 0 ·