Think I finally solved this, went with a different tact started by finding the Min and Max dates by product by day and using the numbers function to produce 30min...

Do you have the DDL for these tables and some sample data?

This work?

DECLARE @A INT = 10,

@B INT = -12;

SELECT@A,

@B,

CASE

WHEN @B < 0 THEN @A + @B

ELSE @A - @B

END;

Right, been able to spend a bit more time on this and revisit and the good news is seems I have something workable uses Luis's code

IF OBJECT_ID(N'dbo.VW_AmendmentsGroupTest1',N'V') IS NOT NULL

DROP...

May also be worth taking a look at using the OUTPUT clause in place of SCOPE_IDENTIY and @@Identity as well

And to answer luis's question I was originally basing it on 30mins from the first amendment, although again had loosely thought about resetting it and taking it as 30mins from...

Thanks guys I'll take a look at these this morning.

The 'requirements' as such aren't set in concrete, 30mins was just a finger in the air estimate on my behalf of...

Think I'm getting closer:

WITH GroupTime (Style,GrpDT)

AS

(

SELECTAT.Style,

DATEADD(HOUR,DATEDIFF(HOUR,0,DATEADD(MINUTE,30,AT.DT)),0)

FROM#AmendTest AS AT

GROUP

BYAT.Style,

DATEADD(HOUR,DATEDIFF(HOUR,0,DATEADD(MINUTE,30,AT.DT)),0)

),

DeriveNew (AmendmentID,Style,GrpDT,GrpNum)

AS

(

SELECTAT.AmendmentID,

AT.Style,

CA1.GrpDT,

DENSE_RANK() OVER (PARTITION BY AT.Style ORDER BY CA1.GrpDT ASC)

FROM#AmendTest AS AT

CROSS APPLY (SELECT DATEADD(HOUR,DATEDIFF(HOUR,0,DATEADD(MINUTE,30,AT.DT)),0)) AS CA1(GrpDT)

INNER

JOINGroupTime GT

ONAT.Style = GT.Style

AND CA1.GrpDT = GT.GrpDT

),

StyleCnt...

Sean Lange (3/12/2014)

Thanks Keith. I would not consider myself a SQL Ninja by any means but thanks for the...

I think I may have misunderstood - if you want it to always be the 30th of each month with the exception of Feb, does what you have not work...

I'm assuming you would like the last day of the month? (March 31st?)

SELECTDATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+N,0))

FROMDBO.GetNums(1,12)

As a side note, is your syntax not incorrect for the UPDATE statement?

UPDATEParts

SET[TimeToTest] = 0.5000

WHEREPartID = 48871;

If you're using a lot of dates where the range can vary from say days to weeks/months/years it's likely the plan being used isn't optimal if the parameters being passed...

I'm guessing it's going to be an issue with parameter sniffing

How are you running the dynamic SQL? Adding your values to a string and then using

EXEC(@SQL)

or a parameterized query like:...

Bit hard for me to check if this works, but what you're looking to do is populate a variable with the returned result from the query. At the moment you're...