better way to rewrite this code

Question

better way to rewrite this code

klineandking

SSCrazy

Points: 2570
More actions
February 22, 2010 at 3:48 pm

#386366

good afternoon guys am trying to find a better way to rewrite this code, i inherited it from some one else, any help would be welcome
declare @data_refresh datetime
set @data_refresh=convert(datetime,convert(varchar(10),getdate(),101))
select n.propertyid, n.loanno, n.redcid, n.auctionid, n.sellerprimaryid, n.status,
'A' as Tran_cd, '01' as Tran_reason, getdate() as data_date
from
(select *
from dw.dbo.RCP_seller_breakdown_history h
where effective_to_dt>=@data_refresh
) n
left outer join
(select *
from dw.dbo.RCP_seller_breakdown_history h
where effective_from_dt<@data_refresh
) h on h.propertyid=n.propertyid

Viewing 6 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Also, check out the link in my signature for common date routines for a faster way to strip the time off of a datetime variable. However, since you're using SS 2008 (at least that is the forum you posted in), you can just:

declare @data_refresh DATE = GetDate()

Without table definitions, sample data, and expected results based on that sample data, that's about the best that we can do for you.

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

sturner SSC-Insane Points: 22411 More actions · Answer 2

well looks like this might benefit from a where clause.

The probability of survival is inversely proportional to the angle of arrival.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

klineandking (2/22/2010)
good afternoon guys am trying to find a better way to rewrite this code, i inherited it from some one else, any help would be welcome
declare @data_refresh datetime
set @data_refresh=convert(datetime,convert(varchar(10),getdate(),101))
select n.propertyid, n.loanno, n.redcid, n.auctionid, n.sellerprimaryid, n.status,
'A' as Tran_cd, '01' as Tran_reason, getdate() as data_date
from
(select *
from dw.dbo.RCP_seller_breakdown_history h
where effective_to_dt>=@data_refresh
) n
left outer join
(select *
from dw.dbo.RCP_seller_breakdown_history h
where effective_from_dt<@data_refresh
) h on h.propertyid=n.propertyid

Someone's having you on, mate. All this query can do is return singles or duplicates of rows which can be returned by the first part of the query (in bold). Is there something down the line which counts the dupes?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

klineandking SSCrazy Points: 2570 More actions · Answer 4

well thanks for the tip, the guy whom i inherited the code from has a weird way or writing code which is crazy i have been trying to make it simpler for someone else to understand when read so thats why i asked for help thank you ll give it a shot and see the outcome

Paul White SSC Guru Points: 150467 More actions · Answer 5

klineandking (2/22/2010)
good afternoon guys am trying to find a better way to rewrite this code, i inherited it from some one else, any help would be welcome

I can help with this bit:

declare @data_refresh datetime
set @data_refresh=convert(datetime,convert(varchar(10),getdate(),101))

Isn't this better...?

-- This removes the time portion of the current date and time

-- More efficient than converting to a VARCHAR and back, also potentially SARGable

DECLARE @data_refresh DATETIME2 = DATEADD(DAY, DATEDIFF(DAY, 0, SYSDATETIME()), 0);

Paul 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi