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 12345»»»

Cursor Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2008 5:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 1:01 PM
Points: 57, Visits: 488
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
1 38:16.7 A 1 00:00.0 19:36.4 2009 Jul
2 48:50.0 A 1 00:00.0 19:36.4 2009 Jul
3 52:18.0 A 1 00:00.0 19:36.4 2009 Jul
4 57:28.9 A 1 00:00.0 19:36.4 2009 Jul
5 01:37.3 A 1 00:00.0 19:36.4 2009 Jul
6 06:31.1 A 1 00:00.0 19:36.4 2009 Jul
7 08:01.8 B 1 00:00.0 19:36.4 2009 Jul
8 11:11.6 B 1 00:00.0 19:36.4 2009 Jul
9 49:31.3 B 1 00:00.0 19:36.4 2009 Jul
10 51:31.2 B 1 00:00.0 19:36.4 2009 Jul
11 51:34.5 B 1 00:00.0 19:36.4 2009 Jul
12 57:48.9 B 1 00:00.0 19:36.4 2009 Jul
13 05:21.4 B 2 00:00.0 19:36.4 2009 Jul
14 01:20.7 B 2 00:00.0 19:36.4 2009 Jul
15 25:03.6 A 1 00:00.0 19:36.4 2009 Jul
16 52:58.3 A 1 :00.0 19:36.4 2009 Jul
17 12:02.6 A 1 00:00.0 19:36.4 2009 Jul
18 49:51.4 A 1 00:00.0 19:36.4 2009 Jul
19 50:01.3 A 1 :00.0 19:36.4 2009 Jul
20 53:19.9 A 1 00:00.0 19:36.4 2009 Jul
21 19:36.4 A 2 00:00.0 19:36.4 2009 Jul

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



Post #559271
Posted Tuesday, August 26, 2008 7:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #559303
Posted Wednesday, August 27, 2008 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #559635
Posted Wednesday, August 27, 2008 7:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
simon phoenix (8/26/2008)
Here i have scripted the table and the data such that it is rerunnable

Thanks 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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560058
Posted Wednesday, August 27, 2008 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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. :D


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560060
Posted Wednesday, August 27, 2008 9:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 1:01 PM
Points: 57, Visits: 488
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


  Post Attachments 
Routing_Acccuracy_Sample_data.xlsx (11 views, 10.94 KB)
Post #560098
Posted Wednesday, August 27, 2008 10:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
Thanks for the feedback, Simon... Gus is one of those tenacious posters... you landed a good one;)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560110
Posted Thursday, August 28, 2008 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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. :D


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
Post #560404
Posted Thursday, August 28, 2008 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #560426
Posted Thursday, August 28, 2008 9:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 1:01 PM
Points: 57, Visits: 488
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
Post #560592
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse