November 13, 2011 at 3:21 pm
Hello, everyone!
I have got two questions.
The first question:
Is it possible in SQL Server 2005 to have it automatically update a value from a previous row into a next row in a same column? I basically want to increment a value from a very first row per 5% with each new row inserted so that I don’t have to do it manually with every new row. Here is an example:
1.100 (automatic 5% increment command will send its result automatically to the next row when it is created by a user)
2.105
3.110,2
4.115,7
5.121,4
and so on...
If there is a syntax for it, I would love to know it.
The second question:
Is it possible to have a column with the datetime data type showing only Month-Year format of a date? If yes, how?
Thank you kindly for all your answers.
November 13, 2011 at 4:03 pm
First of all realize that there is not a "next row", in a SQL table, rows are not guaranteed to be returned in the order in which they were added to the table. Unless you have a incrementing column defined, lets use an IDENTITY for this discussion and you use that column in an ORDER BY clause in your T-SQL that returns values from you table.
Second assuming you always use the ORDER BY clause, and for your data, lets say an entry (IDENTITY value = 3) is deleted from the table. What percentage value for an entry with an IDENTITY value of 4 would be correct, the value that was 5 per cent greater than the now non existing data or 5 percent greater than the still remaining row with an IDENTITY value of 2 ?
November 13, 2011 at 9:44 pm
jeronym.vasica (11/13/2011)
<snip>...The second question:
Is it possible to have a column with the datetime data type showing only Month-Year format of a date? If yes, how?
Thank you kindly for all your answers.
Sql server stores datetimes internally in a binary representation. Your application is responsible for displaying the value in whatever format it wants.
You can use CAST and CONVERT to return datetimes as strings, or use DATEPART and roll your own.
You're question is equivalent to asking how to get sql server to return integers with thousands seperators (123,456,789 -or- 123.456.789). Sql Server is going to return a 4-byte binary value (32 bits), it's up to the client how to format it (hex, octal, decimal, etc)
November 14, 2011 at 7:49 am
I should have write the second question more clearly, sorry.
I am able to use SELECT command for the datetime values in the date column and show them in a month-year-only format, but what I want to do is to have the column with dates formatting it automatically and permanently without the further use of the select command so that the month-year values would behave as a datetime and could be incremented with each new row.
Example:
Jan 2011 (this is the very first row and I want it to be incremented automatically per 1 month with each new row - the next rows would therefore look like this:)
Feb 2011
Mar 2011
Apr 2011
May 2011
and so on...
So I am interested in a column that is permanently set to showing month-year format of a date and with ability to increment its months with each new row. i hope this is clear enough. Thank you very much in advance.
Sql server stores datetimes internally in a binary representation. Your application is responsible for displaying the value in whatever format it wants.
You can use CAST and CONVERT to return datetimes as strings, or use DATEPART and roll your own.
You're question is equivalent to asking how to get sql server to return integers with thousands seperators (123,456,789 -or- 123.456.789). Sql Server is going to return a 4-byte binary value (32 bits), it's up to the client how to format it (hex, octal, decimal, etc)
November 14, 2011 at 7:52 am
Classic demand / mistake.
Either return the date of the first of the month or return YEAR and month in 2 separete columns.
Then sort by those values, but present the month name.
November 14, 2011 at 8:56 am
Ninja's_RGR'us (11/14/2011)
Classic demand / mistake.Either return the date of the first of the month or return YEAR and month in 2 separete columns.
Then sort by those values, but present the month name.
Could you please elaborate it bit more so I could understand precisely what you mean?
Thank you!
November 14, 2011 at 9:02 am
year - month
2011 - 1 - January
2011 - 2 - February
2011 - 3 - March
If you sort by column 1 & 2, the sequence will be correct in the month name. It helps also if you do that across more than 1 year.
November 14, 2011 at 9:07 am
bitbucket-25253 (11/13/2011)
First of all realize that there is not a "next row", in a SQL table, rows are not guaranteed to be returned in the order in which they were added to the table. Unless you have a incrementing column defined, lets use an IDENTITY for this discussion and you use that column in an ORDER BY clause in your T-SQL that returns values from you table.Second assuming you always use the ORDER BY clause, and for your data, lets say an entry (IDENTITY value = 3) is deleted from the table. What percentage value for an entry with an IDENTITY value of 4 would be correct, the value that was 5 per cent greater than the now non existing data or 5 percent greater than the still remaining row with an IDENTITY value of 2 ?
The value that was 5 per cent greater than the now non existing data would be fine for my case I suppose.
November 14, 2011 at 9:16 am
Ninja's_RGR'us (11/14/2011)
year - month2011 - 1 - January
2011 - 2 - February
2011 - 3 - Marche
If you sort by column 1 & 2, the sequence will be correct in the month name. It helps also if you do that across more than 1 year.
So in fact I should make three columns for this purpose? The first will be a year, the second a number of a month and the third column a name of a month and then sort it by (order by) column one and two?
November 14, 2011 at 9:20 am
Bouben (11/14/2011)
Ninja's_RGR'us (11/14/2011)
year - month2011 - 1 - January
2011 - 2 - February
2011 - 3 - March
If you sort by column 1 & 2, the sequence will be correct in the month name. It helps also if you do that across more than 1 year.
So in fact I should make three columns for this purpose? The first will be a year, the second a number of a month and the third column a name of a month and then sort it by (order by) column one and two?
Yes.
You can replace columns 1 & 2 with a single data column. But I use this mainly in SSRS and I usually need to group by on both of them so that makes more sense to me.
November 14, 2011 at 1:47 pm
Below is an example to do what you asked.
However, I strongly recommend against taking this approach.
The value that was 5 per cent greater than the now non existing data would be fine for my case I suppose.
Are you sure?
If you look at the table, you see 2 rows (1, 100), (2, 105) you insert a row, and you're ok with it being 110 or 115? (realize that you have no idea whether row 3 was ever in the table or not? and even if it wasn't inserted and deleted, you have no garuantee that id=3 will be the next identity value you get.)
Assuming that rows are inserted into a table in a specific order is a very bad design that will bite you later. Your application should be responsible for caculating things like this (even if you have to do it each and every time you insert a row.)
If you can't do this calculation in your application, please state why your application doesn't know what month it is inserting data for.
USE tempdb
go
CREATE TABLE test
(
id INT IDENTITY
NOT NULL
PRIMARY KEY
, Dt DATETIME NULL
, DisplayMonth VARCHAR(50) NULL
, Value DECIMAL(10, 4) NULL
, other_col INT
)
go
CREATE TRIGGER ti_Test ON test
FOR INSERT
AS
BEGIN
DECLARE @maxId INT
DECLARE @maxDt DATETIME
DECLARE @maxVal NUMERIC(10, 4)
DECLARE @prcnt DECIMAL(10, 4)
SET @prcnt = 1.05 -- 5 percent increase
--Grab @maxId that's not being inserted
SELECT @maxId = MAX(id) FROM Test where id NOT IN (SELECT id FROM INSERTED)
SELECT
@maxDt =dt, @maxVal = VALUE
FROM
test
WHERE id = @maxId
IF @maxDt IS NOT NULL --null on first row inserted into table
BEGIN
UPDATE
Test
SET dt = DATEADD(mm, 1, @maxDt),
DisplayMonth = LEFT(DATENAME(mm, DATEADD(mm, 1, @maxDt)), 3)
+ ' ' + CAST(YEAR(DATEADD(mm, 1, @maxDt)) AS VARCHAR(4))
FROM
INSERTED
INNER JOIN Test ON INSERTED.id = test.id
END
IF @maxVal IS NOT NULL --null on first row inserted into table
BEGIN
UPDATE
Test
SET VALUE = @maxVal * @prcnt
FROM
INSERTED
INNER JOIN Test ON INSERTED.id = test.id
END
END
GO
INSERT INTO Test
( other_col )
VALUES
( 1 )
-- no previous maxes, dt, display and value are null.
SELECT
*
FROM
Test
--fix it
UPDATE
test
SET dt = '2011-01-01', DisplayMonth = 'Jan 2011', VALUE = 100
SELECT
*
FROM
Test
INSERT INTO Test
( other_col )
VALUES
( 2 )
INSERT INTO Test
( other_col )
VALUES
( 3 )
DELETE FROM test WHERE other_col = 3
--reinsert 3
INSERT INTO Test
( other_col )
VALUES
( 3 )
--so far so good.
BEGIN TRAN
INSERT INTO Test
( other_col )
VALUES
( 4 )
ROLLBACK TRAN
SELECT
*
FROM
Test
INSERT INTO test
( other_col )
SELECT
5
UNION ALL
SELECT
6
UNION ALL
SELECT
7
--3 rows for May 2011
SELECT
*
FROM
test
go
DROP TABLE Test
November 14, 2011 at 1:56 pm
+1
This SHOULD be done app side unless you have absolutely no front end that can do it for you.
I've just coded something like this and it's a effing PITA. 1000 lines of code to handle most cases.
November 14, 2011 at 1:57 pm
Bouben (11/13/2011)
Hello, everyone!I have got two questions.
The first question:
Is it possible in SQL Server 2005 to have it automatically update a value from a previous row into a next row in a same column? I basically want to increment a value from a very first row per 5% with each new row inserted so that I don’t have to do it manually with every new row. Here is an example:
Yes, with:
- a calculated column that's not persisted and thus constantly recalculated because it is non-deterministic to the engine.
- a trigger to update the 'next' row (and all following rows) when a change is made to a current row, an insert 'instead of' trigger to deal with new rows coming in, and a delete trigger to re-calculate all following rows after a deletion occurence.
- A proc that calculates the values on the fly during request (probably your best bet)
- Not using SQL to perform this task and using rolling totals in your display application.
I do not usually recommend doing this type of work constantly in the database. Lookups of previous entries in a logging table is usually done piecemeal. If this is a continuous event you would most likely be better off with it in the application tier. The biggest concern is the update of EVERY row following a change/deletion to a midpoint row. This will primarily depend on your data methodology and business rules surrounding the information.
The other option for runtime evaluation is a process called the "Quirky Update" or "Running Totals Update". It's a technique that uses undisclosed features of the engine that has a number of rules surrounding it to avoid the possible pitfalls. Jeff Moden has an excellent article here on the process:
http://www.sqlservercentral.com/articles/T-SQL/68467/
If you can give us more details about exactly what business rules you're trying to fulfill and what methods of input they have, we might be able to assist you in finding the optimal path to what you're looking to do here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 14, 2011 at 2:52 pm
Guys, thank you very much for your support.
I will take my time and make an in-depth research of what you have written so far.
To be honest, this is a task I was given by my SQL tutor after two weeks of studying it. I have never ever used SQL or any other database language before and have to learn a lot in a short period of time.
So thank you for your patience. I really appreciate that.
What level of knowledge of the SQL would you say this task is? Intermediate, advanced or expert?
November 14, 2011 at 3:11 pm
Bouben (11/14/2011)
Guys, thank you very much for your support.I will take my time and make an in-depth research of what you have written so far.
To be honest, this is a task I was given by my SQL tutor after two weeks of studying it. I have never ever used SQL or any other database language before and have to learn a lot in a short period of time.
That's a REALLY interesting choice of challenge to be presented by your 'tutor'. Does he have a business need for this or did he read something somewhere and thought it would be 'interesting'? It's not a common scenario to do outside of reporting, and SSRS is built for things like that.
So thank you for your patience. I really appreciate that.
No worries, welcome to the forum.
What level of knowledge of the SQL would you say this task is? Intermediate, advanced or expert?
Basic/intermediate to do, advanced to do well. It's a matter of discussing the business rules, requirements, and knowing the side effects of the different approaches and how each one will affect the system. There's a few dozen pitfalls to avoid in terms of clean data approaches.
Mind you, a lot of us work on billion row systems, so, our need to optimize and tighten all the bolts and screws comes from a mindset of hyper-optimization. If you've got a few hundred rows, go nuts... but don't be surprised if in two years after someone in marketing decided to tell a few buddies you're now supporting the enterprise wide app built out of bubble gum and shoestring. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply