help needed with datetimes

Question

help needed with datetimes

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

NOTE: You need the #tmp here, it's not the source data build. It works off the sample data you gave us earlier. What this does is basically create a row_number() table and then manipulates the smaller data set to find the results, then links back to your primary data.

DROP TABLE #tmp

GO

CREATE TABLE #tmp

(RowIDINT IDENTITY(1, 1) NOT NULL,

ChangeIDINT,

refotsidINT,

OTTimeDATETIME,

DateChanged DATETIME,

DT_DiffBIGINT -- Stores difference in minutes.

)

-- Order these by their grouping (refOTSid) and then by when they occurred (DateChanged)

-- this is out of order of 'ChangeID'.

INSERT INTO #tmp (ChangeId, refOTSid, OTTime, DateChanged)

SELECT

ChangeId, refOTSid, OTTime, DateChanged

FROM

mytable

ORDER BY

refOTSid, DateChanged

CREATE CLUSTERED INDEX idx_tmp ON #tmp ( RowID)

-- Need a fake zero row.

SET IDENTITY_INSERT #tmp ON

INSERT INTO #tmp ( RowID, ChangeID, refotsid, OTTIME, DATECHANGED)

VALUES ( 0, 0, 0, '1/1/1900', '1/1/1900')

SET IDENTITY_INSERT #tmp OFF

SELECT * FROM #tmp

UPDATEt1

SETDT_Diff = CASE WHEN t1.refotsid <> t2.refotsid THEN DATEDIFF( n, t1.OTTime, t1.DateChanged)

ELSE DATEDIFF( n, t2.DateChanged, t1.DateChanged) END

FROM

#tmp AS t1

JOIN

#tmp AS t2

ONt1.RowID = t2.RowID + 1

-- Now that we've got our dates, drop row 0.

DELETE FROM #tmp WHERE RowID = 0

-- Clean up our optimization a bit...

DROP INDEX #tmp.idx_tmp

CREATE CLUSTERED INDEX idx_tmp ON #tmp ( ChangeID)

-- Now linkback to the original table

SELECT

mt.*,

t.DT_Diff AS xyz

FROM

mytable AS mt

LEFT JOIN

#tmp AS t

ONmt.ChangeID = t.ChangeID

Your Clustered Indexes won't allow for us to mess with mytable directly, so we'll go here as a workaround. A Quirky/Serial update might also do the trick, but this is quick and clean. If the performance is horrendous, come back and we'll see if we can't come up with a faster result for you.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95461 More actions · Answer 2

Craig Farrell (11/1/2010)
There's a couple of different techniques and approaches to this and I don't want to give you an over-complicated one if unnecessary. You've posted in the 7.0/2k forums. Are you on SQL 2k? Or are you on 2k5/2k8? The difference is astounding, and I want to make sure.

What determines the order that the rows need to be compared in?

Is there an identity column on this table?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

WayneS (11/1/2010)
What determines the order that the rows need to be compared in?
Is there an identity column on this table?

See above, islanded on the ostid..something and then sorted by the CreatedDate. 🙂

EDIT: Ah, this field: refOTSid

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

naidu4u SSC Eights! Points: 981 More actions · Answer 4

let me try to analyze this and implement with my table...

Thanks a lot for your help...

naidu4u SSC Eights! Points: 981 More actions · Answer 5

Craig Farrell (11/1/2010)
NOTE: You need the #tmp here, it's not the source data build. It works off the sample data you gave us earlier. What this does is basically create a row_number() table and then manipulates the smaller data set to find the results, then links back to your primary data.
DROP TABLE #tmp
GO
CREATE TABLE #tmp
(RowIDINT IDENTITY(1, 1) NOT NULL,
ChangeIDINT,
refotsidINT,
OTTimeDATETIME,
DateChanged DATETIME,
DT_DiffBIGINT -- Stores difference in minutes.
)
-- Order these by their grouping (refOTSid) and then by when they occurred (DateChanged)
-- this is out of order of 'ChangeID'.
INSERT INTO #tmp (ChangeId, refOTSid, OTTime, DateChanged)
SELECT
ChangeId, refOTSid, OTTime, DateChanged
FROM
mytable
ORDER BY
refOTSid, DateChanged
CREATE CLUSTERED INDEX idx_tmp ON #tmp ( RowID)
-- Need a fake zero row.
SET IDENTITY_INSERT #tmp ON
INSERT INTO #tmp ( RowID, ChangeID, refotsid, OTTIME, DATECHANGED)
VALUES ( 0, 0, 0, '1/1/1900', '1/1/1900')
SET IDENTITY_INSERT #tmp OFF
SELECT * FROM #tmp
UPDATEt1
SETDT_Diff = CASE WHEN t1.refotsid <> t2.refotsid THEN DATEDIFF( n, t1.OTTime, t1.DateChanged)
ELSE DATEDIFF( n, t2.DateChanged, t1.DateChanged) END
FROM
#tmp AS t1
JOIN
#tmp AS t2
ONt1.RowID = t2.RowID + 1
-- Now that we've got our dates, drop row 0.
DELETE FROM #tmp WHERE RowID = 0
-- Clean up our optimization a bit...
DROP INDEX #tmp.idx_tmp
CREATE CLUSTERED INDEX idx_tmp ON #tmp ( ChangeID)
-- Now linkback to the original table
SELECT
mt.*,
t.DT_Diff AS xyz
FROM
mytable AS mt
LEFT JOIN
#tmp AS t
ONmt.ChangeID = t.ChangeID
Your Clustered Indexes won't allow for us to mess with mytable directly, so we'll go here as a workaround. A Quirky/Serial update might also do the trick, but this is quick and clean. If the performance is horrendous, come back and we'll see if we can't come up with a faster result for you.

I have a small question "as I am not a SQL developer I think dont the rights to create clustered index"

Please help me giving some suggestion...

I am assuming my code to be like this....

select mt.* , t.DT_diff as XYZ from

mytable as mt

left join #tmp t

on mt.changeid=t.changeid

-- in the place of #tmp I need to get Rowid, Changeid, refOTSid, OTTime, DateChanged, DT_Diff as output.

and also I think I will not be having the permissions to set identity_insert ON or OFF...

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Naidu,

Note that it's all on the #tmp table, which is a temporary build in the tempdb, not a permanent structure. You should have the rights to run both that and identity_insert on/off.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

naidu4u SSC Eights! Points: 981 More actions · Answer 7

Can you help me structuring this #tmp table...

This is my root and to which I need to add the rows column...

select k.ChangeId, k.refOTSid, k.OTtime, k.DateClosed, k.DateChanged, rowid from

(select top 100 percent a.* , b.OTtime, b.DateClosed

from X a

join Y b

on a.refOTSid=b.eventid

order by a.refeventid, a.DateChanged )k

Please help me how to insert the row column in this...

I am unable to structure the code, I mean its becoming really difficult to insert the row column in this tmp table...(by using views since I can really insert a table into the database)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

naidu4u (11/2/2010)
I am unable to structure the code, I mean its becoming really difficult to insert the row column in this tmp table...(by using views since I can really insert a table into the database)

You can't do this in a VIEW, you need to do it in a PROC.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

naidu4u SSC Eights! Points: 981 More actions · Answer 9

naidu4u

SSC Eights!

Points: 981

November 3, 2010 at 8:09 am

#1246010

thanks a lot Craig for all your help...