August 26, 2008 at 5:27 pm
Hi
I have a Unique need, even with the cost of performance using Cursors , i could not find any tools to achieve my goal without using Cursor. Could anyone just show me direction?
Here i have attached a table which show statusid( 1=open, 2=Close), Prd=Product, id is the order how the record appears on the table
Scenario: i would like to retrieve row by row data checking if statusid =1 and until it sees
another status id=2 it should consider only one event for all those record and
insert into one table or put into variable( doesnot matter) . The next record with statusid=1 after that last Statusid=2 should be consider new set until again it hits the next status id =2 .So, i am looking at the block by block data retreival order by id(not the status id , they are different)
In simple form , i would like each block of data sequentially which starts with statusid=1 and ends on
status id =2 and after that again move to the next set following the same pattern
May be cursor can be used? , i am not sure, fairly new to Cursor
Any help will be highly appreciated
id creation date Prd statusid
138:16.7A100:00.019:36.42009Jul
248:50.0A100:00.019:36.42009Jul
352:18.0A100:00.019:36.42009Jul
457:28.9A100:00.019:36.42009Jul
501:37.3A100:00.019:36.42009Jul
606:31.1A100:00.019:36.42009Jul
708:01.8B100:00.019:36.42009Jul
811:11.6B1 00:00.019:36.42009Jul
949:31.3B100:00.019:36.42009Jul
1051:31.2B100:00.019:36.42009Jul
1151:34.5B1 00:00.019:36.42009Jul
1257:48.9B1 00:00.019:36.42009Jul
1305:21.4B200:00.019:36.42009Jul
1401:20.7B2 00:00.019:36.42009Jul
1525:03.6A100:00.019:36.42009Jul
1652:58.3A1 :00.019:36.42009Jul
1712:02.6A100:00.019:36.42009Jul
1849:51.4A100:00.019:36.42009Jul
1950:01.3A1 :00.019:36.42009Jul
2053:19.9A100:00.019:36.42009Jul
2119:36.4A2 00:00.019:36.42009Jul
Thanks
Simon
Here i have scripted the table and the data such that it is rerunnable
Thanks for your suggestion, Jeff
----------------------------------------------------------------
---------------------------------------------------------------
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) ,
DateValue DATETIME, Prd nvarchar(250), Statusid INT )
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(ID, DateValue,Prd, Statusid)
SELECT '1','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '2','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '3','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '4','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT '6','Oct 17 2007 12:00AM','B','2' UNION ALL
SELECT '6','Oct 17 2007 12:00AM','B','2' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT '8','Oct 17 2007 12:00AM','A','2'
SET IDENTITY_INSERT #mytable OFF
August 26, 2008 at 7:32 pm
Hi Simon,
You'd probably get a faster, better response if you put your data in a ready to use/test format. Take a look at the link in my signature line below. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2008 at 7:43 am
You can avoid a cursor.
Create a temp table with the ID, Prod, Date and Status from the main table, and two extra columns, SetStart and SetEnd, both bit data type.
Then run this on the temp table:
;with CTE (Row, Date, Prd, StatusID, SetFirst, SetLast) as
(select row_number() over (partition by Prd order by Date),
Date, Prd, StatusID, SetFirst, SetLast
from #TempTable)
update C1
set
SetFirst =
case
when C2.row is not null then 1
else 0
end,
SetLast =
case
when C3.row is not null then 1
else 0
end
from CTE C1
left outer join CTE C2 -- The next row is a 2
on C1.Prd = C2.Prd
and C1.Row = C2.Row-1
and C1.StatusID = 1
and C2.StatusID = 2
left outer join CTE C3 -- The prior row is a 2
on C1.Prd = C3.Prd
and C1.Row = C3.Row+1
and C1.StatusID = 2
and C3.StatusID = 1
That should give you what you need. It may need refining, since I don't have your table structure and data to test it on, but it should be okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2008 at 7:10 pm
simon phoenix (8/26/2008)
Here i have scripted the table and the data such that it is rerunnableThanks for your suggestion, Jeff
Good... Here's one more suggestion... don't edit your posts on something like this. People who have already seen the "bad" post won't necessarily look at an edited post because editing doesn't change the submitted date to show an update. Always make a new post on the thread. It will also cause the thread to appear on the "recent posts" selection folks like me use with a new datetime effectively making the post appear near the beginning as a new post would.
Don't get carried away with such "bumps" either... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2008 at 7:14 pm
Simon,
Now that Gus has replied, it's very good forum etiquette/good practice to let him know if it worked or not. If it didn't work, explain why with some good detail and if any error messages show up, be sure to include those.
Heh.. NO Gus... not saying your code has any errors... just trying to help out a newbie. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2008 at 9:15 pm
Hi
Jeff,Gsquared
Thanks for giving me the best suggestions that helped Gsquared to provide me the outstanding logic for my task. It was my first time posting and i was quite skeptical about it, i am amazed there are people like jeff and Gsquared who actually get into the problem , no matter how long and tedious it was! I am impressed. Gsquared , thanks a million
For 'Gsquared'
You have understand my requirment, so it raises my expectation that you could help me nail this down.
your script and logic is outstanding and it was able to tag SetFirst and SetLast with 1. But the problem is that i
I would like to update the temp table like this:
Every time it sees different 'Prd' and different status id it should update accordingly
id 1, if Prd =A, statusid=1, then update setfirst=1 and setlast=1
id 2, if Prd=B, status id=1, then update setfirst=2 and setlast=1
id 3, if Prd=B, statusid =2, then update setfirst=1 and setlast=1
id 4, if Prd=B, statusid=2, then update setfirst=1 and setlast=2
id 5, if Prd=A, statusid=1, then update setfirst=3 and setlast=1
id 6, if Prd=A, statusid=2, then update setfirst=1 and setlast=3
id 7, if Prd=B, statusid=1, then update setfirst=4 and setlast=1
id 8, if Prd=B, statusid=2, then update setfirs=1 and setlast=4
Basically i am trying to distinguish the each block of data which starts from one 'Prd' and statusid=1 and change to different 'Prd' and change to statusid=2 and might again change back to different 'Prd'
and different status orderby id
Business logic : i am trying to see how the case was open on Product Support centre when customer call for troubleshooting . First what 'Prd' name they open the case and as time goes by how the case was handled and how many time the 'Prd' name was changed and how many times it was open and closed under different 'Prd' name
Here i have also attached the data on excel file, hope it might provide some insight
Millions of Thanks Gsquared
Thanks
Simon
August 27, 2008 at 10:21 pm
Thanks for the feedback, Simon... Gus is one of those tenacious posters... you landed a good one;)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2008 at 7:21 am
Jeff Moden (8/27/2008)
Simon,Now that Gus has replied, it's very good forum etiquette/good practice to let him know if it worked or not. If it didn't work, explain why with some good detail and if any error messages show up, be sure to include those.
Heh.. NO Gus... not saying your code has any errors... just trying to help out a newbie. 😀
Jeff, half my code posts aren't tested, because I don't have the data to test them or don't have the time. Trust me, those ones quite regularly have a few errors in them. I think I even had a backwards inequality relationship in one that made it into complete nonsense. (The other half, I have the data and time to test, and those ones work as written.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 7:37 am
The idea of the "Set First" and "Set Last" columns is that they are the first and last records in each set.
Once you have those, what you need to do is select the ones where SetFirst = 1, and assign a row_number to them, and do the same with the SetEnd = 1, then join those together and get everything in between them.
Something like this (adding a "SetID" column, data type = int, and "SetSequence", also int):
;with
Sets1 (ID, SetID1) as
(select id, row_number() over (partition by prd order by date)
from #TempTable
where SetFirst = 1),
Sets2 (ID, SetID2) as
(select id, row_number() over (partition by prd order by date)
from #TempTable
where SetLast = 1)
update TempTable
set SetID = SetID1
from Sets1
inner join Sets2
on Sets1.SetID1 = Sets2.SetID2
inner join #TempTable TempTable
on TempTable.ID between SetID1 and SetID2;
with
SetSeq (ID, SetSequence) as
(select ID,
row_number() over (partition by SetID order by date)
from #TempTable)
update TempTable
set SetSequence = SetSeq.SetSequence
from #TempTable TempTable
inner join SetSeq
on TempTable.ID = SetSeq.ID
The first query breaks them up into sets, based on the SetStart and SetEnd columns and the Date column.
The second query then takes each set and gives it a sequence number within the set.
Is that what you're looking for? Is it clear enough?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 9:28 am
Hi
Gsquared
Your first query for SetFirst and Set last was tagging the setfirst to the record just before the setlast (stausid=2) but i was looking for it to tag for the very first record it found order by id as setfirst=1 and again setfirst =1 immediately after statusid=2
statusid setfirst setlast
1 1 0
1 0 0
1 0 0
2 0 1
1 1 0
1 0 0
2 0 1
Righ now your query is tagging the setfirst=1 one record before it sees statusid=2, that do not represent the section, section is where it first starts and closed
One more question on your second query, is that id same as column 'Row' from the previous #temptable , if not it do not exist in our previous #temptable to select?
Sets1 (ID, SetID1) as
(select id,
Again , GSquared Tons of thanks for spending your valuable , SQL guru time for my tedious task
Thanks
Simon
August 28, 2008 at 9:37 am
In the first query, change:
and C1.Row = C3.Row+1
to:
and C1.Row = C3.Row-1
See if that gets you what you need.
On the second query, I often call the row_number column in a CTE "Row". It's generic for me. Doesn't matter what you call it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 9:39 am
I misunderstood your question about "Row". No, the ID column is the ID from the original table. You'll need to add that to the temp table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 1:11 pm
Hi
Gsquared
Setfirst still missing the tag even after chaning the first query and even SetLast is taging on some place and not in other!
Thanks
Simon
August 28, 2008 at 7:21 pm
GSquared (8/28/2008)
Jeff, half my code posts aren't tested, because I don't have the data to test them or don't have the time. Trust me, those ones quite regularly have a few errors in them. I think I even had a backwards inequality relationship in one that made it into complete nonsense. (The other half, I have the data and time to test, and those ones work as written.)
Guess I have to say that, knowing that, I'm even more impressed with your work. 🙂 It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2008 at 8:27 pm
Jeff Moden (8/28/2008)
It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.
Man, I think that I linked to that article like 20 times today. Now I know why you and Jack put it into your signature!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply