July 29, 2015 at 8:50 am
I have a situation where I have to Join a table that has Credit Score from a value of 0 to to a Lookup table that I need to create that has three columns.
Min_Score Max_Score Action_Rating
80 100 Low Risk
50 79 Medium Risk
0 49 High Risk
This will be an Excel Source Table going into a Destination SQL Server Table.
Is this enough information to reply to this question?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2015 at 9:31 am
Welsh Corgi (7/29/2015)
I have a situation where I have to Join a table that has Credit Score from a value of 0 to to a Lookup table that I need to create that has three columns.Min_Score Max_Score Action_Rating
80 100 Low Risk
50 79 Medium Risk
0 49 High Risk
This will be an Excel Source Table going into a Destination SQL Server Table.
Is this enough information to reply to this question?
What question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 29, 2015 at 9:45 am
Phil Parkin (7/29/2015)
Welsh Corgi (7/29/2015)
I have a situation where I have to Join a table that has Credit Score from a value of 0 to to a Lookup table that I need to create that has three columns.Min_Score Max_Score Action_Rating
80 100 Low Risk
50 79 Medium Risk
0 49 High Risk
This will be an Excel Source Table going into a Destination SQL Server Table.
Is this enough information to reply to this question?
What question?
Weak is the force right now. The question, not seeing.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 29, 2015 at 10:38 am
SELECT
C.cs
, L.MIN_CS
, L.MAX_CS
, L.CS_ACTION
FROM CREDITSCORE AS C
LEFT OUTER JOIN LOOKUP AS L ON C.cs <= L.MAX_CS
AND C.cs >= L.MIN_CS;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 1:42 pm
I need to return as part of the SELECT Statement the Experian Score that is derived by comparing the score in the #ImportExperianDataTest Table it to a range in another Table ActionRatingDuns.
CREATE TABLE #ImportExperianDataTest (
ID int IDENTITY(1,1) NOT NULL,
ExperianScore int NOT NULL,
ActionRating VARCHAR (15))
INSERT INTO #ImportExperianDataTest (
ExperianScore )
VALUES (40),(71),(85)
SELECT *
FROM #ImportExperianDataTest
CREATE TABLE [dbo].ActionRatingDuns(
[actionRatingID] [int] IDENTITY(1,1) NOT NULL,
Min_Score int NOT NULL,
Max_Score int NOT NULL,
[actionRatingDesc] [varchar](250) NOT NULL,
CONSTRAINT [PK_actionRatingDuns] PRIMARY KEY CLUSTERED
(
[actionRatingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON
INSERT INTO ActionRatingDuns
([Min_Score],
[Max_Score],
[actionRatingDesc])
VALUES (80,100,'Low Risk'),(50,79, N'Medium Risk'),(0,49,N'High Risk')
ExperianScoreAction Rating
80-100 Low Risk
50-79 Medium Risk
0-49 High Risk
It basically Selects the ExperianScore and other columns and stores the Action rating in the #ImportExperianDataTest Table.
How can I do this?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2015 at 2:08 pm
try this for a start
SELECT
IE.ID
, IE.ExperianScore
, AR.Min_Score
, AR.Max_Score
, AR.actionRatingDesc
FROM #ImportExperianDataTest AS IE
LEFT OUTER JOIN
ActionRatingDuns AS AR ON IE.ExperianScore <= AR.Max_Score
AND IE.ExperianScore >= AR.Min_Score
edited....see posts below
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 2:14 pm
J Livingston SQL (7/29/2015)
try this for a start
SELECTIE.ID
, IE.ExperianScore
, AR.Min_Score
, AR.Max_Score
, AR.actionRatingDesc
FROM #ImportExperianDataTest AS IE
LEFT OUTER JOIN
ActionRatingDuns AS AR ON IE.ExperianScore < AR.Max_Score
AND IE.ExperianScore >= AR.Min_Score
Not sure this works completely. Shouldn't it be '<= AR.Max_Score' ?
July 29, 2015 at 2:19 pm
Lynn Pettis (7/29/2015)
J Livingston SQL (7/29/2015)
try this for a start
SELECTIE.ID
, IE.ExperianScore
, AR.Min_Score
, AR.Max_Score
, AR.actionRatingDesc
FROM #ImportExperianDataTest AS IE
LEFT OUTER JOIN
ActionRatingDuns AS AR ON IE.ExperianScore < AR.Max_Score
AND IE.ExperianScore >= AR.Min_Score
Not sure this works completely. Shouldn't it be '<= AR.Max_Score' ?
quite correct Lynn...thanks
(fat fingered...!)
my initial reply had it right (few posts back)
.....cant work out why I am so many points behind OP
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 2:26 pm
Basically what I'm trying to do is to populate the ActionRating Column in the #ImportExperianDataTest Table based on the Min_Score Column in the ActionRatingDuns Table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2015 at 2:35 pm
Welsh Corgi (7/29/2015)
Basically what I'm trying to do is to populate the ActionRating Column in the #ImportExperianDataTest Table based on the Min_Score Column in the ActionRatingDuns Table.
ok...so as long as the select statement delivers the correct results you are looking for, then change it to an UPDATE statement
along the lines of
UPDATE ImportExperianDataTest
SET
ActionRating = AR.actionRatingDesc
FROM ImportExperianDataTest
etc etc
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 29, 2015 at 2:37 pm
Thank you! 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2015 at 3:32 pm
I found out that I need to get the ID not the Description and as a subquery must JOIN with this code:
create table #tmpContractorCreditReports
(
AutoID int identity (1,1),
Contr_ID int,
CRRPT_ID_NB int,
CRRPT_RUN_DT datetime,
reporttype varchar(60),
CRRPT_DBT_CT int,
CRRPT_CR_LIN_CT int,
CRRPT_TOT_CR_LIN_CT money,
CRRPT_CURR_BAL_AMT money,
LegalBalance money,
creditscore varchar(50),
actionratingid int
)
insert into #tmpContractorCreditReports(Contr_ID,CRRPT_ID_NB,CRRPT_RUN_DT,reporttype,CRRPT_DBT_CT,CRRPT_TOT_CR_LIN_CT,CRRPT_CR_LIN_CT,creditscore,actionratingid,LegalBalance)
Select replace([Customer NUmber],'* ','') as Contr_ID,0 as CRRPT_ID_NB,[Import Date] as CRRPT_RUN_DT,
'EXP - Commercial' as reporttype,[DBT Overall] as CRRPT_DBT_CT,convert(money,[RECENT_HIGH_CR]) as CRRPT_TOT_CR_LIN_CT,
[TradeLines] as CRRPT_CR_LIN_CT,[Experian Score] as creditscore,
(Select ActionRatingID from ActionRating where ExperianRatingText = Action) as ActionRatingID, -- Replace this line with the query but can only contain 1 row
[Legal Balance] as LegalBalance
from importExperianData
I changed the table to a Text Suffix.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2015 at 4:17 pm
How do I filter the sub query to return one record from my Credit Lookup Table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2015 at 11:57 am
Is the table ActionRating in your latest query the same as the table ActionRatingDuns in your earlier table?
Actually, how about posting the DDL for the table(s) involved in your latest query, sample data for the table(s), and expected results. Also, how does this all relate to the original question you asked?
July 30, 2015 at 2:22 pm
Hi Lynn,
A Business Analyst gave me a requirement to create a new table when in fact there already is one.
I commented the sub query replaced it with a CASE Statement.
This is much simpler.
I learned from this post and if I have to do something like that I will do it again. again I will save it for future use.
--(Select ActionRatingID from ActionRating where ExperianRatingText = Action) as ActionRatingID,
CASE
WHEN [Experian Score] BETWEEN 0 and 49 THEN
5
WHEN [Experian Score] BETWEEN 50 AND 79 THEN
3
WHEN [Experian Score] BETWEEN 80 and 100 THEN
1
END AS ActionRatingID ,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply