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


Possible to determine the datetime range of loaded data to staging table?


Possible to determine the datetime range of loaded data to staging table?

Author
Message
SatishAyyar
SatishAyyar
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 530
You may write the expression something like this in the conditional split

(DT_DBTIMESTAMP)(event_d + " " + event_t) >= DATEADD("HH",-73,GETDATE()) && (DT_DBTIMESTAMP)(event_d + " " + event_t) <= DATEADD("HH",8,(DT_DBDATE)(GETDATE()))
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50815 Visits: 21152
Here's my pseudo-code expanded a bit to accommodate the start and end date bits:

declare @StartDate datetime
,@EndDate datetime

set @EndDate = dateadd(hour, 8, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, - 8, getdate()))))
set @StartDate = dateadd(hour, - 73, @EndDate)

select col1
,col2
from table
where [convert varchar date and time to datetime] between @StartDate
and @EndDate



That's how it works in T-SQL. Just need to convert that to Oracle.

--Edit: fixed typo.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 180
Thanks to both of you, currently out of office cant test our both solutions. Will test as soon as i get back. Btw from what i read from both your solutions, i wonder if you all took care of scenario whereby package i executed before or at 8am. Which means this examples:

Datetime when package run: 26/12/12 0759
Startdate: 22/12/12 0700
Enddate: 25/12/12 0800

Datetime when package run: 26/12/12 0800
Startdate: 22/12/12 0700
Enddate: 25/12/12 0800

Just clarifying. Thanks alot
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50815 Visits: 21152
i wonder if you all took care of scenario whereby package i executed before or at 8am.


My solution handles that.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 180
Phil Parkin (12/28/2012)
i wonder if you all took care of scenario whereby package i executed before or at 8am.


My solution handles that.


Thanks! :-D
SatishAyyar
SatishAyyar
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 530
My solution handles too
10e5x
10e5x
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 180
SatishAyyar (12/28/2012)
My solution handles too


Hi satish,

I tried out your solution, it does not met my requirement. I guess your solution take 73 hrs before my current sys date time. This is not what i want. The start time should be 73 hours ago from the end time while the end time is the nearest 8am(past). Thanks anyway. Btw if u are not sure what i am talking about, kindly study the scenarios. Thanks for the input
10e5x
10e5x
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 180
Phil Parkin (12/28/2012)
i wonder if you all took care of scenario whereby package i executed before or at 8am.


My solution handles that.


From what i see, phil your solution will definitely work. However i prefer doing it in conditional split for this. Do not how to convert ur tsql into conditional split expression
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