SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need help with applying function within Query


need help with applying function within Query

Author
Message
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
Very interesting. After diggin a litle more into that SQL database i found that the 2 user defined function i attached on my first post are being called by a TRIGGER :

USE [SMS_AR_Report]
GO
/****** Object: UserDefinedFunction [dbo].[udfGetLoggedItemsInsertion_DateT] Script Date: 07/30/2013 17:03:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfGetLoggedItemsInsertion_DateT]
(
@Ticks BIGINT, --The sample time in ticks
@SampleTypeID INT, --The sample type id of data to be inserted
@DateTimeNow DATETIME --The current date time, getdate does not work in udf
)
RETURNS DATETIME AS
BEGIN

--Copyright ¼ 2006 Square D / Schneider Electric. All rights reserved.
--If the sample type is logged data, we'll start with the sample date
Declare @ReturnDate as DateTime
IF @SampleTypeID <> -1
BEGIN
set @ReturnDate = dbo.udfTicksToDateTime(@Ticks)
END
ELSE
BEGIN
--If it is archived data (sample type of -1) then
--we want to start with the current date that is passed in
set @ReturnDate = @DateTimeNow
END

--Now modify the date to point to midnight of the first day of the next month.
--This ensures that ALL data for the given month will remain in the
--database for at least as long as the retention period. Some data will
--remain longer than the retention period (but by no more than a month).
--If we don't do this, then some data may be archived out almost
--immediately after it is imported.
set @ReturnDate = convert(nvarchar(25),Month(@ReturnDate)) + '/1/' + convert(nvarchar(25),Year(@ReturnDate))
set @ReturnDate = DateAdd(Month, 1, @ReturnDate)

RETURN @ReturnDate
END
GO
/****** Object: UserDefinedFunction [dbo].[udfTicksToDateTime] Script Date: 07/30/2013 17:03:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfTicksToDateTime]
(
@Ticks BIGINT
)
RETURNS DATETIME AS
BEGIN

DECLARE @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT

IF @Ticks < CONVERT(BIGINT,624511296000000000)
BEGIN
SET @Ticks = CONVERT(BIGINT,624511296000000000)
END

SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
SET @DaysBefore1753 = CONVERT(BIGINT,639905)
SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)

RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))

END
GO
/****** Object: Table [dbo].[HST_Currents] Script Date: 07/30/2013 17:03:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HST_Currents](
[Timestamp_ID] [bigint] NOT NULL,
[Device_ID] [int] NOT NULL,
[Topic_ID] [int] NOT NULL,
[Sample_Type_ID] [smallint] NOT NULL,
[Original_Value] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Trigger [TRIG_UPDATE_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[TRIG_UPDATE_HST_Currents] ON [dbo].[HST_Currents] FOR UPDATE AS IF UPDATE(ORIGINAL_VALUE) BEGIN INSERT INTO PMO_HEVENTS (TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, VALUE, ACTION) SELECT TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, ORIGINAL_VALUE, 0 FROM DELETED INSERT INTO PMO_HEVENTS (TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, VALUE, ACTION) SELECT TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, ORIGINAL_VALUE, 1 FROM INSERTED END
GO
/****** Object: UserDefinedFunction [dbo].[GetTopicGroupID] Script Date: 07/30/2013 17:03:18 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
--Copyright ⌐ 2006 Square D / Schneider Electric. All rights reserved.
CREATE FUNCTION [dbo].[GetTopicGroupID] (@Topic_ID int)
RETURNS int AS
BEGIN
DECLARE @GroupID int


SELECT @GroupID = T2.GROUP_ID
FROM TOPIC_GROUPS T1,
PARTITIONED_GROUPS T2
WHERE T1.GROUP_ID = T2.GROUP_ID
AND T1.TOPIC_ID = @Topic_ID

IF @GroupID = null
SET @GroupID = -1

RETURN @GroupID
END
GO
/****** Object: Trigger [TRIG_INSTEADOFINSERT_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[TRIG_INSTEADOFINSERT_HST_Currents] ON [dbo].[HST_Currents] INSTEAD OF INSERT AS BEGIN
-- Use left outer join to select all items in the INSERTED table that are not in the main historical table.
-- This enforces a sort of virtual primary key on the timestamp_id, device_id, and topic_id fields. This
-- INSTEAD OF trigger runs in place of the actual INSERT that was executed on the table. When the
-- INSERT statement below runs, it then triggers any AFTER INSERT triggers that are also set up for
-- this table.
DECLARE @Device_ID INT
DECLARE @Topic_ID INT
DECLARE @Timestamp_ID BIGINT
DECLARE @Sample_Type_ID SMALLINT
DECLARE @Original_Value FLOAT

DECLARE InsertList_HST_Currents CURSOR FOR SELECT * FROM INSERTED

OPEN InsertList_HST_Currents

--Get first Entry.
FETCH NEXT FROM InsertList_HST_Currents INTO @Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value

WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM HST_Currents WHERE @Timestamp_ID = Timestamp_ID AND
@Device_ID = Device_ID AND @Topic_ID = Topic_ID) = 0
BEGIN
INSERT INTO HST_Currents VALUES (@Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value)
END

FETCH NEXT FROM InsertList_HST_Currents
INTO @Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value
END
CLOSE InsertList_HST_Currents
DEALLOCATE InsertList_HST_Currents
END
GO
/****** Object: Trigger [TRIG_AFTERINSERT_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[TRIG_AFTERINSERT_HST_Currents] ON [dbo].[HST_Currents] AFTER INSERT AS
BEGIN
INSERT INTO LOGGEDITEMS (DEVICE_ID,TOPIC_ID,YEARMONTH,SAMPLE_TYPE_ID,TABLENAME,GROUP_ID,INSERTION_DATET)
SELECT DEVICE_ID,TOPIC_ID,(YEAR(dbo.udfTicksToDateTime(TIMESTAMP_ID))*100+MONTH(dbo.udfTicksToDateTime(TIMESTAMP_ID))),SAMPLE_TYPE_ID,'HST_Currents',(dbo.GetTopicGroupID(TOPIC_ID)),(dbo.udfGetLoggedItemsInsertion_DateT (TIMESTAMP_ID, SAMPLE_TYPE_ID,GETUTCDATE()))
FROM INSERTED
END
GO
/****** Object: ForeignKey [FK_HST_Currents_Devices] Script Date: 07/30/2013 17:03:17 ******/
ALTER TABLE [dbo].[HST_Currents] WITH NOCHECK ADD CONSTRAINT [FK_HST_Currents_Devices] FOREIGN KEY([Device_ID])
REFERENCES [dbo].[Devices] ([local_Device_ID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[HST_Currents] CHECK CONSTRAINT [FK_HST_Currents_Devices]
GO
/****** Object: ForeignKey [FK_HST_Currents_Topics] Script Date: 07/30/2013 17:03:17 ******/
ALTER TABLE [dbo].[HST_Currents] WITH NOCHECK ADD CONSTRAINT [FK_HST_Currents_Topics] FOREIGN KEY([Topic_ID])
REFERENCES [dbo].[Topics] ([topic_ID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[HST_Currents] CHECK CONSTRAINT [FK_HST_Currents_Topics]
GO



The part that seems intersting is:

(YEAR(dbo.udfTicksToDateTime(TIMESTAMP_ID))*100+MONTH(dbo.udfTicksToDateTime(TIMESTAMP_ID)



that code i run each time a new record is added to the table. Anyone here can help me on how to use this information to be able to convert the Timestamp_ID column to a DATETIME?

thanks
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65158 Visits: 17979
tigars39 (7/30/2013)
Anyone here can help me on how to use this information to be able to convert the Timestamp_ID column to a DATETIME?



You have three people on this thread that are willing. What we don't have is enough details to help you. You have been presented with a number of guesses. I assume that none of those are correct? Give us something that we can work with. Find a value for this Timestamp_ID AND what the corresponding datetime should be.

Here is what we know. We have a bigint with some very large values. You want us to help you turn that into a datetime. We have no idea how these large values were derived so we have little to no chance of deciphering it correctly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2908 Visits: 3318
Based on the function that you posted the following should convert your ticks.
You may want to case statement to make sure the timestamp_id is at least 624511296000000000 to match the function.

SELECT t.Timestamp_id
, DATEADD(
s,
(t.Timestamp_id % CONVERT(BIGINT,864000000000)) / CONVERT(BIGINT,10000000),
DATEADD(
d,
(t.Timestamp_id / CONVERT(BIGINT,864000000000)) - CONVERT(BIGINT,639905),
'17530101'
)
)
FROM (SELECT Timestamp_id FROM (VALUES (624511296000000000),(634956975000000000)) AS Ticks(Timestamp_id)) T


From the look of it the ticks have a datum of year 0.

Given that it appears the 19800101 is the least date that the function will return, you could simplify this further to

SELECT DATEADD(s,
(t.Timestamp_id - 624511296000000000) / 10000000,
'19800101')
FROM (SELECT Timestamp_id FROM (VALUES (624511296000000000),(634956975000000000)) AS Tick(Timestamp_id)) T


tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
Sean: dont know my friend. I was still trying to find more infos about the Timestamp_ID.
Thanks for the hints Lowell!

Chris, i think thats what i needed my friend!. Now the question is,(sorry for the noob question) how do i insert your conversion code into the following query:

SELECT     HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description, 
HST_Currents.Original_Value
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID


tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
Sorry i meant Micky :-)

thanks in advance
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
aslo i forgot to say that i am using SQL2005 so the VALUES heyword seem to be a problem in that version. WHat would be the equivalent code for SQL2005?

thanks again
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2908 Visits: 3318
tigars39 (8/1/2013)
Sean: dont know my friend. I was still trying to find more infos about the Timestamp_ID.
Thanks for the hints Lowell!

Chris, i think thats what i needed my friend!. Now the question is,(sorry for the noob question) how do i insert your conversion code into the following query:

SELECT     HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description, 
HST_Currents.Original_Value
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID


Just add it into the select clause
SELECT   
HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,
HST_Currents.Original_Value,
DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101') AS TimeStampDateTime
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID


tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
Works Great!!!Cool Thanks much MickyT!!!

now one more step. w00t

i need the converted date to be in the format: yyyy-mm-ddThh:mm:ss.mmm

i found this using google:

SELECT convert(varchar, getdate(), 126)



but still trying to figure out how to include that into my query which is now:

SELECT   
HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,
HST_Currents.Original_Value,
DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101') AS TimeStampDateTime
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID



thznks again
laurie-789651
laurie-789651
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1520 Visits: 1272
Try this:



SELECT
HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,
HST_Currents.Original_Value,
convert(varchar, DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101'), 126) AS TimeStampDateTime
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID



tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
thanks Laurie! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search