February 14, 2019 at 8:23 pm
i have table as below:
country code
australia 55555
australia 55555
australia checklater
ukraine 33333
ukraine checklater
ukraine checklater
ukraine 33333
america 22222
i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.
Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222
February 15, 2019 at 8:30 am
girl_bj - Thursday, February 14, 2019 8:23 PMi have table as below:
country code
australia 55555
australia 55555
australia checklater
ukraine 33333
ukraine checklater
ukraine checklater
ukraine 33333
america 22222i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.
Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222
First, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code. On that assumption, try this:CREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);
INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222');
SELECT *
FROM #COUNTRY_CODES;
WITH CODE_VALUES AS (
SELECT DISTINCT country, code
FROM #COUNTRY_CODES
WHERE code <> 'checklater'
)
UPDATE CC
SET CC.code = CV.code
FROM #COUNTRY_CODES AS CC
INNER JOIN CODE_VALUES AS CV
ON CC.country = CV.country;
SELECT *
FROM #COUNTRY_CODES;
DROP TABLE #COUNTRY_CODES;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2019 at 9:02 am
sgmunson - Friday, February 15, 2019 8:30 AMFirst, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code. On that assumption, try this:CREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222');SELECT *
FROM #COUNTRY_CODES;WITH CODE_VALUES AS (
SELECT DISTINCT country, code
FROM #COUNTRY_CODES
WHERE code <> 'checklater'
)
UPDATE CC
SET CC.code = CV.code
FROM #COUNTRY_CODES AS CC
INNER JOIN CODE_VALUES AS CV
ON CC.country = CV.country;SELECT *
FROM #COUNTRY_CODES;DROP TABLE #COUNTRY_CODES;
WITH CODE_VALUES AS
(
SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
FROM #COUNTRY_CODES CC
)
UPDATE CV
SET cv.code = cv.countrycode
FROM CODE_VALUES AS CV
WHERE cv.code = 'checklater';
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 24, 2019 at 8:14 pm
drew.allen - Friday, February 15, 2019 9:02 AMThis runs faster on my test runs, because it only requires one scan of the table and one sort.WITH CODE_VALUES AS
(
SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
FROM #COUNTRY_CODES CC
)
UPDATE CV
SET cv.code = cv.countrycode
FROM CODE_VALUES AS CV
WHERE cv.code = 'checklater';Drew
CREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);
INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222'),
-----------new condition-------------
('nigeria', 'checklater'),
('nigeria', 'checklater'),
('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')
SELECT *
FROM #COUNTRY_CODES;
i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.
Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)
Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222
nigeria NEW
nigeria NEW
korea 66666
korea 66666
korea 66666
korea 66666
February 25, 2019 at 12:24 pm
girl_bj - Sunday, February 24, 2019 8:14 PMCREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222'),-----------new condition-------------
('nigeria', 'checklater'),
('nigeria', 'checklater'),('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')SELECT *
FROM #COUNTRY_CODES;i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.
Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222
nigeria NEW
nigeria NEW
korea 66666
korea 66666
korea 66666
korea 66666
The first one is a simple change to the query that I provided. If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results. Try it for yourself, and post your changes here.
The second one isn't fully defined. Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 25, 2019 at 6:44 pm
drew.allen - Monday, February 25, 2019 12:24 PMThe first one is a simple change to the query that I provided. If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results. Try it for yourself, and post your changes here.The second one isn't fully defined. Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?
Drew
I did something like this for first one.
WITH CODE_VALUES AS
(
SELECT cc.country, cc.code
,case when MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) is null then 'NEW'
else MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) end AS countrycode
FROM #COUNTRY_CODES CC
)
UPDATE CV
SET cv.code = cv.countrycode
FROM CODE_VALUES AS CV
WHERE cv.code = 'checklater';
for the second condition im not sure how to apply it
('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')
if multiple code found 66666 then apply all as 66666, if multiple code found 22222 then all apply to 22222.
February 26, 2019 at 9:54 am
girl_bj - Monday, February 25, 2019 6:44 PMdrew.allen - Monday, February 25, 2019 12:24 PMgirl_bj - Sunday, February 24, 2019 8:14 PMdrew.allen - Friday, February 15, 2019 9:02 AMsgmunson - Friday, February 15, 2019 8:30 AMgirl_bj - Thursday, February 14, 2019 8:23 PMi have table as below:
country code
australia 55555
australia 55555
australia checklater
ukraine 33333
ukraine checklater
ukraine checklater
ukraine 33333
america 22222i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.
Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222First, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code. On that assumption, try this:
CREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222');SELECT *
FROM #COUNTRY_CODES;WITH CODE_VALUES AS (
SELECT DISTINCT country, code
FROM #COUNTRY_CODES
WHERE code <> 'checklater'
)
UPDATE CC
SET CC.code = CV.code
FROM #COUNTRY_CODES AS CC
INNER JOIN CODE_VALUES AS CV
ON CC.country = CV.country;SELECT *
FROM #COUNTRY_CODES;DROP TABLE #COUNTRY_CODES;
This runs faster on my test runs, because it only requires one scan of the table and one sort.WITH CODE_VALUES AS
(
SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
FROM #COUNTRY_CODES CC
)
UPDATE CV
SET cv.code = cv.countrycode
FROM CODE_VALUES AS CV
WHERE cv.code = 'checklater';Drew
CREATE TABLE #COUNTRY_CODES (
country varchar(10),
code varchar(10)
);INSERT INTO #COUNTRY_CODES (country, code)
VALUES ('australia', '55555'),
('australia', '55555'),
('australia', 'checklater'),
('ukraine', '33333'),
('ukraine', 'checklater'),
('ukraine', 'checklater'),
('ukraine', '33333'),
('america', '22222'),-----------new condition-------------
('nigeria', 'checklater'),
('nigeria', 'checklater'),('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')SELECT *
FROM #COUNTRY_CODES;i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.
Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)Expected result:
country code
australia 55555
australia 55555
australia 55555
ukraine 33333
ukraine 33333
ukraine 33333
ukraine 33333
america 22222
nigeria NEW
nigeria NEW
korea 66666
korea 66666
korea 66666
korea 66666The first one is a simple change to the query that I provided. If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results. Try it for yourself, and post your changes here.
The second one isn't fully defined. Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?
Drew
I did something like this for first one.
WITH CODE_VALUES AS
(
SELECT cc.country, cc.code
,case when MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) is null then 'NEW'
else MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) end AS countrycode
FROM #COUNTRY_CODES CC
)
UPDATE CV
SET cv.code = cv.countrycode
FROM CODE_VALUES AS CV
WHERE cv.code = 'checklater';for the second condition im not sure how to apply it
('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')if multiple code found 66666 then apply all as 66666, if multiple code found 22222 then all apply to 22222.
Use COALESCE() or ISNULL() for the first one instead of the CASE expression.
For the second one, what do you want to do when the data is slightly changed?
('korea', '66666'),
('korea', '66666'),
('korea', 'checklater'),
('korea', '22222')
('korea', '22222')
You said to use the one that has the most values, but 66666 and 22222 are tied for the most.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply