June 18, 2012 at 9:55 am
I feel very stupid but can't fathom this:
I have a constantlt refreshed table always with 5 rows and these columns: ID, Date, Description.
I want to make sure that the entries conform to a certain order, so that Description1 always has a lower ID and Date than Description2 etc up to 5.
I can't for the life of me figure out how to do a simple comparison, as think am overcomplicating things. I want to say output the data to a location if it conforms to the criteria.
Can someone help a dummy :blush:
June 18, 2012 at 10:06 am
If I understood you correctly you need to create an identity column for the ID and the date column can get the value of getdate() when you insert a record, but I'm not sure that I understood what you need:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/
June 18, 2012 at 10:12 am
Please provide sample data and sample desired results so that we can understand your requirements better.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 18, 2012 at 10:55 am
I think i understood the requirement, but hey, i may be terribly misinformed :w00t:
So, lets cook some sample data (you said 5 rows, ALWAYS)
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test;
CREATE TABLE #Test
(
iD INT IDENTITY(1,1)
, RefreshDate DATETIME2
, Description VARCHAR(15)
);
INSERT INTO #Test
SELECT GETDATE() , 'Description 1'
UNION ALL SELECT GETDATE() , 'Description 2'
UNION ALL SELECT GETDATE() , 'Description 3'
UNION ALL SELECT GETDATE() , 'Description 4'
UNION ALL SELECT GETDATE() , 'Description 5'
;
SELECT T.iD , T.RefreshDate , T.Description
FROM #Test T
;
And you wnated to check if the lowest id has the lowest date and the next lowest id has the next lowest date and so on, right? If thats the case, then, here are 2 sample pieces of code:
WITH CTE AS
(
SELECT T.iD , T.RefreshDate , T.Description
, RN_iD = ROW_NUMBER() OVER (ORDER BY T.iD ASC)
, RN_Dt = ROW_NUMBER() OVER (ORDER BY T.RefreshDate ASC)
FROM #Test T
)
SELECT TOP 1 Result = 'Dates and iDs dont follow order'
FROM CTE C
WHERE EXISTS (
SELECT TOP 1 InrTab.*
FROM CTE InrTab
WHERE InrTab.RN_iD <> InrTab.RN_Dt
)
;
WITH CTE AS
(
SELECT T.iD , T.RefreshDate , T.Description
, Indicator = CASE WHEN ROW_NUMBER() OVER (ORDER BY T.iD ASC) <>
ROW_NUMBER() OVER (ORDER BY T.RefreshDate ASC) THEN 0
ELSE 1
END
FROM #Test T
)
SELECT TOP 1 Result = 'Dates and iDs dont follow order'
FROM CTE C
WHERE C.Indicator = 0
June 19, 2012 at 2:41 am
Not sure I explained very well...
If I have this:
ID Date Description
--------------------------------
221 1/11/12 09:00 Note1
222 1/11/12 09:02 Note2
225 1/11/12 09:08 Note3
678 1/11/12 10:00 Note4
898 1/11/12 10:33 Note5
I want to run a check to see that where certain Descriptions which are always 'Note1' through to 'Note5' exist, that they also have ascending IDs and ascending Datetime stamps (it corresponds to jobs and their correct running order). If they do, I would probably select all to output somewhere (csv file, table etc) and if not maybe either output whatever exists but with an additional columns stating failure or a filename to that effect.
I'm just stuggling with the logic of examining the contents initially
June 19, 2012 at 2:49 am
..I would probably select all to output somewhere (csv file, table etc) and if not maybe either output what ...
If you don't know what you want, what chance do we have? You need to think about this some more and (as suggested in my previous post) give us some sample output.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 19, 2012 at 3:32 am
OK, specifically this stumps me:
If this is my table:
ID Date Description
--------------------------------
221 1/11/12 09:00 Note1
222 1/11/12 09:02 Note2
225 1/11/12 09:08 Note3
678 1/11/12 10:00 Note4
898 1/11/12 10:33 Note5
How do I cross-reference that 5 notes exist, and that they are in ascending order in relation to their ID and datetime?
June 19, 2012 at 3:40 am
Please detail what is wrong with Cold Coffee's solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 19, 2012 at 4:26 am
Have looked at Cold Coffee's solution and it works to an extent, but it will only work when the notes are as they should be. I can't expect that to be the case. For instance my table may look like this:
ID Date Description
--------------------------------
221 1/11/12 09:00 Note1_sometext
222 1/11/12 09:02 Note5_more
225 1/11/12 09:08 Note3_words
678 1/11/12 10:00 Note4_bitoftext
898 1/11/12 10:33 Note2_etcetc
In that case the IDs and dates are in the correct order, but the notes are not. Now I know what the notes will say and the order they should be in, but the wording isn't sequential in any way (I used Note1 to 5 for simplicity). Do I need some sort of modification to also look for the correct note in the correct position?
June 20, 2012 at 7:44 am
Now I know what the notes will say and the order they should be in, but the wording isn't sequential in any way (I used Note1 to 5 for simplicity).
I'm not sure if I'm following: are you saying that there is something in the contents of [Description] that implies an order, but that it's nothing quite as clear-cut as Note1, Note2, etc.?
If [Description] did contain values like Note1, Note2, etc. you could write string parsing logic to get the ordered value and derive a rank based on that (especially if they always followed a nice, predictable pattern), which could then be compared to a rank based on ID and date. Could be something as simple as:
CAST(SUBSTRING('Note1',5,1) AS int)
If the contents of [Description] don't follow such a pattern, can you provide any insight on what the contents would be and how you would use those contents to derive a rank?
June 20, 2012 at 7:57 am
rarara (6/19/2012)
OK, specifically this stumps me:If this is my table:
ID Date Description
--------------------------------
221 1/11/12 09:00 Note1
222 1/11/12 09:02 Note2
225 1/11/12 09:08 Note3
678 1/11/12 10:00 Note4
898 1/11/12 10:33 Note5
How do I cross-reference that 5 notes exist, and that they are in ascending order in relation to their ID and datetime?
You will need 3 triggers (one each for INSERT, UPDATE and DELETE) to ENSURE that you NEVER allow the table to get into an unacceptable state. You will also need to either have a FIXED type of description whereby the "counter" in that description can be stripped off by position in the string OR you will need to iterate through the characters to grab the numeric "counter". Each trigger will need to be coded to validate all of your rules and rollback any modification that attempts to put data into an unacceptable state.
Do not feel bad that you couldn't figure this out. Your requirements, as I understand them, are quite complex. I note that (to me) they also go beyond what a freely supported forum is designed for and I encourage you to get a professional to assist you. Perhaps others here will volunteer enough time to help build you a fully functional system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2012 at 8:22 am
So your ID and Description pairings will always be the same (ID=1 & Desc=Foo, ID=2 & Desc=Bar, ID=3 & Desc=Blah, etc.), but the date values will change from day to day, right? And all you're looking for is to be notified (somehow) when the date for ID=1 isn't older than the date for ID=2, and 2 isn't older than 3, and so on. Is that right?
If so, I'm sure one of the True Experts (like Cold Coffee) could whip something up pretty easily. Part of the struggle is that we (and maybe you, too) aren't 100% sure of the requirements. Clarity will get you suggested options.
June 20, 2012 at 11:39 am
Now I know what the notes will say and the order they should be in, but the wording isn't sequential in any way (I used Note1 to 5 for simplicity). Do I need some sort of modification to also look for the correct note in the correct position?
Well, if you cannot add a 'Rank' column to the original table and add the appropriate ranking at the time the note field is updated, then I suppose you could create a second table of descriptions with their ranking and join to that. This could also be used to test whether a specific note exists.
Using Cold Coffee's test table with some different, more random content:
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test;
CREATE TABLE #Test
(
iD INT IDENTITY(1,1)
, RefreshDate DATETIME2
, Description VARCHAR(15)
);
INSERT INTO #Test
SELECT GETDATE() , 'sometext'
UNION ALL SELECT GETDATE() , 'more'
--UNION ALL SELECT GETDATE() , 'words'
UNION ALL SELECT GETDATE() , 'bitoftext'
UNION ALL SELECT GETDATE() , 'etcetc'
;
SELECT T.iD , T.RefreshDate , T.Description
FROM #Test T
;
/*
(You will notice that I have deliberately commented-out the description 'words' so that it is not included in the #Test table for now. This will help us test for missing notes. You can un-comment it if you want later.)
Now create the reference table:
*/
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2;
CREATE TABLE #Test2
(
Description VARCHAR(15),
Rank int
);
INSERT INTO #Test2
SELECT 'sometext', 1
UNION ALL SELECT 'more', 2
UNION ALL SELECT 'words', 3
UNION ALL SELECT 'bitoftext', 4
UNION ALL SELECT 'etcetc', 5
;
SELECT T.Description, Rank
FROM #Test2 T
;
/*
All five notes are included in the ranking table, along with their ranking order.
Now run this:
*/
SELECT t2.Rank , T.RefreshDate , T.Description
FROM #Test2 t2
LEFT OUTER JOIN #Test T ON T2.Description = T.Description
ORDER BY t2.Rank
/*
. . . will return the notes and dates in Ranking order, and NULL where the missing data should be.
I hope this helps
*/
June 20, 2012 at 12:40 pm
Seems many of the posters here are missing a key phrase in the OP: "constantly refreshed table". SELECT-only solutions are thus pointless from what I understand. There must be rules in place to ensure any "refresh" of data in that table (i.e. INSERT/UPDATE/DELETE) leaves the data in a state such that all requirements remain adhered to. The only way I know of to enforce data requirements against DML activity is/are trigger(s).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2012 at 2:39 pm
rarara (6/19/2012)
I want to run a check to see that where certain Descriptions which are always 'Note1' through to 'Note5' exist, that they also have ascending IDs and ascending Datetime stamps (it corresponds to jobs and their correct running order). If they do, I would probably select all to output somewhere (csv file, table etc) and if not maybe either output whatever exists but with an additional columns stating failure or a filename to that effect.
I'm just stuggling with the logic of examining the contents initially
The OP's second post seems to imply that's he/she is looking to examine the table at certain times to determine whether it is in the appropriate state and then act accordingly. The fact that it's "constantly refreshed" may mean that this check needs to occur frequently, but not necessarily during DML operations.
Depending on the requirements, it could make sense to implement a system that compels data to always conform to the correct methodology, rather than allowing it to exist in an incorrect state in the first place. I agree with you that this sort of solution is generally preferable.
In the app designs employed by my company, all DML is handled through store procedures; in that design business logic validation could go in the SP(s) rather than in a trigger.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply