DTS - Losing Transformations

[Schnappa]

All

I had a hard coded date format DTS which I updated ( with help from TT colleagues) to include a where clause that would bring back the start and end of the previous month in order to bring back 'last months' transactions. The hard coded version worked of course, but when I use the following coding, I get an error and lose all my source transformation data.

Can anyone advise me where I may have gone wrong.

Thanks

Gezza

--------------------------------------------------------

DECLARE @tStartDate datetime
DECLARE @tEndDate datetime
SET @tStartDate = DATEADD(mm, DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of previous month

SET @tEndDate = DATEADD(mm, 1 + DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of current month

SELECT

dp_history.ptid AS DPTranNumber,
dp_history.acct_no AS AccountID,
dp_history.tran_code AS TranCode,
dp_history.amt AS TranAmount,
dp_history.effective_dt AS TranDate,
ISNULL(dp_history.long_desc,dp_history.description) AS TranNarration,
ad_gb_rsm.name AS OperatorName,
ad_gb_branch.short_name AS TranBranch,
pc_origin.description AS TranOriginDesc,
ad_gb_channel.short_desc AS TranChannelDesc


FROM

(((tstUCU.dbo.dp_history dp_history LEFT OUTER JOIN tstUCU.dbo.ad_gb_rsm ad_gb_rsm ON dp_history.empl_id=ad_gb_rsm.employee_id) LEFT OUTER JOIN tstUCU.dbo.pc_origin pc_origin ON dp_history.origin_id=pc_origin.origin_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_channel ad_gb_channel ON dp_history.channel_id=ad_gb_channel.channel_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_branch ad_gb_branch ON ad_gb_rsm.branch_no=ad_gb_branch.branch_no


WHERE dp_history.effective_dt>=@tStartDate AND
dp_history.effective_dt < @tEndDate

-----------------------------------------------------------

Just as an aside, I also had a previous version of the above where I did not declare variables but used the follwoing where clause

dp_history.effective_dt>=dateadd(mm,-1,dateadd(dd,((datepart(dd,getdate())*-1)+1),getdate())) AND dp_history.effective_dt<= dateadd(dd,((datepart(dd,getdate())*-1)),getdate())

Unfortunately I seem to be missing about 7k transactions from the source table. I am thinking that it could be the time portion of the formula above. It will bring back the time portion to be the time portion of the getdate() funtion, but all transactions in the source system have a time set of dd/mm/yyyy 12:00:00AM (Sybase database)- so I think I miss out on those posted on the 1st prior and the lst day post the time portion.

I hope I make sense.

Gezza

 

[TomKane]

Hi,

Are you putting all this code into the SQL properties task? You would be better off putting that stuff into a stored procedure.

In relation to your other issue with the time portion - I have a piece of code here that may help:

SELECT dateadd(d,0,dateadd(m,0,convert(varchar,getdate()-2,110)))

The above SELECT returns GETDATE() without the time portion.

Hope this helps,
Tom

 

[AlexCuse]

are you certain that this query returns records? I am not sure I'm understanding you.

Did it work when you used the second where clause mentioned in the bottom part of your post?

dp_history.effective_dt>=dateadd(mm,-1,dateadd(dd,((datepart(dd,getdate())*-1)+1),getdate())) AND dp_history.effective_dt<= dateadd(dd,((datepart(dd,getdate())*-1)),getdate())

As far as the records you are missing, try doing this with your dates:

declare @today datetime
declare @tStartDate datetime
declare @tEndDate datetime

set @today = dateadd(dd, datediff(dd, 0, getdate()), 0)


set @tStartDate = DATEADD(mm, DATEDIFF(mm, 0, DateAdd(mm,-1,@today)), 0) -- Beginning of previous month

set @tEndDate =  DATEADD(mm, 1 + DATEDIFF(mm, 0, DateAdd(mm,-1,@today)), 0) -- Beginning of current month


select @tStartDate
select @tEndDate

Hope this helps, and please reply with some clarification on the first part.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Schnappa]

Thanks to you both for your replies. Being close to midnight here in Perth, I will have a look at both your suggestions, and reply when I get into work in the morning.

I appreciate your help.

Cheers

Gezza

 

[AlexCuse]

Schnappa - No problem. Post back when you have had a chance to look in your package for answers to questions (and test suggestions). Get some sleep!

TomKane - have a look at this:

dateadd(dd, datediff(dd, 0, getdate()), 0)

It does not exactly strip the time component out, but it returns zeroes in it, which has been sufficient for every purpose I have needed to use it for. It would probably be faster on large tables than what you are using.



A wise man once said
"The only thing normal about database guys is their tables".

 

[TomKane]

Thanks Alex. Much appreciated.

 

[Schnappa]

Hi Alex and Tom

Apologies, but I haven;t had the chance to check out your solutions as yet. I have been working on another project all day today, however I will ensure some free time to work through this issue tomorrow.

Thanks again for your help thus far.

Cheers

Gezza