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

Possible to determine the datetime range of loaded data to staging table? Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 10:33 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 3:48 PM
Points: 485, Visits: 449
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()))
Post #1400792
Posted Friday, December 28, 2012 12:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400808
Posted Friday, December 28, 2012 1:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1400826
Posted Friday, December 28, 2012 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400831
Posted Friday, December 28, 2012 2:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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!
Post #1400841
Posted Friday, December 28, 2012 8:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 3:48 PM
Points: 485, Visits: 449
My solution handles too
Post #1400934
Posted Wednesday, January 2, 2013 12:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1401682
Posted Wednesday, January 2, 2013 12:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1401683
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse