Introduction
Like so many people, you've run into a problem with T-SQL that you need a little help with. You turn to a well know forum, like SQLServerCentral.com, where you know there are lots and lots of good people who are sure to quickly take an interest in your problem and help. You post data and descriptions of your tables as well as a nearly perfect description of both what your problem is and what you'd like to get for a result...
But, your post goes for hours or, maybe, even days with no answer. With your boss breathing down your neck and in a final act of desperation, you may even resort to the two most despicable of forum practices... double posting or "bumping" your post.
Finally, someone answers your desperate plea for help only to find that either the code they gave you is wrong or, worse yet, they give you a long-hand "suggestion" as to what to do with your code instead of providing a snippet of fully tested code that would solve all your woes.
What went wrong? Chances are, it's the way you posted your data or your code...
The Wrong Way to Post Data
Normally, a person will start a post by saying something like "I have a table that contains the following data:" and then they post something like this (this is just the first 8 rows from an actual 601 row post)...
| ID DateValue Value YearValue Monthvalue 4 2007-10-17 00:00:00.000 5.1709 8 1 37 2007-10-17 00:00:00.000 5.5319 17 8 44 2007-10-17 00:00:00.000 5.5793 21 11 54 2007-10-17 00:00:00.000 5.2471 9 2 55 2007-10-17 00:00:00.000 5.1177 7 0 81 2007-10-17 00:00:00.000 5.5510 18 9 86 2007-10-17 00:00:00.000 5.5128 16 7 96 2007-10-17 00:00:00.000 5.5758 20 10 |
Now, while that doesn't look so bad, it's pretty much useless as actual test data. Certainly, no one will manually type that information to get it into a test table... some severe formatting must be done, first. Lots of well meaning folks won't even take the time to answer such a problem when the data needs all sorts of work to be done to it just to get it into a table to test. Even saving the data to a file and importing the file is all very much a pain for the person trying to help solve the posted problem especially for DATETIME datatypes have contain a space in the column... makes it very import resistant
Even if someone does answer the post with code, unless they've tested it with your data, there's a pretty good chance of the code not working or not doing what you really want it to.
The Correct Way to Post Data
So, how do you post data in a format that will actually build a table? Well, sure, YOU could do all of the manual formatting... but that would also take YOU a long time. Here's how to make it easy on everyone including yourself...
Create Some Table Code
Yeah, this first part is all manual... but, it's worth it and it doesn't take any real time, at all. It does, however, do several very important things...
- It very precisely identifies the data-types and that, alone, answers so many unnecessary questions.
- It very precisely identifies constraints, conditions, and the Primary Key of the table so you don't have to write it out long-hand. Makes things crystal clear as to what the table actually looks like because it's an actual table definition in code.
- It will entice folks who might be able to help solve your problem into actually taking the time to solve your problem.
So, for the data originally given above, you would write something like the following:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
Value DECIMAL(9,4),
YearValue INT,
MonthValue INT
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
Notice the SET statement? The original data is in the DMY format. Instead of explaining that long-hand, the SET statement has been included as a "special condition" so the potential respondent to your problem doesn't even have to think about it when it comes time to loading your data. Instead, they'll be able to spend the time working on an answer to your problem.
Create Some Data
Obviously, the most convenient way to post data is to just SELECT the data from an actual table. That's what most folks do and they end up with the unformatted, difficult-to-load mess of data listed at the beginning of this article. That puts a lot of people off from answering your post or may cause them to create an "untested" bit of code because it's just too much of a pain to put your unformatted data into a test table to verify their code.
With just a little effort on your part, you can make it so the data is "self loading"... here's an example of how using QUOTENAME (assumes a table name of "yourtable"...
SELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(DateValue,'''')+','
+ QUOTENAME(Value,'''')+','
+ QUOTENAME(YearValue,'''')+','
+ QUOTENAME(MonthValue,'''')
+ ' UNION ALL'
FROM yourtable
If you run that in the Text Mode of Query Analyzer (dunno what they call it in 2k5), it'll produce a nice data-producing output like the following:
SELECT '4','Oct 17 2007 12:00AM','5.1709','8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM','5.5319','17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM','5.5793','21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM','5.2471','9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM','5.1177','7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM','5.5510','18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM','5.5128','16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM','5.5758','20','10' UNION ALL
(Sidebar: If you take out the UNION ALL from the code generator, and change the single quote to a double quote in QUOTENAME, guess what you have... a nice neat little CSV data generator!)
Delete the last "Union All" (highlighted in RED and BOLD above), add the necessary SET statements for inserting into an IDENTITY column, and add the actual INSERT statement, and here's what you come up with...
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value, YearValue, Monthvalue)
SELECT '4','Oct 17 2007 12:00AM','5.1709','8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM','5.5319','17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM','5.5793','21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM','5.2471','9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM','5.1177','7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM','5.5510','18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM','5.5128','16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM','5.5758','20','10'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
Problems with Some Data Types
The Money data type and a few others simply will not allow implicit conversions from CHAR or VARCHAR... so, what do you do? The answer is simply, don't put quotes around that data...
So, in our current example, if the table actually looked like this...
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME,
Value MONEY,
YearValue INT,
Monthvalue INT
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
... then our "data generation" code would look like this to keep from putting quotes around the data for the VALUE column...
SELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(DateValue,'''')+','
+ CAST(Value AS VARCHAR)+','
+ QUOTENAME(YearValue,'''')+','
+ QUOTENAME(MonthValue,'''')
+ ' UNION ALL'
FROM yourtable
... and that would make data that could be inserted into the character-resistant MONEY data type...
SELECT '4','Oct 17 2007 12:00AM',5.1709,'8','1' UNION ALL SELECT '37','Oct 17 2007 12:00AM',5.5319,'17','8' UNION ALL SELECT '44','Oct 17 2007 12:00AM',5.5793,'21','11' UNION ALL SELECT '54','Oct 17 2007 12:00AM',5.2471,'9','2' UNION ALL SELECT '55','Oct 17 2007 12:00AM',5.1177,'7','0' UNION ALL SELECT '81','Oct 17 2007 12:00AM',5.5510,'18','9' UNION ALL SELECT '86','Oct 17 2007 12:00AM',5.5128,'16','7' UNION ALL SELECT '96','Oct 17 2007 12:00AM',5.5758,'20','10'
What It Should Look Like When Done
Just for a good reference, here's what the data should look like when it's all put together for your very important post for help...
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME,
Value MONEY,
YearValue INT,
Monthvalue INT
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value, YearValue, Monthvalue)
SELECT '4','Oct 17 2007 12:00AM',5.1709,'8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM',5.5319,'17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM',5.5793,'21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM',5.2471,'9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM',5.1177,'7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM',5.5510,'18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM',5.5128,'16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM',5.5758,'20','10'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
My Pet Peeve...
To keep from doing people's homework or keep from answering online "interview questions" during the heat of an interview, folks like me will, many times, insist that you post the code that you've tried. But, if we can't read your code, we're just as likely to NOT give your desparate post even a second glance if your code likes like the following...
select v.* from #mytable v, ( select min(id) as firstidofstagnation from #mytable v, ( select min(id) as lastidbeforestagnation from ( select top 2 value, max(id) as id from #mytable group by value order by max(id) desc )x )ls where v.id > ls.lastidbeforestagnation )fs where v.id = fs.firstidofstagnation
When I see garbage code like that, I figure, "Heck, if you don't care about your code, neither do I" and I go to the next post. Folks that want to try to help aren't any more likely to help on this type of posted code than if you listed your data like was done at the beginning of this thread. We just don't have the time or gumption to reformat your cruddy code just so we can figure out what it is that you've done.
AND... comments as to what you're doing are a big help... the easier it is to read your code, the more likely folks like me are to help. So, the same code above would likely get my attention quicker if it were nice and readable like the following example (same code as above, just formatted and commented for clarity sake)...
SELECT v.*
FROM #MyTable v,
(--==== Derived table "fs" finds ID of the first row
-- where stanation of VALUE occurs
SELECT MIN(ID) AS FirstIDOfStagnation
FROM #MyTable v,
(--==== Derived table "ls" finds last ID before
--stagnation of VALUE
SELECT MIN(ID) AS LastIDBeforeStagnation
FROM (--==== Derived table "x" finds the ID's of the
-- last value and the value before that
SELECT TOP 2
Value, MAX(ID) AS ID
FROM #MyTable
GROUP BY Value
ORDER BY MAX(ID) DESC
)x
)ls
WHERE v.ID > ls.LastIDBeforeStagnation
)fs
WHERE v.ID = fs.FirstIDOfStagnation
Conclusion
Give your question a chance of being answered correctly, never mind at all...
Instead of the unassuming and nearly useless posting of data that was shown at the beginning of this thread, give yourself a better chance to get your very important question answered quickly and correctly! Make it so folks don't have to even think about how to load your sample data or make your code readable. Doing both show folks like me just how important your question is.
Remember, if you don't care what your data or code look like, I don't either and I probably won't even bother with your post. Help us help you.
--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
