January 16, 2009 at 7:49 pm
Understood and much appreciated, Micheal. Thanks. But, look at the data in the example... it's not ranking anything. It's simply building a seqence of how many times a given SomeType appears in sequential order according to the RowNum column...
... I just don't understand why anyone would do such a thing and none of the examples given, so far, seem to fit the description of the data.
The only purpose I can see to this is that (maybe) they simply want to know the maximum number of times SomeType appears in sequence... and they didn't define the second half of the problem (cuz it's simple) which would be to select the max sequence for each SomeType.
I sure hope I'm not driving anyone nuts with this... heh... it's driving me nuts.
Thanks for the ideas folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 8:02 pm
Jeff: do you have any pointers to some of these prior examples? I seem to recall that I understood their reasoning when I read them, but I cannot remember it now.
[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]
January 16, 2009 at 9:08 pm
No, I sure don't, Barry. I looked but I've got so many posts and there's so many wierd post names, I've not been successful at all. Thought I'd found one because the data was laid out in a similar fashion, but they wanted the RowNum ranges on it... not a sequence number.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2009 at 2:01 am
I've got a similar one that I'm working on, that does have a legit business requirement. I don't need a solution, I did figure one out after staring at it for a while
I've got a list of 'events' for different people. I need a sequence of events, by time, but resetting the sequence whenever there's more than 1 day's gap from the previous event. So...
CREATE TABLE #SomeEvents (
RowNum INT IDENTITY,
PersonID INT,
EventDate DATE -- datetime if you're not using SQL 2008
)
INSERT INTO #SomeEvents (PersonID, EventDate)
SELECT 1, '2008/01/01' UNION ALL
SELECT 1, '2008/01/01' UNION ALL
SELECT 1, '2008/01/02' UNION ALL
SELECT 2, '2008/01/02' UNION ALL
SELECT 1, '2008/01/03' UNION ALL
SELECT 2, '2008/01/03' UNION ALL
SELECT 2, '2008/01/04' UNION ALL
SELECT 1, '2008/01/05' UNION ALL
SELECT 1, '2008/01/06' UNION ALL
SELECT 1, '2008/01/07'
And I need this output (order irrelevance)
[font="Courier New"]RowID PersonID EventDate EventGrouping EventSequence
1 1 2008/01/01 1 1
2 1 2008/01/01 1 2
3 1 2008/01/02 1 3
5 1 2008/01/03 1 4
8 1 2008/01/05 2 1
9 1 2008/01/06 2 2
10 1 2008/01/07 2 3
4 2 2008/01/02 1 1
6 2 2008/01/03 1 2
7 2 2008/01/04 1 3[/font]
Now, while I'm not working on data in the hotel industry, I could see them having the same requirement. If there's a gap of more than a day, it's a different stay and hence a different bill.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2009 at 8:52 am
Yeah, that rings true. I am recalling now, that most of these instances seem to be cases of insufficient data that attempts to infer some missing grouping factor by using "sequence" events.
Like for instance, some counter that starts over from 1 with each new user or customer, but does not have the user/customer recorded. So it tries to "infer" the grouping by user based on the restarting of the sequence.
And then of course, use that inferred grouping as the basis for some kind of summary reporting.
The variants of this that I can recall are:
Restarted Counter:
a counter that resets when the missing column changes:
1234 1234 1234...
and not necessarily fixed-length sequence:
123 123456 12 123 1 12345...
Reused IDs
The "missing" column has a value recorded, but they reuse the ID's, so anytime the flag or ID value changes, it indicates a new group, that of course should not be combined with any previous group with this same ID value. Simplest is the "toggle":
AAA BBB AA BBBB AAA B AA...
Frequently more than two values though:
AAA BBBBB CCCC AA BBB...
And the ID's are not necessarily reused in order:
AA BBB CC DDD BB AAA...
Any Previous Value
The customer has been recorded with some value that cannot repeat for a single customer. So any reuse of one of those values (within the current group) must indicate a new customer:
45 4638 329476 4526 2839 8695...
(this one is obviously pretty unreliable.
Any Lower Value
Imagine that you recorded the time of day for a series of events, but not the date. This case tries to infer the change of date from the fact that if the time-of-day ever goes down then it must be a different day:
01:30,04:00,09:15,13:35, 06:55,15:44,22:00, 14:15,...
[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]
January 17, 2009 at 1:11 pm
GilaMonster (1/17/2009)
I've got a similar one that I'm working on, that does have a legit business requirement. I don't need a solution, I did figure one out after staring at it for a while
Ooh... very good and very close... the thing is that Ranking can take care of that one because you're grouping by day. Lordy, I wish I could put my finger on the original request so you could see. All I have is the code remnant that I posted.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2009 at 1:51 pm
Jeff Moden (1/17/2009)
the thing is that Ranking can take care of that one because you're grouping by day.
No, I'm not. A group is any number of events that are a day or less apart. They may be on the same day, they probably won't be. When there's a break of 1 or more days, the next event after the break starts a new group.
I wish is was group by day. That's trivial to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2009 at 3:06 pm
GilaMonster (1/17/2009)
Jeff Moden (1/17/2009)
the thing is that Ranking can take care of that one because you're grouping by day.No, I'm not. A group is any number of events that are a day or less apart. They may be on the same day, they probably won't be. When there's a break of 1 or more days, the next event after the break starts a new group.
I wish is was group by day. That's trivial to do.
Ah... got it... especially if I throw in what you said earlier...
If there's a gap of more than a day, it's a different stay and hence a different bill.
Thanks, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2009 at 7:20 am
Jeff Moden (1/16/2009)
Luke L (1/16/2009)
...to put into a flat file full of repeating groups to send to another organization.Heh.. that's what you did... my question would be, why did they want it that way?
Unfortunately, I was sending the data to a federal agency and didn't really have any control whatsoever on the format... It's that whole our way or the highway bit... They're some of those crazy non Windows using Oracle people, they like to do things the hard way...
-Luke.
January 18, 2009 at 11:27 am
I would like to share my thoughts on the need of such rules.
I have the punch/swipe card data imported from some other system in the following format...
EmployeeNo, CardReaderNo, CardReaderType, PunchDateTime
E1, C1, IN, 21-Jan-2008 11:01:21 134
E1, C1, OUT, 21-Jan-2008 11:10:01 449
E1, C1, IN, 21-Jan-2008 12:04:35 121
E1, C1, OUT, 21-Jan-2008 15:34:05 154
Now, here the business requirement is, a report that shows the employee day wise in/out history in the office...
Employee, Date, InTime, OutTime
E1, 21-Jan-2008, 11:01:21 134, 11:10:01 449
E1, 21-Jan-2008, 12:04:35 121, 15:34:05 154
Now, the business need is to track an employee's productive time in the office.
--Ramesh
January 18, 2009 at 12:08 pm
That's actually not that hard, providing it's impossible to have two sequential in's or out's and the first entry is going in.
Pseudocode:
SELECT * FROM
(SELECT *, ROW_NUMBER OVER (PARTITION BY EmployeeID ORDER BY PunchDateTime) AS Seq FROM Cards WHERE Direction = 'IN') GoingIn
INNER JOIN
(SELECT *, ROW_NUMBER OVER (PARTITION BY EmployeeID ORDER BY PunchDateTime) AS Seq FROM Cards WHERE Direction = 'OUT') GoingOut
ON GoingIn.EmployeeID = GoingOut.EmployeeID and GoingIn.Seq = GoingOut.Seq
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2009 at 1:16 pm
GilaMonster (1/18/2009)
That's actually not that hard, providing it's impossible to have two sequential in's or out's and the first entry is going in.
Is that ever the case though? =).
January 18, 2009 at 1:26 pm
Garadin (1/18/2009)
Is that ever the case though? =).
Maybe. There's more than one company I know where the access control doesn't allow two entrances one after the other. If you swipe a card to go in and that's registered, the system notes that you're 'in'. If you then swipe again to enter, it's rejected.
In reality, that just leads to people leaning over the turnstile to swipe the exit so that they can swipe entry, but it does ensure the system will always have enter - exit - enter - exit for one person
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2009 at 9:46 am
GilaMonster (1/18/2009)
That's actually not that hard, providing it's impossible to have two sequential in's or out's and the first entry is going in.Pseudocode:
SELECT * FROM(SELECT *, ROW_NUMBER OVER (PARTITION BY EmployeeID ORDER BY PunchDateTime) AS Seq FROM Cards WHERE Direction = 'IN') GoingIn
INNER JOIN
(SELECT *, ROW_NUMBER OVER (PARTITION BY EmployeeID ORDER BY PunchDateTime) AS Seq FROM Cards WHERE Direction = 'OUT') GoingOut
ON GoingIn.EmployeeID = GoingOut.EmployeeID and GoingIn.Seq = GoingOut.Seq
Hey Gail, I was just posting an example of "why the people needs to do this?" and "the business rule behind it".
Don't mind, in this case, there are indeed multiple IN entries without having an OUT entry!!
--Ramesh
January 19, 2009 at 10:57 am
Ramesh (1/18/2009)
I would like to share my thoughts on the need of such rules.I have the punch/swipe card data imported from some other system in the following format...
EmployeeNo, CardReaderNo, CardReaderType, PunchDateTime
E1, C1, IN, 21-Jan-2008 11:01:21 134
E1, C1, OUT, 21-Jan-2008 11:10:01 449
E1, C1, IN, 21-Jan-2008 12:04:35 121
E1, C1, OUT, 21-Jan-2008 15:34:05 154
Now, here the business requirement is, a report that shows the employee day wise in/out history in the office...
Employee, Date, InTime, OutTime
E1, 21-Jan-2008, 11:01:21 134, 11:10:01 449
E1, 21-Jan-2008, 12:04:35 121, 15:34:05 154
Now, the business need is to track an employee's productive time in the office.
That's pretty good... thanks Ramesh... but look at the original data I posted again... there's absolutely no hint of such row pairing in that data.
Lot's of good suggestions, folks. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 94 total)
You must be logged in to reply to this topic. Login to reply