Add New Column Based on Criteria

Question

Add New Column Based on Criteria

VegasL

Hall of Fame

Points: 3006
More actions
November 18, 2017 at 10:24 am

#340558

Hello,
I have:
select * from #test_table
INSERT INTO #test_table ([Date], [KW], [PP], [PM], [CP], [Δ T], )
VALUES ('2016-11-01 00:00:00.000', 'voucher', 10, 'up', 22, 800, 'http://www.test.com/p1/'), ('2016-12-01 00:00:00.000', 'voucher', 5, 'up', 31, -300, 'http://www.test.com/p1/'), ('2017-02-04 00:00:00.000', 'voucher', 11, 'up', 10, 1000, 'http://www.test.com/p1/'), ('2017-02-06 00:00:00.000', 'voucher', 3, 'up', 1, 500, 'http://www.test.com/p1/'), ('2017-02-10 00:00:00.000', 'voucher', 15,'down', 5,-2500, 'http://www.test.com/p1/')
What I'd like to do is add a new column, "Page" which would be dependent of result of column CP.
Criteria: If value in cp is between 1 and 10, value in new column should be 1, if value in cp is between 11 and 20, value in new column should be 2, etc. Counting by 1 in increments of 10.
Also date should be in format of MM/DD/YYYY
I've attached example of sample result, what it should be.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Try:
ALTER TABLE #test_table ADD [Page] AS CONVERT(int,((CP - 1) / 10) + 1);
In regards to the date format, it's best to do that in your presentation layer, rather than your SQL. You can't "change" the format that a date stored in in SQL server. They are technically store as numerics, and these are converted to the format yyyy-MM-dd as a display format. If you need to display them in a different way then do so in your presentation layer, as it preserves the data type.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VegasL Hall of Fame Points: 3006 More actions · Answer 2

Thanks Thom.

I tried:
ALTER TABLE #test_table ADD [Page] AS CONVERT(int,((CP - 1) / 10) + 1);

however, the values for coulmn [page] in the highlighted attachment are off by 1. instead of 3 it should be 2, on second row instead of 4 it should be 3

also how can i move this column over to be next to column cp?

After running the insert values code, i then ran the one you provided and it put the new column at the end.

Thom A SSC Guru Points: 99133 More actions · Answer 3

Why are they wrong..? You said that 1-10 is 1, 11-20 is 2. Ergo this would mean:
1 - 10 = 1
11- 20 = 2
21 - 30 = 3 (thus CP 22 = Page 3)
31 - 40 = 4 (thus CP 31 = Page 4)
The values are correct for the logic you supplied.

You can't "move" a column within a table. If you need to place a new column in the "middle" of a table, you will need to create the entire table with the columns in the order you want. That means creating a copy of the table, with the new column, reating any foreign keys, inserting the data from the original table into the new table, re adding any existing indexes, dropping any foreign keys on tables that reference that original table, drop the original table, rename the new table, and then recreate and foreign keys that referred the original table.

The order of the columns in a table, however, shouldn't matter, as you can return the columns in your SELECT statement in any order you choose.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VegasL Hall of Fame Points: 3006 More actions · Answer 4

Sorry my mistake. You're right. Oversight. Thanks for explanation and help!

Appreciated.

sgmunson SSC Guru Points: 110640 More actions · Answer 5

VegasL - Saturday, November 18, 2017 4:02 PM
Sorry my mistake. You're right. Oversight. Thanks for explanation and help!
Appreciated.

Just an FYI, but as another of your posts has the same table structure, I have to ask why the CP column is a float, when the values are clearly integers. That would cause the problem in this post of the incorrect page values, because the formula for the calculated field relies on integer math, and the CP column is defined as float, which would result in rounding, unlike integer math.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 6

As a very slight simplification, you could also do this (I prefer CAST when possible since it's ANSI-standard, whereas CONVERT is not):

CAST((CP + 9) / 10 AS int)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Thom A - Saturday, November 18, 2017 11:06 AM
Try:
ALTER TABLE #test_table ADD [Page] AS CONVERT(int,((CP - 1) / 10) + 1);
In regards to the date format, it's best to do that in your presentation layer, rather than your SQL. You can't "change" the format that a date stored in in SQL server. They are technically store as numerics, and these are converted to the format yyyy-MM-dd as a display format. If you need to display them in a different way then do so in your presentation layer, as it preserves the data type.

Nice job. A little integer arithmetic goes a long way. Might want to persist these types of things for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)