The PLC counts objects, when the PLC count reaches 32000 it resets to zero so in the PLC you see 1,2,3,4,5,........31999,32000,0. But the transaction group to SQL only runs...

OK, read the Forum Etiquette and hope this is correct.

DECLARE @T TABLE

(open_front INT

,open_rear INT

,wrap_count INT

,t_stamp DATETIME);

INSERT INTO @T VALUES (0,0,0,'2013-01-01 01:00');

INSERT INTO @T VALUES (1,10,1000,'2013-01-01 02:00');

INSERT INTO @T VALUES (1,20,2000,'2013-01-01...

Haha!!! Put a ISNULL in top line to change no rollover to -32000.

Going to give it a test.

SELECT ISNULL(32000.0-A.open_front + B.open_front+ISNULL(C.[Rollover_Count],-32000),0) AS [Count], A.Date

FROM

(SELECT

ROW_NUMBER()...

OK, found HUGE stumbling block. The code below returns nothing if there is no rollover occuring in that day whereas I need it to return -32000 with that days...

I cant believe it but I MAY have found a solution:

SELECT ISNULL(32000.0-A.wrap_count+B.wrap_count+C.[Rollover_Count],0) AS [Count], A.Date

FROM

(SELECT

ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp)...

Sean,

That is absolutely brilliant. Thank you so much. I should have tried this forum before wasting around 3 days and getting nowhere!!!!!! I have modified the code with...

That worked a treat, thanks so much. 🙂

Thanks Sean,

The name of the function required would have been 99% help, the brilliant article with an example was the icing on the cake !!!! totally worked for me,...

I came up with a method that I believe works but if anyone can tell me a better or simplier way, or critisism, I would appreciate it. I...

EDIT: I have found a method that works but I would still like to know if this is possible on timestamps (latest and earliest per sub-period).

My select is now (MAX(value...

Thanks again ColdCoffee really appreciate it. I would say they are alias for the two "virtual" tables created for the first select to pick from? I have read...

I dont suppose anybody would be kind enough to explain why there is an A and a B at the end of both selects. If they are removed then...

Thank you ColdCoffee that is EXACTLY what I was after. I just now have to work on the where clause. I have wasted 4 hours tring to achieve...

After looking at a few examples I have eventually come up with this:

set datefirst 5

declare @thisdate datetime

declare @fromdate datetime

select @thisdate = getdate()

if convert(varchar,getdate(),108)< '11:59:59' and datepart(dw,getdate())= '7'

begin

select @fromdate...

Ran these through SQL server management studio and it works as it should so I guess I can then do further DATEADD's etc to fine tune the date to...