Can I know how to make Stimulsoft see the table generated by the stored proc at the end of the run?
The code of the Stored Proc is as follows, am I missing something?
Code: Select all
CREATE PROCEDURE [dbo].[WeeklyCollectDeliverJobReport]
(
@month INT,
@year INT,
@CustList NVARCHAR(MAX),
@Activity nchar(1)
)
AS
BEGIN
DECLARE @DateStart DATETIME;
DECLARE @DateEnd DATETIME;
DECLARE @RealDateStart DATETIME;
DECLARE @RealDateEnd DATETIME;
DECLARE @TimeSpan INT;
SET @DateStart = CAST(@year AS nvarchar(MAX))+'-'+CAST(@month AS nvarchar(MAX))+'-01 00:00:00';
SET @DateEnd = DATEADD(DAY, 1, EOMONTH(@DateStart));
SET @RealDateStart=IIF(DATEPART(dw, @DateStart) > 1, DATEADD(DAY, 8 - DATEPART(dw, @DateStart), @DateStart), @DateStart);
SET @RealDateEnd=DATEADD(DAY, 7 - DATEPART(dw,@DateEnd), @DateEnd);
SET @TimeSpan = DATEDIFF(DAY, @RealDateEnd, @RealDateEnd);
CREATE TABLE #JobsCollect
(
JobNo NVARCHAR(MAX) NULL,
JobDateBand INT NULL,
JobDateString NVARCHAR(MAX) NULL,
JobDate DateTime NULL,
CustCode NVARCHAR(4) NULL,
RequestDate DateTime NULL,
ServiceType NVARCHAR(MAX) NULL,
MediaType NVARCHAR(MAX) NULL,
JobType NVARCHAR(2) NULL,
Remarks NVARCHAR(MAX) NULL,
TotalCtn INT NULL,
TotalMat INT NULL,
Staff NVARCHAR(MAX) NULL
)
IF LOWER(@Activity)='c'
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, 1 + (DATEDIFF(DAY, @RealDateStart, DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE))) / 7) AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE j.JobType LIKE '%C%'
AND CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
ELSE IF LOWER(@Activity)='d'
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, 1 + (DATEDIFF(DAY, @RealDateStart, DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE))) / 7) AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE j.JobType LIKE '%D%'
AND CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
ELSE
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, (DATEDIFF(DAY,DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)), @RealDateStart) / 7) + 1 AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
DECLARE @tempDate DATETIME = @RealDateStart;
DECLARE @counter int = 1;
WHILE (DATEADD(DAY, 6, @tempDate) <= @RealDateEnd)
BEGIN
IF (SELECT COUNT(JobNo) FROM #JobsCollect WHERE JobDate BETWEEN @tempDate AND DATEADD(DAY, 6, @tempDate))=0
BEGIN
INSERT INTO #JobsCollect (JobDateBand, JobDateString, Remarks) VALUES (
@counter, CAST(CAST(@tempDate AS DATE) AS NVARCHAR(MAX))+' - '+CAST(CAST(DATEADD(DAY,7,@tempDate) AS DATE) AS NVARCHAR(MAX)),
'<--- NIL ------>')
END
SET @counter = @counter + 1;
SET @tempDate = DATEADD(DAY,7,@tempDate);
END
SELECT * FROM #JobsCollect ORDER BY JobDateString
END