I am going round in circles with this, can someone point me in the right direction. I am not sure if I am approaching this the correct way.
I have a table with dates, e.g. received, acknowledged, closed. I am trying to calculate the business days using a stored procedure which works for 1 record but is not working for multirow updates (DTS daily import routine appx 40,000 records). Getting the error: Server Msg 512, Level 16, State1 subquery returned more than 1 value.
I think correlated subqueries might solve my problem but I can't figure out the syntax for the trigger to handle 2 date parameters and pass them to the procedure for all the rows that are updated. I acknowledge that the trigger is poorly written but I have hit a brick wall so please be gentle!
Thanks in advance.
----------------------------
CREATE TRIGGER [PostcardDays] ON [tblSubComments]
FOR INSERT, UPDATE
AS
DECLARE @bdays INT
DECLARE @dtFrom CHAR(20)
DECLARE @dtTo CHAR(20)
SET DATEFIRST 1
--CALCULATE BUSINESS DAYS BETWEEN RAISED AND ACKNOWLEDGED
IF UPDATE (dtPostcardSent)
--I AM HAVING DIFFICULTY FIGURING OUT A FIX FOR BELOW
SELECT @dtFrom = (SELECT dtSubCaseReceived FROM inserted)
SELECT @dtTo = (SELECT dtPostCardSent FROM inserted)
SELECT @dtFrom = CONVERT(CHAR(20), @dtFrom, 103) --UK
SELECT @dtTo = CONVERT(CHAR(20), @dtTo, 103) --UK
--PASS DATE PARAMETERS TO STORED PROCEDURE
EXEC busdays @dtFrom, @dtTo, @bdays OUTPUT
--UPDATE TABLE WITH TOTAL NUMBER OF BUSINESS DAYS RETURNED
UPDATE t SET
LPI_1 = @bdays
FROM tblsubcomments AS t INNER JOIN inserted AS i
ON t.subcaseid = i.subcaseid
---------------------------
CREATE PROCEDURE Busdays @dtFrom CHAR(20), @dtTo CHAR(20), @bdays INT OUTPUT
AS
DECLARE @from_dt DATETIME,
DECLARE @to_dt DATETIME,
DECLARE @days INT,
DECLARE @dp INT,
DECLARE @cnt INT
SET @from_dt = CONVERT(DATETIME, @dtFrom, 103)
SET @to_dt = CONVERT(DATETIME, @dtTo, 103)
SET @bdays=0
SET @cnt=0
SET @days=DATEDIFF(d,@from_dt,@to_dt)
--COUNT BUSINESS DAYS AS MON - FRI
WHILE @cnt <= @days
BEGIN
SET @dp=DATEPART(dw,DATEADD(day,@cnt,@from_dt))
IF @dp NOT IN (6, 7)
BEGIN
SET @bdays=@bdays+1
SET @cnt=@cnt+1
END
ELSE
SET @cnt=@cnt+1
END
I have a table with dates, e.g. received, acknowledged, closed. I am trying to calculate the business days using a stored procedure which works for 1 record but is not working for multirow updates (DTS daily import routine appx 40,000 records). Getting the error: Server Msg 512, Level 16, State1 subquery returned more than 1 value.
I think correlated subqueries might solve my problem but I can't figure out the syntax for the trigger to handle 2 date parameters and pass them to the procedure for all the rows that are updated. I acknowledge that the trigger is poorly written but I have hit a brick wall so please be gentle!
Thanks in advance.
----------------------------
CREATE TRIGGER [PostcardDays] ON [tblSubComments]
FOR INSERT, UPDATE
AS
DECLARE @bdays INT
DECLARE @dtFrom CHAR(20)
DECLARE @dtTo CHAR(20)
SET DATEFIRST 1
--CALCULATE BUSINESS DAYS BETWEEN RAISED AND ACKNOWLEDGED
IF UPDATE (dtPostcardSent)
--I AM HAVING DIFFICULTY FIGURING OUT A FIX FOR BELOW
SELECT @dtFrom = (SELECT dtSubCaseReceived FROM inserted)
SELECT @dtTo = (SELECT dtPostCardSent FROM inserted)
SELECT @dtFrom = CONVERT(CHAR(20), @dtFrom, 103) --UK
SELECT @dtTo = CONVERT(CHAR(20), @dtTo, 103) --UK
--PASS DATE PARAMETERS TO STORED PROCEDURE
EXEC busdays @dtFrom, @dtTo, @bdays OUTPUT
--UPDATE TABLE WITH TOTAL NUMBER OF BUSINESS DAYS RETURNED
UPDATE t SET
LPI_1 = @bdays
FROM tblsubcomments AS t INNER JOIN inserted AS i
ON t.subcaseid = i.subcaseid
---------------------------
CREATE PROCEDURE Busdays @dtFrom CHAR(20), @dtTo CHAR(20), @bdays INT OUTPUT
AS
DECLARE @from_dt DATETIME,
DECLARE @to_dt DATETIME,
DECLARE @days INT,
DECLARE @dp INT,
DECLARE @cnt INT
SET @from_dt = CONVERT(DATETIME, @dtFrom, 103)
SET @to_dt = CONVERT(DATETIME, @dtTo, 103)
SET @bdays=0
SET @cnt=0
SET @days=DATEDIFF(d,@from_dt,@to_dt)
--COUNT BUSINESS DAYS AS MON - FRI
WHILE @cnt <= @days
BEGIN
SET @dp=DATEPART(dw,DATEADD(day,@cnt,@from_dt))
IF @dp NOT IN (6, 7)
BEGIN
SET @bdays=@bdays+1
SET @cnt=@cnt+1
END
ELSE
SET @cnt=@cnt+1
END
