July 18, 2012 at 4:49 pm
I have a record of CIP cleaning processes that have been applied to a number of tanks. They have unique id's and all occur on different dates. There are two kinds of processes -- caustic with acid (NaOH for short) or Acid only (Acid). A tank will undergo several (0 - 10) acid cleanings before it will receive a caustic cleaning. I would like to return a result set that identifies a caustic cleaning with a "0", and subsequent acid cleanings with increments of 1, until the next caustic cleaning which again is identified with a "0".
I have tried with row_number etc. but cannot crack the code. Here is some sample data:
CREATE TABLE #t (MicroTestID INT,TestDate DATETIME,Item CHAR(3),CIPType CHAR(10))
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('10302','1/4/2012','05W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('10324','1/6/2012','05W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('11002','1/11/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('11034','1/13/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('11393','1/18/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('11763','1/25/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('12470','2/1/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('12657','2/4/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('12828','2/8/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('13453','2/14/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('13460','2/15/2012','05W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('13488','2/16/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('14043','2/22/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('14081','2/24/2012','05W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('14585','2/28/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('14625','3/2/2012','05W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('15117','3/7/2012','03W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('15161','3/9/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('15627','3/15/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('15794','3/20/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('15845','3/22/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('16411','3/26/2012','03W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('16426','3/27/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('16473','3/30/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('17005','4/3/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('17034','4/5/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('17570','4/9/2012','03W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('17590','4/12/2012','03W','NaOH')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('17598','4/13/2012','03W','Acid')
INSERT #t(MicroTestID,TestDate,Item,CIPType) VALUES ('18066','4/21/2012','03W','Acid')
SELECT t.MicroTestID
,t.TestDate
,t.Item
,t.Ciptype
,ROW_NUMBER() OVER (PARTITION BY t.Item,t.CIPType ORDER BY t.[TestDate]) AS RowNum
,ROW_NUMBER() OVER (PARTITION BY t.Item ORDER BY t.[TEstDate]) AS RowNumSeq
,ROW_NUMBER() OVER (PARTITION BY t.Item ORDER BY t.[TestDate])-ROW_NUMBER() OVER (PARTITION BY t.Item,t.CIPType ORDER BY t.[TestDate]) AS Diff
FROM #t t
DROP TABLE #t
I would like to return a result set that looks something like:
MicroTestIDTestDateItemCiptypeSequence
151171/4/201203WNaOH 0
151611/6/201203WAcid 1
156271/11/201203WAcid 2
157941/13/201203WAcid 3
158451/18/201203WAcid 4
164111/25/201203WNaOH 0
164262/1/201203WAcid 1
164732/4/201203WAcid 2
170052/8/201203WAcid 3
170342/14/201203WAcid 4
175702/15/201203WNaOH 0
175902/16/201203WNaOH 0
175982/22/201203WAcid 1
180662/24/201203WAcid 2
103022/28/201205WNaOH 0
103243/2/201205WNaOH 0
110023/7/201205WAcid 1
110343/9/201205WAcid 2
113933/15/201205WAcid 3
117633/20/201205WAcid 4
124703/22/201205WAcid 5
126573/26/201205WAcid 6
128283/27/201205WAcid 7
134533/30/201205WAcid 8
134604/3/201205WNaOH 0
134884/5/201205WAcid 1
140434/9/201205WAcid 2
140814/12/201205WNaOH 0
145854/13/201205WAcid 1
146254/21/201205WAcid 2
So the sequence restarts for every tank at every NaOH with '0'.
Thanks for reading! d lewis
July 18, 2012 at 5:17 pm
This?
;WITHBase As
(
SELECT *
, RN = ROW_NUMBER() OVER (ORDER BY T.TestDate)
, RN1 = ROW_NUMBER() OVER (PARTITION BY T.Item , T.CIPType ORDER BY T.TestDate)
FROM #t T
)
SELECT B.MicroTestID , B.TestDate , B.Item , B.CIPType
, Seq_Num = ROW_NUMBER() OVER (PARTITION BY B.Item , B.CIPType ,(B.RN - B.RN1) ORDER BY B.TestDate)
FROM Base B
WHERE B.CIPType <> 'NaOH '
UNION ALL
SELECT B.MicroTestID , B.TestDate , B.Item , B.CIPType
, Seq_Num = 0
FROM Base B
WHERE B.CIPType = 'NaOH '
ORDER BY B.Item , B.TestDate , Seq_Num
July 19, 2012 at 1:47 am
Check This.. I am not sure whether you meant the same or not.
Declare @testtable TABLE (MicroTestID INT,TestDate DateTime,Item CHAR(3),CIPType CHAR(10))
Declare @testtable1 TABLE (ID int,MicroTestID INT,TestDate DateTime,Item CHAR(3),CIPType CHAR(10),sequence int)
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('10302','1/04/2012','05W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('10324','1/06/2012','05W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('11002','1/11/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('11034','1/13/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('11393','1/18/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('11763','1/25/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('12470','2/01/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('12657','2/04/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('12828','2/08/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('13453','2/14/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('13460','2/15/2012','05W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('13488','2/16/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('14043','2/22/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('14081','2/24/2012','05W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('14585','2/28/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('14625','3/2/2012','05W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('15117','3/07/2012','03W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('15161','3/09/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('15627','3/15/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('15794','3/20/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('15845','3/22/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('16411','3/26/2012','03W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('16426','3/27/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('16473','3/30/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('17005','4/03/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('17034','4/05/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('17570','4/09/2012','03W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('17590','4/12/2012','03W','NaOH')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('17598','4/13/2012','03W','Acid')
INSERT INTO @testtable(MicroTestID,TestDate,Item,CIPType) VALUES ('18066','4/21/2012','03W','Acid')
declare @i int =0
insert into @testtable1
SELECT ROW_NUMBER() OVER (Order by MicroTestID asc),MicroTestID,TestDate,Item,CIPType,null
FROM @testtable
Order by TestDate asc
declare @g int = 0
while(@i<=(Select COUNT(*) FROM @testtable))
begin
IF((Select CIPType FROM @testtable1 WHERE ID = @i)= 'NaOH')
begin
UPdate @testtable1
SET sequence = 0
FROM @testtable1 WHERE ID =@i
Set @g =1
end
ELSE
begin
UPdate @testtable1
SET sequence = @g
FROM @testtable1 WHERE ID =@i
Set @g =@g +1
end
Set @i =@i+1
end
Select * from @testtable1
Thanks
July 19, 2012 at 5:26 am
select a.MicroTestID,a.TestDate,a.Item,a.ciptype
,case when a.ciptype like '%O%' THEN '0'
Else
row_number() over(partition by item,ciptype order by ciptype desc) END as sequence
from
(select MicroTestID,TestDate,Item,ciptype from #t) a
........:-P
July 19, 2012 at 5:39 am
July 19, 2012 at 5:43 am
July 19, 2012 at 8:38 am
Several nice solutions, and as I suspected simpler than I thought! Thank you all. d lewis
July 19, 2012 at 9:15 am
For the record, ColdCoffee's solution was the one I was looking for. I needed something I could wrap into a query, without table functions. The other solution with a case statement did not restart the sequence at each NaOH. Thank you ColdCoffee. d lewis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply