January 22, 2015 at 2:08 pm
Hello I am working on a query in SQL and can't seem to get it to work 100%
I have a data set in SQL Server that I have created but I need to get a true or false by Event ID if the steps were done in order based on the steps listed in the Step column.. The data below shows the steps were done out of order so I want to query the EventID and a value telling me it wasn't done in order. Picture of the data can be found at the link below..
https://docs.google.com/file/d/0B5TX97oiRQrHX3c1aG1hUFAxWFE/edit?usp=docslist_api
It works for processes done out of order, but when I create one that is in order (meaining the steps were done in order) it stops at step 10 because I believe it thinks 9 is bigger then 10..
Was looking for perhaps another solution or a way to fix this solution..
select
case when exists
(
select *
from ##UpdateTest as this_step
where exists
(
select *
from ##UpdateTest as earlier_step
where cast(earlier_step.dates as datetime) + cast(earlier_step.logtime as datetime) <
cast(this_step.dates as datetime) + cast(this_step.logtime as datetime)
and earlier_step.step > this_step.step
)
) then
'ERROR'
else
'OKAY'
end as status;
January 22, 2015 at 2:41 pm
What data type is your step column? If it is stored as text that would mean 9 is greater than 10. If it is being stored as text, cast or convert the column in your where clause to be a numeric data type.
January 22, 2015 at 2:46 pm
The step is being pulled in from another table and Its an Integer.. Below is a sample of the table that the step is being pulled from..
Create Table ##TriggerMap (
TriggerID bigint,
TriggerName nvarchar(256),
ProcessID bigint,
StepID int,
StepRequired int,
POA int,
UW int,
OW int)
Insert Into ##TriggerMap (TriggerID, TriggerName,ProcessID,StepID,StepRequired,POA,UW,OW) Values(1,'Step1',1,1,1,1,1,1)
Insert Into ##TriggerMap (TriggerID, TriggerName,ProcessID,StepID,StepRequired,POA,UW,OW) Values(2,'Step2',1,2,1,1,1,1)
Insert Into ##TriggerMap (TriggerID, TriggerName,ProcessID,StepID,StepRequired,POA,UW,OW) Values(3,'Step3',1,3,1,1,1,1)
Insert Into ##TriggerMap (TriggerID, TriggerName,ProcessID,StepID,StepRequired,POA,UW,OW) Values(4,'Step4',1,4,1,1,1,1)
Insert Into ##TriggerMap (TriggerID, TriggerName,ProcessID,StepID,StepRequired,POA,UW,OW) Values(5,'Step5',1,5,1,1,1,1)
January 22, 2015 at 2:52 pm
when you create ##UpdateTest are you explicitly defining the temp table or doing a select into?
January 22, 2015 at 2:57 pm
the StepID is being pulled via an update statement
Update ##UpdateTest
Set Step = b.StepID
From ##UpdateTest a, ##TriggerMap b
where a.ProcessID = b.ProcessID
and a.TriggerDisplayName = b.TriggerName
and b.StepRequired = 1
January 22, 2015 at 3:03 pm
Actually that might be it.. I am inserting a space in the table and then updating it, but that space isn't declared as an int...
Select a.EventID
,a.Dates
,a.LogTime
,a.TriggerDisplayName
,Process
,SPACE(20) as Step
into ##UpdateTest
FROM Raw2 as a
January 22, 2015 at 3:06 pm
Yes, there it is. Either use a create table statement and make that column numeric, or select a number into it instead of a space. Personally, I would use a create table statement and explicitly define each columns data type.
January 22, 2015 at 3:10 pm
Yep that was it... Used a create table and casted it as an int and all is good in the hood! Thanks for the help! Don't know why I didn't see that before..
January 22, 2015 at 3:12 pm
No problem, Judging by your original post, you knew what was happening, just not where.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply