ASP code to TSQL code

Question

ASP code to TSQL code

MegaDBA

Hall of Fame

Points: 3518
More actions
December 20, 2005 at 10:11 am

#364484

Can anyone help me translate this asp code to a sql statement/ procedure?
The concept is I am eliminating duplicate records and pulling data back relevant to the end user when the AE passes the variable (ae_id).
Here's the TSQL code that pulls the dupes:
SELECT distinct b1.whole_rep_id,
b1.dba,
b1.address,
b1.city,
b1.state,
b1.zip,
b1.phone,
b1.cur_status,
b1.exclude
FROM setups.dbo.brokers b1,
setups.dbo.brokers b2
WHERE b1.whole_rep_id = @aeID
AND b1.zip = b2.zip
AND substring (b1.DBA, 1, 5) = substring (b2.DBA, 1, 5)
AND substring (b1.address, 1, 5) = substring (b2.address, 1, 5)
AND b1.zip is not null
AND b2.zip is not null
AND b1.address is not null
AND b2.address is not null
AND b1.address <> b2.address
AND b1.DBA <> b2.DBA
order by b1.address, b1.zip
Here's the ASP code that I think is looping through the recordset to find a count greater than or equal to 2. How do I do this in SQL?
Cnt=0
Do until rs_main.eof

‘do until x = 50
x = x+1
          ‘if dup_flag = “false” then
‘cnt = 0
‘end if
Broker_name = trim(rs_main(“DBA”))
Address = trim(rs_main(“address”))
City=(rs_main(“city”))
Brok_state=(rs_main(“state”))
Zip=(rs_main(“zip”))
Phone=(rs_main(“phone”))
Cur_status=(rs_main(“state”))
Brokers_id=(rs_main(“brokers_id”))
Whole_rep_id==(rs_main(“whole_rep_id”))

Var_dba=left(broker_name, 5)
Var_address = left(address, 5)
Var_zip =zip

‘------------------Checking for more than 2 occurrences-------
Set rs_count = oConn.Execute (
SET Count = (SELECT count(b1.brokers_id) as Count
          FROM dbo.brokers
          WHERE         substring (b1.DBA, 1, 5) = (‘“ & (var_dba) & “’)
AND             substring (b1.address, 1, 5) ) = (‘“ & (address) & “’)
AND             zip=) =( ‘“ & (var_dba) & “’))”)

IF rs_count.eof THEN
          Xcnt2 = “”
ELSE
          Xcnt2== rs_count(“count”
END if
Rs_count.close
IF xcnt2 <= 2 THEN

IF       not isnull(whole_rep_id) or whole_rep_id <> “” THEN

Select UI.first_name, ui.last_name, from dbo.userinfo
Where (ui.employee_id =’ “ & (whole_rep_id) & “ ‘) )”)
TIA,

Christine

Aurora

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

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

jratwork SSC-Addicted Points: 439 More actions · Answer 1

Ooph. OK, it looks like the SQL code is only looking for duplicate brokers (address and DBA (which includes zip code) are similar but not identical). Then the ASP code isn't eliminating the duplicates, it's just including the count of people (agents?) at that same location. Then, if there are 1 or 2 matching brokers, it selects the userinfo for that whole_rep_id.

I guess the question is what are you trying to do? Are you trying to create a query that returns results similar to the ASP page? Are you trying to write a report?

It isn't completely clear what the goal is. Give a little bit more info about what the query should do, and maybe a snippet of sample data, and I can help. If you don't want to share data here, feel free to send to me offline, and I'll post just the answer back here.

JR

MegaDBA Hall of Fame Points: 3518 More actions · Answer 2

Thanks!

Here's the background...I have a dB (3rd party vendor) that has no real constraints...therefore duplicate broker data is constantly being entered...like this

1. Acme Lending

2. Acme Lending Inc.

3. Acme Lending Incorporated

Since I can't make changes in the 3rd party dB(or else they won't support) I have to cleanse via stored procedure.

My job is to:

A: Find the duplicate records

B. Roll up the loans attached to the dupes to one broker record

C. Generate a report to show loan information by AE.

so in the first TSQL i am finding the brokers that are in the dB multiple times...i think

let me know if you need sample data...

THANKS A MILLION!

Aurora

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

If I read it right you're assuming that if the first 5 characters match then the brokers are duplicates? If so, have you tested that to see if it really holds up? I'd be concerned, i.e.:

Lending Tree
Lending Brokers Associates.

The first 5 characters match but it's entirely possible these are different companies.

Maybe you sort by name, take the shortest as the base name and compare to that. If they match:

Acme Lending
Acme Lenders (no match, but then maybe a difference company)
Acme Lending, Inc. (matches)

Then you roll them up.

You can pick up the matches by "Select top 1 @name=Lender Name from Lenders" then "Select * into #temp from Lenders where Lender name Like +@name+'%'. This will give you a temporary table containing all the matching records. Roll the values up and then update the base record in the original table.

MegaDBA Hall of Fame Points: 3518 More actions · Answer 4

Thanks and you're right so I will look at more characters, perhaps 10...

Now my question is, how within a function, can I return a record where the datetime field is less than today's date...whatever today's date may be...I cannot use the getdate() within a function...any ideas?

TIA,

Chris

Aurora

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

Something like:

Create function dbo.checkdate (@dt as datetime) AS
BEGIN
Declare @test-2 datetime
Select @test-2=datefield from table
If @test-2 = @dt then return 0
Return 1
END

dbo.checkdate(getdate())