quote:

---

Originally posted by vincent kelly

Problem: (@num1 + @num2) return nothing, even though there is underlying data:


DECLARE contact CURSOR FOR SELECT [Order], [Customer] FROM [MasterConsole].[dbo].[main] WHERE (((main.SLA) Is Null))
Declare @CusID varchar(100)
DECLARE @Ord INT;
DECLARE @OrderDate DATE;
DECLARE @num1 int;
DECLARE @num2 int;

OPEN contact;
FETCH NEXT FROM contact INTO @Ord, @CusID;
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @num1 = days AS '@num1' FROM [MasterConsole].[dbo].[sla] WHERE [sla].Customer = @CusID

SELECT @num2=COUNTED AS '@num2' FROM [MasterConsole].[dbo].Calender WHERE Calender.DATE = (SELECT Main.[Date Received] FROM [MasterConsole].[dbo].[main] WHERE main.[Order] = @Ord AND SLA Is Null)

UPDATE [MasterConsole].[dbo].[main] SET main.SLA = (SELECT Min([Date]) FROM [MasterConsole].[dbo].Calender WHERE [COUNTED] = (@num1 + @num2) AND main.[Order]= @Ord);


END;
CLOSE contact;

nelliegoat

---

UPDATE m
m.SLA = c2.MinDate
FROM [MasterConsole].[dbo].[main] m
INNER JOIN [MasterConsole].[dbo].[sla] s
ON s.Customer = m.Customer
INNER JOIN [MasterConsole].[dbo].Calender c1
ON c1.DATE  = m.[Date Received]
INNER JOIN (SELECT  [Order],[COUNTED], Min([Date])  AS MinDate
            FROM [MasterConsole].[dbo].Calender
            GROUP BY [Order],[COUNTED]
            )c2
ON c2.[Order] = m.[Order]
AND c2.[COUNTED] = s.Days + c1.COUNTED
WHERE m.SLA IS NULL