How can I calculate how many mussels have been shucked?

Question

Post reply

How can I calculate how many mussels have been shucked?

pstarling

Valued Member

Points: 61
More actions
August 24, 2008 at 5:18 pm

#197471

The Situation
I work for a company that farms, harvests and processes Greenshell Mussels.
The 'Mussels Shuckers' get paid per mussel they shuck.
At the shucking station each mussel is counted by an electronic eye and the data is dumped from the SCADA system into a SQL 2005 database every 180 seconds (This time period is variable). Unfortunately the number of mussels shucked is a running count for the shift rather than the sum for the 180 second time period.
The data is stored in the following table:
[ShiftCountersRawData](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[ShiftID] [int] NOT NULL,
[TableID] [int] NOT NULL,
[StationID] [int] NOT NULL,
[EmployeeID] [varchar](12) NULL,
[CounterDateTimeStamp] [datetime] NOT NULL,
[MusselCount] [int] NOT NULL,
[CounterTimeSpan] [int] NULL
The selected data looks:
SELECT top 15
[ShiftID]
,[TableID]
,[StationID]
,[EmployeeID]
,[CounterDateTimeStamp]
,[MusselCount]
,[CounterTimeSpan]
FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]
ORDER BY [ShiftID] ,[TableID],[StationID],[EmployeeID],[CounterDateTimeStamp]
71166522006-12-07 13:13:22.22310261180
71166522006-12-07 13:16:22.33010261180
71166522006-12-07 13:19:22.47010302180
71166522006-12-07 13:22:22.47010388180
71166522006-12-07 13:25:22.48710476180
71166522006-12-07 13:28:22.48710565180
71166522006-12-07 13:31:22.48710648180
71166522006-12-07 13:34:22.48710734180
71166522006-12-07 13:37:22.48710825180
71166522006-12-07 13:40:22.48710917180
71166522006-12-07 13:43:22.50310998180
71166522006-12-07 13:46:22.50311084180
71166522006-12-07 13:49:22.50311179180
71166522006-12-07 13:52:22.50311268180
71166522006-12-07 13:55:22.50311355180
My Question
How do I calculate how many mussels have been shucked in the 180 second span.
I know that the number I need is (NextRecord.MusselCount - CurrentRecord.MusselCount) But do not know how to code it in SQL.
Can someone enlighten me?
Thanks
Paul

Paul

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Try this:

Select [ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]

,MusselCount - Coalesce( (Select B.MusselCount

From [ShiftCountersRawData] B

Where B.ShiftID = A.ShiftID

And B.TableID = A.TableID

And B.StationID = A.StationID

And B.EmployeeID = A.EmployeeID

And B.CounterDateTimeStamp

Between DateAdd(ss, -CounterTimeSpan-5, A.CounterDateTimeStamp)

And DateAdd(ss, -CounterTimeSpan+5, A.CounterDateTimeStamp))

, 0) as [MusselCountDiff]

FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

I'm assuming that you have an index on ([ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]), which you will need in any event.

[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]

Rufio Hall of Fame Points: 3064 More actions · Answer 2

with RawData (EmpRow, ShiftID, TableID, StationID, EmployeeID, MusselCount, CounterDateTimeStamp) as (

select

row_number() over (partition by EmployeeID order by ShiftID, TableID, StationID),

ShiftID,

TableID,

StationID,

EmployeeID,

MusselCount,

CounterDateTimeStamp

from

ShiftCountersRawData)

select

a.ShiftID,

a.TableID,

a.StationID,

a.CounterDateTimeStamp,

a.EmployeeID,

(a.MusselCount - b.MusselCount) MusselCount,

datediff(s, b.CounterDateTimeStamp, a.CounterDateTimeStamp) CounterPeriod

from

RawData a

left join

RawData b on

a.EmpRow = b.EmpRow + 1 and

a.EmployeeID = b.EmployeeID

where

b.MusselCount is not null

For further reading look at this article

http://www.sqlservercentral.com/articles/T-SQL/62159/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Oops, I got the order of the last 2 fields of the index wrong. Should be:

[ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[CounterTimeSpan]

,[MusselCount]

[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]

pstarling Valued Member Points: 61 More actions · Answer 4

Thanks for the code but unfortunately it returned zero rows.

Paul

pstarling Valued Member Points: 61 More actions · Answer 5

Hi Rufio,

Firstly:

Thanks for the pointer to the article. It was functionality that I was unaware of in SQL Server.

Secondly:

Your code seems to be almost there.

I chose some raw data:

SELECT [ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]

FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

where ShiftID = 7

and TableID = 1

and StationID =1

and EmployeeID = 6652

ORDER BY [ShiftID] ,[TableID],[StationID],[EmployeeID],[CounterDateTimeStamp]

which returned:

ShiftID TableID StationID EmployeeID CounterDateTimeStamp MusselCount CounterTimeSpan

----------- ----------- ----------- ------------ ----------------------- ----------- ---------------

7 1 1 6652 2006-12-07 13:13:22.223 10261 180

7 1 1 6652 2006-12-07 13:16:22.330 10261 180

7 1 1 6652 2006-12-07 13:19:22.470 10302 180

7 1 1 6652 2006-12-07 13:22:22.470 10388 180

7 1 1 6652 2006-12-07 13:25:22.487 10476 180

7 1 1 6652 2006-12-07 13:28:22.487 10565 180

7 1 1 6652 2006-12-07 13:31:22.487 10648 180

7 1 1 6652 2006-12-07 13:34:22.487 10734 180

7 1 1 6652 2006-12-07 13:37:22.487 10825 180

7 1 1 6652 2006-12-07 13:40:22.487 10917 180

7 1 1 6652 2006-12-07 13:43:22.503 10998 180

7 1 1 6652 2006-12-07 13:46:22.503 11084 180

7 1 1 6652 2006-12-07 13:49:22.503 11179 180

7 1 1 6652 2006-12-07 13:52:22.503 11268 180

7 1 1 6652 2006-12-07 13:55:22.503 11355 180

7 1 1 6652 2006-12-07 13:58:22.503 11436 180

7 1 1 6652 2006-12-07 14:01:22.673 11521 180

7 1 1 6652 2006-12-07 14:04:22.767 11600 180

7 1 1 6652 2006-12-07 14:07:22.770 11684 180

7 1 1 6652 2006-12-07 14:10:22.770 11763 180

7 1 1 6652 2006-12-07 14:13:22.770 11839 180

7 1 1 6652 2006-12-07 14:16:22.770 11894 180

7 1 1 6652 2006-12-07 14:19:22.783 11972 180

7 1 1 6652 2006-12-07 14:22:22.783 12005 180

7 1 1 6652 2006-12-07 14:25:22.783 12025 180

7 1 1 6652 2006-12-07 14:28:22.783 12025 180

7 1 1 6652 2006-12-07 14:31:22.863 12025 180

7 1 1 6652 2006-12-07 14:34:22.923 12025 180

7 1 1 6652 2006-12-07 14:37:22.923 12025 180

7 1 1 6652 2006-12-07 14:40:22.923 12025 180

7 1 1 6652 2006-12-07 14:43:22.923 12025 180

7 1 1 6652 2006-12-07 14:46:23.050 12025 180

7 1 1 6652 2006-12-07 14:49:23.177 12025 181

7 1 1 6652 2006-12-07 14:52:23.190 12025 180

7 1 1 6652 2006-12-07 14:55:23.190 12025 180

7 1 1 6652 2006-12-07 14:58:23.190 12025 180

7 1 1 6652 2006-12-07 15:01:23.190 12025 180

7 1 1 6652 2006-12-07 15:04:23.207 12025 180

7 1 1 6652 2006-12-07 15:07:23.207 12025 180

7 1 1 6652 2006-12-07 15:10:23.207 12025 180

7 1 1 6652 2006-12-07 15:13:23.207 12025 180

7 1 1 6652 2006-12-07 15:16:23.207 12025 180

7 1 1 6652 2006-12-07 15:18:39.440 12025 136

(43 row(s) affected)

With the same 'where' clause your excellent code returned:

ShiftID TableID StationID CounterDateTimeStamp EmployeeID MusselCount CounterPeriod

----------- ----------- ----------- ----------------------- ------------ ----------- -------------

7 1 1 2006-12-07 13:16:22.330 6652 0 180

7 1 1 2006-12-07 13:19:22.470 6652 41 180

7 1 1 2006-12-07 13:22:22.470 6652 86 180

7 1 1 2006-12-07 13:25:22.487 6652 88 180

7 1 1 2006-12-07 13:28:22.487 6652 89 180

7 1 1 2006-12-07 13:31:22.487 6652 83 180

7 1 1 2006-12-07 13:34:22.487 6652 86 180

7 1 1 2006-12-07 13:37:22.487 6652 91 180

7 1 1 2006-12-07 13:40:22.487 6652 92 180

7 1 1 2006-12-07 13:43:22.503 6652 81 180

7 1 1 2006-12-07 13:46:22.503 6652 86 180

7 1 1 2006-12-07 13:49:22.503 6652 95 180

7 1 1 2006-12-07 13:52:22.503 6652 89 180

7 1 1 2006-12-07 13:55:22.503 6652 87 180

7 1 1 2006-12-07 13:58:22.503 6652 81 180

7 1 1 2006-12-07 14:01:22.673 6652 85 180

7 1 1 2006-12-07 14:04:22.767 6652 79 180

7 1 1 2006-12-07 14:07:22.770 6652 84 180

7 1 1 2006-12-07 14:10:22.770 6652 79 180

7 1 1 2006-12-07 14:13:22.770 6652 76 180

7 1 1 2006-12-07 14:16:22.770 6652 55 180

7 1 1 2006-12-07 14:19:22.783 6652 78 180

7 1 1 2006-12-07 14:22:22.783 6652 33 180

7 1 1 2006-12-07 14:25:22.783 6652 20 180

7 1 1 2006-12-07 14:28:22.783 6652 0 180

7 1 1 2006-12-07 14:31:22.863 6652 0 180

7 1 1 2006-12-07 14:34:22.923 6652 0 180

7 1 1 2006-12-07 14:37:22.923 6652 0 180

7 1 1 2006-12-07 14:40:22.923 6652 0 180

7 1 1 2006-12-07 14:43:22.923 6652 0 180

7 1 1 2006-12-07 14:46:23.050 6652 0 181

7 1 1 2006-12-07 14:49:23.177 6652 0 180

7 1 1 2006-12-07 14:52:23.190 6652 0 180

7 1 1 2006-12-07 14:55:23.190 6652 0 180

7 1 1 2006-12-07 14:58:23.190 6652 0 180

7 1 1 2006-12-07 15:01:23.190 6652 0 180

7 1 1 2006-12-07 15:04:23.207 6652 0 180

7 1 1 2006-12-07 15:07:23.207 6652 0 180

7 1 1 2006-12-07 15:10:23.207 6652 0 180

7 1 1 2006-12-07 15:13:23.207 6652 0 180

7 1 1 2006-12-07 15:16:23.207 6652 0 180

7 1 1 2006-12-07 15:18:39.440 6652 0 136

(42 row(s) affected)

However as you can see above, the code assumes that the count starts from zero which may not always be the case, it depends if the supervisor remembers to start the shift on the computer as well as on the SCADA system. If they 'forget' the counter system picks up at the current count, in this case 10261 mussels.

Can I impose upon your good will to include this senario in the code?

Regards

Paul

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

pstarling (8/25/2008)
Thanks for the code but unfortunately it returned zero rows.

That cannot possibly be the fault of my code. You will note the lack of a where clause, a join or any other means of filtering the data at all. I grant you that my code could be in error, or could return the wrong results, but if it returns NO rows, that must be because of something that you did.

[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]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

Here is my contribution:

declare @ShiftCountersRawData table(

RecID int IDENTITY(1,1) NOT NULL,

ShiftID int NOT NULL,

TableID int NOT NULL,

StationID int NOT NULL,

EmployeeID varchar(12) NULL,

CounterDateTimeStamp datetime NOT NULL,

MusselCount int NOT NULL,

CounterTimeSpan int NULL

);

Insert @ShiftCountersRawData

(ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp, MusselCount, CounterTimeSpan)

select 7, 1, 1, 6652, '2006-12-07 13:13:22.223', 10261, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:16:22.330', 10261, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:19:22.470', 10302, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:22:22.470', 10388, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:25:22.487', 10476, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:28:22.487', 10565, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:31:22.487', 10648, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:34:22.487', 10734, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:37:22.487', 10825, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:40:22.487', 10917, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:43:22.503', 10998, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:46:22.503', 11084, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:49:22.503', 11179, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:52:22.503', 11268, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:55:22.503', 11355, 180union all

select 7, 1, 1, 6652, '2006-12-07 13:58:22.503', 11436, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:01:22.673', 11521, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:04:22.767', 11600, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:07:22.770', 11684, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:10:22.770', 11763, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:13:22.770', 11839, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:16:22.770', 11894, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:19:22.783', 11972, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:22:22.783', 12005, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:25:22.783', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:28:22.783', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:31:22.863', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:34:22.923', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:37:22.923', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:40:22.923', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:43:22.923', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:46:23.050', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:49:23.177', 12025, 181union all

select 7, 1, 1, 6652, '2006-12-07 14:52:23.190', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:55:23.190', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 14:58:23.190', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:01:23.190', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:04:23.207', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:07:23.207', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:10:23.207', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:13:23.207', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:16:23.207', 12025, 180union all

select 7, 1, 1, 6652, '2006-12-07 15:18:39.440', 12025, 136;

-- Now find out how many mussels a mussleshucker could shuck if a mussleshucker could shuck mussles.

-- (Sorry 'bout that :))

select s1.ShiftID, s1.TableID, s1.StationID, s1.EmployeeID,

s1.CounterDateTimeStamp as CounterStart, s2.CounterDateTimeStamp as CounterEnd,

s1.MusselCount as StartingMusselCount, s2.MusselCount as EndingMusselCount,

s2.MusselCount - s1.MusselCount as MusselCount

from @ShiftCountersRawData s1

join @ShiftCountersRawData s2

on s1.ShiftID = s2.ShiftID

and s1.TableID = s2.TableID

and s1.StationID = s2.StationID

and s1.EmployeeID = s2.EmployeeID

and s2.CounterDateTimeStamp = (

select Min( s3.CounterDateTimeStamp )

from @ShiftCountersRawData s3

where s3.CounterDateTimeStamp > s1.CounterDateTimeStamp

and s3.ShiftID = s1.ShiftID

and s3.TableID = s1.TableID

and s3.StationID= s1.StationID

and s3.EmployeeID= s1.EmployeeID

group by s3.ShiftID, s3.TableID, s3.StationID, s3.EmployeeID

)

order by s1.ShiftID, s1.TableID, s1.StationID, s1.EmployeeID, s1.CounterDateTimeStamp

You'll notice there is no assumption that the counter is reset to zero. I have displayed more fields than you need, but you will get a better picture of what is going on.

For performance, create a covering index on ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp. In fact, as the Identity field may rarely be used in ordering, you will probably get a bit more performance if you define the PK index nonclustered and create the covering index clustered. In fact, although the query above seems to be quite convoluted, you will get impressive performance from it (with the index) even if the table is quite large.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

pstarling Valued Member Points: 61 More actions · Answer 8

Thanks for all your help, as the man in the Visa advert said 'That will do nicely Sir'

Re : Now find out how many mussels a mussleshucker could shuck if a mussleshucker could shuck mussles. :w00t:

Just to show how fast these guys actually are, in 2007 one of our staff, Kannha Keo, set a Guinness World record of 2 min 11 sec for opening 100 mussels, beating her brother who set it the year before. Thats a mussel every 1.3 seconds if you can imagine that.

The clicp is at: http://community.guinnessworldrecords.com/kickapps/_Mussel-opening-fastest-100/video/93225/7691.html

Paul

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Tomm Carr (8/26/2008)
Here is my contribution:

Ummmm... nicely done, but be careful. If you take a look at the actual execution plan, there are two flow arrows of great concern... one with 1849 rows and the other 902. Notice that there are only 43 original rows of data and that 1849 = 43² which is a full "Square" or Cartesian Join and 903 = ((43-1)²+(43-1))/2 which is a Triangular join.

If anyone wants to know more about "Triangular Joins", please take a look at the article at the following link...

http://www.sqlservercentral.com/articles/T-SQL/61539/

Perhaps something like this, instead...

;With

cteMussels AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY ShiftID, TableID, StationID, EmployeeID, DATEADD(dd,DATEDIFF(dd,0,CounterDateTimeStamp),0)

ORDER BY ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp) AS RowNum,

*

FROM #ShiftCountersRawData

)

SELECT m1.ShiftID, m1.TableID, m1.StationID, m1.EmployeeID,

m1.CounterDateTimeStamp AS StartTime,

m2.CounterDateTimeStamp AS EndTime,

CONVERT(CHAR(12),m2.CounterDateTimeStamp-m1.CounterDateTimeStamp,114) AS Duration,

m1.MusselCount AS StartCount,

m2.MusselCount AS EndCount,

m2.MusselCount-m1.MusselCount AS PeriodCount

FROM cteMussels m1

INNER JOIN cteMussels m2 ON m1.RowNum+1 = m2.RowNum

Because of the relatively low original row count, it won't make much of a difference in performance, but it will cut the scan counts from 45 down to 2 and the logical reads from 87 down to 2.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff, dude:

Jeff Moden (8/26/2008)
SELECT ROW_NUMBER() OVER (PARTITION BY ShiftID, TableID, StationID, EmployeeID, DATEADD(dd,DATEDIFF(dd,0,CounterDateTimeStamp),0) ORDER BY ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp) AS RowNum, *

... Uhm, ... what the blankety-blank is this !?!?!

[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]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

Jeff, you are correct. However, I have to avoid things like CTEs. Not because I have anything against them but because 90% of my dbs are still SS2k, not to mention the Oracle side.

The query I submitted follows a pattern I developed about 8 months ago (though I doubt it is original with me) for joining a row with the chronologically next row of a table. The key to performance is the index (hmm, come to think of it, isn't it almost always the index? :)).

Change the table definition in my code from a table variable to a permanent table. Like this:create table ShiftCountersRawData (

ShiftID int NOT NULL,

TableID int NOT NULL,

StationID int NOT NULL,

EmployeeID varchar(12) NOT NULL,

CounterDateTimeStamp datetime NOT NULL,

MusselCount int NOT NULL,

CounterTimeSpan int NULL,

constraint PK_ShiftCountersRawData primary key (ShiftID, TableID,

StationID, EmployeeID, CounterDateTimeStamp)

);

I've eliminated the Identity key field because it is not needed by this query (indeed, I have a feeling it is not used much at all in any of that company's queries) and the PK index is also the covering index. Change the query only to remove the "@" from the table names and look at the actual execution plan. Viola!

I use this pattern on tables with over a million records. Performance is excellent. The index is critical, of course. With it, you get the bear :smooooth:; without it, the bear gets you :crying:. In fact, in answer possibly to Barry's question, isn't that what you are accomplishing on the fly with the partition -- getting the search range down to one day?

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

noeld SSC Guru Points: 96590 More actions · Answer 12

I felt that I had to post my version 😀

SELECT a.ShiftID

, a.TableID

, a.StationID

, a.EmployeeID

, a.CounterDateTimeStamp

, a.MusselCount

, a.CounterTimeSpan

,(SELECT TOP 1 b.MusselCount

FROM dbo.ShiftCountersRawData b

WHERE b.ShiftID = a.ShiftID

AND b.TableID = a.TableID

AND b.StationID = a.StationID

AND b.EmployeeID = a.EmployeeID

AND b.CounterDateTimeStamp > a.CounterDateTimeStamp

ORDER BY b.CounterDateTimeStamp )

- a.MusselCount as MusselCountDiff

FROM dbo.ShiftCountersRawData a

ORDER BY a.ShiftID, a.TableID, a.StationID, a.EmployeeID, a.CounterDateTimeStamp

* Noel

pstarling Valued Member Points: 61 More actions · Answer 13

Hi Guys,

Thanks for the continued input. I stopped DBA'ing about five years ago hence a bit rusty.

I'll test all the solutions against the database (c2M rows) and post back the best performing.

For those that are interested, the database server is a HP DL385 with 2 x AMD Dual Core Dual Processor,4GB RAM running on Windows Server 2003 Standard using SQL Server 2005 latest patch. The OS and logs are mirrored, tempdb separate and the data is spread over 8 disks (RAID5) in attached an HP MSA 500 box.

Paul

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Modified version: for some reason the Coalesce() makes the optimizer go stupid:

Select [ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]

,MusselCount - (Select TOP 1 B.MusselCount

From [ShiftCountersRawData] B

Where B.ShiftID = A.ShiftID

And B.TableID = A.TableID

And B.StationID = A.StationID

And B.EmployeeID = A.EmployeeID

And B.CounterDateTimeStamp

Between DateAdd(ss, -CounterTimeSpan-5, A.CounterDateTimeStamp)

And DateAdd(ss, -CounterTimeSpan+5, A.CounterDateTimeStamp))

as [MusselCountDiff]

FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

[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]