Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

need help with applying function within Query Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 3:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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
Post #1479183
Posted Tuesday, July 30, 2013 3:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's 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)
Post #1479209
Posted Wednesday, July 31, 2013 1:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 953, Visits: 2,894
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

Post #1479675
Posted Thursday, August 1, 2013 6:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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

Post #1479902
Posted Thursday, August 1, 2013 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
Points: 10, Visits: 29
Sorry i meant Micky

thanks in advance
Post #1479915
Posted Thursday, August 1, 2013 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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
Post #1480051
Posted Thursday, August 1, 2013 1:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 953, Visits: 2,894
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

Post #1480103
Posted Friday, August 2, 2013 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
Points: 10, Visits: 29
Works Great!!! Thanks much MickyT!!!

now one more step.

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
Post #1480542
Posted Friday, August 2, 2013 1:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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


Post #1480582
Posted Friday, August 2, 2013 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
Points: 10, Visits: 29
thanks Laurie!
Post #1480594
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse