Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Set value based on previous row Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 8:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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.

Post #1560982
Posted Friday, April 11, 2014 9:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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/


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1560998
Posted Friday, April 11, 2014 2:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:42 PM
Points: 990, Visits: 2,218
Check this article: http://www.sqlservercentral.com/articles/T-SQL/62159/
Post #1561130
Posted Monday, April 14, 2014 1:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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
Post #1561355
Posted Monday, April 14, 2014 2:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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

Post #1561366
Posted Monday, April 14, 2014 3:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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

Post #1561385
Posted Monday, April 14, 2014 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 7,087, Visits: 6,894
;WITH a (ID,ImportGroup) AS (
SELECT c.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))
OR c.DeviceID <> n.DeviceID
OR n.DeviceID IS NULL
),
b (FromID,ToID,ImportGroup) AS (
SELECT ISNULL(a1.ID+1,1),a.ID,a.ImportGroup
FROM a
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.

Post #1561502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse