SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor


Cursor

Author
Message
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 9730
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
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 9730
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
simon phoenix-479217
simon phoenix-479217
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 488
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211215 Visits: 41977
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. Smile 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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34378 Visits: 9518
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!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211215 Visits: 41977
rbarryyoung (8/28/2008)
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!


Heh... yeah... I got tired of looking it up. Sure wish Steve could find a way to make it a "sticky" in some of the forums.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
steve dassin
steve dassin
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 2077
GSquared (8/28/2008)

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?


Unfortunately this type of problem is not so clear to most users even in its recognition let alone a solution. Sql server doesn't help matters by offering no construct(s) to directly solve the problem. And what is the clearest, simplest way to characterize a problem like this so it becomes easy to communicate about it? This problem, along with relational division, appears to be sqls version of porn. You can't nail down the definition but you know it when you see itSmile

These are two pretty good threads about approaching this type of problem. They do contain spoons but forks as well. FWIW I'm dassin, steve.

'searching for the longest subsequence of ones'
http://tinyurl.com/5g2dgq

'Roll Up IP-Location Database'
http://tinyurl.com/6evlbr

www.beyondsql.blogspot.com
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 9730
I finally had time to test this, and I found something I hadn't noticed before. Your test data (first post) has duplicate IDs and all the dates and times are the same. Is that an accident, or does your real data also have duplicate ID values? Also, the test table has no primary key. Is that true of the real table or just an oversight on the test?

In the hope that your data doesn't actually have those problems, I created the following test and code:


CREATE TABLE #mytable
( ID INT IDENTITY(1,1) ,
DateValue DATETIME, Prd nvarchar(250), Statusid INT )

INSERT INTO #mytable
(DateValue,Prd, Statusid)
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','2' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','B','2' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL
SELECT 'Oct 17 2007 12:00AM','A','2'

update #MyTable
set DateValue = dateadd(minute, ID, DateValue)

alter table #MyTable
add SetFirst bit not null default(0), SetLast bit not null default(0)

;with CTE (Row, Date, Prd, StatusID, SetFirst, SetLast) as
(select row_number() over (partition by Prd order by DateValue),
DateValue, Prd, StatusID, SetFirst, SetLast
from #MyTable)
update C1
set

SetFirst =
case
when C2.row is not null then 1
when not exists
(select *
from #MyTable mt
where mt.prd = c1.prd
and mt.datevalue < c1.date) then 1
else 0
end,

SetLast =
case
when C3.row is not null then 1
when not exists
(select *
from #MyTable mt
where mt.prd = c1.prd
and mt.datevalue > c1.date) then 1
else 0
end
from CTE C1
left outer join CTE C2 -- The prior 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 -- Status 2 and next row is a 1
on C1.Prd = C3.Prd
and C1.Row = C3.Row-1
and C1.StatusID = 2
and C3.StatusID = 1

select *
from #MyTable



Because of the Not Exists tests, it has at least that much row-by-row. There's almost certainly a better way to do that particular test, but I don't have time to mess around with that right now. This way works, at least on this data.

Another thing I noticed about your test data is that you had two StatusID 2 in a row for Prd "B". That seems to indicate a close without an open. I got rid of that for my test, but if it really exists in your database, the code will have to correct for that.

- 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
simon phoenix-479217
simon phoenix-479217
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 488
Hi
GSquared
After runnig your both the query, for set and sequece, this is what i got

ID DateValue Prd statusid setfirst setlast setid setsequence
1 2007-10-17 00:01:00.000 A 1 1 0 1 1
2 2007-10-17 00:02:00.000 A 1 0 0 2 1
3 2007-10-17 00:03:00.000 A 1 0 0 0 1
4 2007-10-17 00:04:00.000 A 1 0 0 0 2
5 2007-10-17 00:05:00.000 B 1 1 0 0 3
6 2007-10-17 00:06:00.000 B 1 0 0 0 4
7 2007-10-17 00:07:00.000 B 1 0 0 0 5
8 2007-10-17 00:08:00.000 B 2 0 1 0 6
9 2007-10-17 00:09:00.000 B 1 1 0 0 7
10 2007-10-17 00:10:00.000 B 2 0 1 0 8
11 2007-10-17 00:11:00.000 A 1 0 0 0 9
12 2007-10-17 00:12:00.000 A 1 0 0 0 10
13 2007-10-17 00:13:00.000 A 1 0 0 0 11
14 2007-10-17 00:14:00.000 A 1 0 0 0 12
15 2007-10-17 00:15:00.000 A 2 0 1 0 13

but i was looking for something like this! and setfirst was good escept for the last Prd A

ID DateValue Prd statusid setfirst setlast setid setsequence
1 2007-10-17 00:01:00.000 A 1 1 0 1 1
2 2007-10-17 00:02:00.000 A 1 0 0 2 1
3 2007-10-17 00:03:00.000 A 1 0 0 0 1
4 2007-10-17 00:04:00.000 A 1 0 0 0 1
5 2007-10-17 00:05:00.000 B 1 1 0 0 1
6 2007-10-17 00:06:00.000 B 1 0 0 0 1
7 2007-10-17 00:07:00.000 B 1 0 0 0 1
8 2007-10-17 00:08:00.000 B 2 0 1 0 1
9 2007-10-17 00:09:00.000 B 1 1 0 0 2
10 2007-10-17 00:10:00.000 B 2 0 1 0 2
11 2007-10-17 00:11:00.000 A 1 1 0 0 3
12 2007-10-17 00:12:00.000 A 1 0 0 0 3
13 2007-10-17 00:13:00.000 A 1 0 0 0 3
14 2007-10-17 00:14:00.000 A 1 0 0 0 3
15 2007-10-17 00:15:00.000 A 2 0 1 0 3

So sequence 1 is one block , Sequence two is another block and Sequence 3 is one more block
and after acheiving this i can trap the first and last record of each sequence which is my ultimate goal
I guess i am not messing up your labor day weekend!
I am really excited and thrilled from your unconditional help for new guy in SQL
Thanks
Simon
simon phoenix-479217
simon phoenix-479217
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 488
Hi
Gsquared
i copied the previous data from sql pane and they are not aligining to each other, i think you can
estimate its order. Please let me know if its not readable
Thanks
Simon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search