TESTINg against destiantion

Question

Post reply

TESTINg against destiantion

komal145

SSCrazy Eights

Points: 9918
More actions
October 14, 2011 at 9:50 am

#390624

Is there any way that I can include My update staement , in my select? I know its weird quesion , but I have to do this to test
source against destination, to check if that column is populating the right data against the source.
Example of My source query:( It has to be tested with target table with same data : Rev table)
select * into ##temp_revenue from (SELECT
Row_number ( ) OVER(PARTITION BY S.Case_Number_ID ORDER BY S.Import_ID DESC) AS 'Rank'
,D.CASe_Number_Id
,D.System_ID
,S.Account_Id
,D.Policy_Num
,S.Accrual_Month AS Revenue_Month
, dateadd(month, datediff(month, 0, S.Accrual_Month ) - 1, 0) AS LastRevenueMonth
, CurrentMonth
,S.AccruedAmount
,NULL AS LAStMonthAccruedAmount
,NULL AS Revenue
,D.Renewal_Ind
,D.CASh_Ind
,D.JE_Account_Number
,GETDATE() AS Created_Date
,GETDATE() AS Updated_Date
FROM [ODS].[A_D] D
JOIN [ODS].[AC_Snapshot] S
ON S.CASe_Number_Id=D.CASe_Number_Id
)A
where Rank=1
My Update statement:
UPDATE T
SET LAStMonthAccruedAmount= ISNULL(S.AccruedAmount,0)
FROM ##temp_revenue T
JOIN [ODS].[AC_Snapshot] S
ON T.CASe_Number_Id= S.CASe_Number_Id
AND S.Accrual_Month='2011-10-01 00:00:00.000'
WHERE S.Accrual_Month=LAStRevenueMonth

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

HUH?

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Try LEFT JOINing [ODS].[AC_Snapshot] again (with a different alias).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden