April 11, 2014 at 8:52 am
I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.
Anyway the code is....
declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)
insert into @data (DeviceID, EventTypeID)
values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),
(200, 0),(200, 4),(200, 2),(200, 3),(200, 3)
update t1
set eventid = case when t2.EventTypeID IN (1,2,4) and t1.EventTypeID in (0, 3) then ISNULL(t2.EventID + 1, 1)
when t2.EventTypeID IN (0, 3) and t1.EventTypeID in (1, 2, 4) then ISNULL(t2.EventID + 1, 1)
else ISNULL(t2.EventID + 1, 1) end
from @data t1
inner join @data t2 on t1.ID = t2.ID + 1
select * from @data
[/code]
So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.
Next the rule is coded in the case statement above.
So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.
April 11, 2014 at 9:07 am
Can you also add the desired output to make it easier to understand what you actually mean to convey with this..
See if ranking functions can help you with sequencing numbers. http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/[/url]
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 11, 2014 at 2:19 pm
Check this article: http://www.sqlservercentral.com/articles/T-SQL/62159/
--Vadim R.
April 14, 2014 at 1:45 am
Ok thanks for the replies so far. I checked out that link and it makes sense but I cannot make something like that work for my particular problem.
Here is the required output. It would be much easier if the EventID could be dependent on DeviceID but the customer has asked if it could be independent of DeviceID.
ID DeviceID EventTypeID EventID
1 100 1 1
2 100 2 1
3 100 4 1
4 100 0 2
5 100 3 2
6 200 0 3
7 200 4 4
8 200 2 4
9 200 3 5
10 200 3 5
April 14, 2014 at 2:28 am
The 1st problem I have is I need to track the previous EventID. Here is what I have so far when partitioning on DeviceID. Looking at DeviceID 100 then row 4 should remain at value 2.
declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)
insert into @data (DeviceID, EventTypeID)
values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),
(200, 0),(200, 4),(200, 2),(200, 3),(200, 3);
UPDATE @data
SET EventID = 1
select *,
case when currrow.EventTypeID IN (1,2,4) and nextrow.EventTypeID in (0, 3) then ISNULL(prevrow.EventID + 1, 1)
when currrow.EventTypeID IN (0, 3) and nextrow.EventTypeID in (1, 2, 4) then ISNULL(prevrow.EventID + 1, 1)
else ISNULL(nextrow.EventID, 1) end
from @data currrow
left join @data nextrow on currrow.ID = nextrow.ID -1
left join @data prevrow on currrow.ID = prevrow.ID +1
select * from @data
April 14, 2014 at 3:13 am
Ok this is the draft query which works using DeviceID to base the EventID on. I have had to use a where loop in order to set the value so that the next iteration can use this to increment if (if this is neccessary based on the rules)
Anyone have any other take comments?
declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID INT,
ImportGroup int)
insert into @data (DeviceID, EventTypeID)
values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),
(200, 0),(200, 4),(200, 2),(200, 3),(200, 3);
;WITH cte AS (
SELECT ID, DeviceID, EventTypeID, EventID, ImportGroup,
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY id) AS rownum
FROM @data)
update cte
set ImportGroup = rownum
UPDATE currow
SET eventid = ISNULL(prevrow.EventID + 1, 1)
from @data currow
left join @data nextrow on currow.ID = nextrow.ID -1
left join @data prevrow on currow.ID = prevrow.ID +1
DECLARE @ImportGroup INT
SET @ImportGroup = 1
WHILE (SELECT COUNT(*) FROM @data WHERE ImportGroup=@ImportGroup)>0
BEGIN
UPDATE currow
SET eventid = case when prevrow.EventTypeID IN (1,2,4) and currow.EventTypeID in (0, 3) then ISNULL(prevrow.EventID + 1, 1)
when prevrow.EventTypeID IN (0, 3) and currow.EventTypeID in (1, 2, 4) then ISNULL(prevrow.EventID + 1, 1)
else ISNULL(prevrow.EventID, 1) end
from @data currow
left join @data nextrow on currow.deviceid = nextrow.deviceid AND currow.ID = nextrow.ID -1
left join @data prevrow on currow.deviceid = prevrow.deviceid AND currow.ID = prevrow.ID +1
SET @ImportGroup = @ImportGroup + 1
END
select * from @data
April 14, 2014 at 8:44 am
;WITH a (ID,ImportGroup) AS (
SELECTc.ID,ROW_NUMBER() OVER (ORDER BY c.ID)
FROM@data c
LEFT JOIN @data n ON n.ID = c.ID + 1
WHERE(c.EventTypeID IN (1,2,4) AND n.EventTypeID in (0, 3))
OR(c.EventTypeID IN (0, 3) and n.EventTypeID in (1, 2, 4))
ORc.DeviceID <> n.DeviceID
ORn.DeviceID IS NULL
),
b (FromID,ToID,ImportGroup) AS (
SELECTISNULL(a1.ID+1,1),a.ID,a.ImportGroup
FROMa
LEFT JOIN a a1 On a1.ImportGroup = a.ImportGroup - 1
)
SELECT d.ID,d.DeviceID,d.EventTypeID,b.ImportGroup
FROM @data d
JOIN b ON d.ID BETWEEN b.FromID AND b.ToID
Performance could be an issue here due to table scans.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply