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 Thursday, August 28, 2008 9:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #560603
Posted Thursday, August 28, 2008 9:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #560606
Posted Thursday, August 28, 2008 1:11 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
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
Post #560763
Posted Thursday, August 28, 2008 7: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 @ 6:46 PM
Points: 36,944, Visits: 31,446
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
"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 #560940
Posted Thursday, August 28, 2008 8:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #560958
Posted Thursday, August 28, 2008 10:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(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 #560987
Posted Thursday, August 28, 2008 10:59 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 6:02 PM
Points: 174, Visits: 2,075
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 it:)

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
Post #560991
Posted Friday, August 29, 2008 8:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #561221
Posted Friday, August 29, 2008 2:44 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
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

Post #561525
Posted Friday, August 29, 2008 4:33 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
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
Post #561562
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse