April 25, 2013 at 3:14 am
I have got two stored procedures as below:
ALTER PROCEDURE [rept].[systemActivities]
-- Add the parameters for the stored procedure here
@client VARCHAR(50),
@startDate DATETIME,
@endDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @results AS TABLE (
[subId] INT,
[total] INT
)
DECLARE
@SubIDint,
@queryStringvarchar(8000),
@currentDateDATETIME,
@jcStartint,
@jcEndint
TRUNCATE TABLE rept.tblActivityList
DECLARE crSystems CURSOR FOR
SELECT
CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int) AS SystemID -- Returning the substring as a integer of the table name.
FROM DB3.information_schema.tables
WHERE
LEFT(TABLE_NAME,6) = 'tblAct'
AND LEN(TABLE_NAME) = 11 -- Avoids tblAct0, tblAct1
AND TABLE_NAME <> 'tblActError' -- Needs to be excluded
AND @client =
(
SELECT TOP(1)
dbo.tblClient.ClientNumber
FROM DB_admin1.dbo.tblClient
INNER JOIN DB_admin1.dbo.tblSystem
ON DB_admin1.dbo.tblSystem.ClientNumber = DB_admin1.dbo.tblClient.ClientNumber
WHERE SystemNum = CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int)
)
ORDER BY TABLE_NAME --Probably better to process the entries in number order
OPEN crSystems
FETCH NEXT FROM crSystems
INTO @SubID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Checking system ' + CAST(@subID AS varchar(10))
SET @currentDate = @startDate
WHILE @currentDate < @endDate
BEGIN
PRINT @currentDate
-- Calculate JCStartDateTime, JCEndDateTime
SET @jcStart = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(@currentDate)
SET @jcEnd = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(DATEADD(DAY, 1, @currentDate))
DECLARE @sql AS varchar(8000)
SET @sql =
'INSERT INTO rept.tblActivityList
([systemNum]
,[dtuStart]
,[dtuEnd]
,[total])
VALUES (
' + CAST(@subID AS varchar(10)) + ',
''' + CONVERT(varchar(10), @currentDate, 20) + ''',
DATEADD(DAY, -1, DATEADD(MONTH, 1, ''' + CONVERT(varchar(10), @currentDate, 20) + ''')),
(
SELECT COUNT (*)
FROM DB3.dbo.tblAct' + CAST(@subID AS varchar(10)) + '
WHERE actTimeDate BETWEEN ' + CAST(@jcStart AS varchar(10)) + ' AND ' + CAST(@jcEnd AS varchar(10)) + '
)
)'
--PRINT @sql
EXEC (@sql)
SET @currentDate = DATEADD(MONTH, 1, @currentDate)
END
FETCH NEXT FROM crSystems
INTO @SubID
END
CLOSE crSystems;
DEALLOCATE crSystems;
-- Group the results.
SELECT [dtuStart]
,COUNT(systemNum) AS total
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
END
AND
ALTER PROCEDURE [rept].[spSubscribedSystems]
-- Add the parameters for the stored procedure here
@startDate DATETIME,
@endDate DATETIME,
@client INT
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE rept.tblSubscribedSystems
DECLARE @systemSubcriptions AS TABLE(
SystemNum int,
startDate datetime2(0),
endDate datetime2(0)
)
;WITH cteRenewals AS (
-- Rank the renewals in order by System.
SELECT [UniqueId]
,[dbo].[tblRenewals].[SystemNum]
,[Date]
,[Invoice]
,[Renewed]
,[dbo].[tblClient].[ClientNumber]
,RANK() OVER(PARTITION BY [dbo].[tblRenewals].SystemNum ORDER BY UniqueID ASC) as seq
FROM [DB_admin1].[dbo].[tblRenewals]
INNER JOIN dbo.tblSystem ON dbo.tblRenewals.SystemNum = dbo.tblSystem.SystemNum
INNER JOIN dbo.tblClient ON dbo.tblSystem.ClientNumber = dbo.tblClient.ClientNumber
WHERE [dbo].[tblRenewals].SystemNum >= 10000 AND dbo.tblClient.ClientNumber = 1006--@client
),
cteRenewalsWithEndSubscriptionLength AS (
-- Join back on itself, but join to previous sequence
SELECT
ren1.UniqueId,
ren1.SystemNum,
ren2.[Date] AS startDate,
ren1.[Date] AS endDate,
DATEDIFF(DAY, ren2.[Date], ren1.[Date]) AS subLength,
ren1.Invoice,
ren1.Renewed,
ren1.seq AS seq1,
ren2.seq AS seq2
FROM cteRenewals ren1
INNER JOIN cteRenewals ren2
ON ren1.SystemNum = ren2.SystemNum
AND ren1.seq - 1 = ren2.seq -- Disjoint to find previous record for that system
WHERE ren1.seq > 1
)
INSERT INTO @systemSubcriptions
SELECT SystemNum, startDate, endDate
FROM cteRenewalsWithEndSubscriptionLength
WHERE subLength BETWEEN 0 AND 3000-- Ignoring systems with strange values for subscription length
DECLARE
@currentDate datetime2(0),
@monthEnd datetime2(0),
@monthStart datetime2(0)
SET @currentDate = @startDate
-- Loop around each monthn
WHILE @currentDate < @endDate
BEGIN
SET @monthStart = @currentDate
SET @monthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, @currentDate))
-- Insert any subscribed system in that month into the report table
INSERT INTO rept.tblSubscribedSystems
SELECT
SystemNum,
@monthStart AS startDate,
@monthEnd AS endDate
FROM @systemSubcriptions sysSubs
WHERE
sysSubs.startDate <= @monthEnd--DATEADD(DAY, 7, @monthStart)--@monthEnd
AND
sysSubs.endDate >= @monthStart
SET @currentDate = DATEADD(MONTH, 1, @currentDate)
END
-- Summarise by start date, total number of systems.
SELECT
dtuStart,
COUNT(DISTINCT(systemNum)) AS [Total Systems]
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
END
When I run both stored procedures by executing :
DECLARE @return_value int
EXEC @return_value = [rept].[systemActivities]
@client = N'1060',
@startDate = N'2000-01-01',
@endDate = N'2020-01-01'
GO
-- Subscription data:
DECLARE @return_value int
EXEC @return_value = [rept].[spSubscribedSystems]
@client = N'1060',
@startDate = N'2000-01-01',
@endDate = N'2020-01-01'
I get the result set:
What I want is to combine both stored procedures.
So in the new result set it will show:
dtuStart and Total Systems from the [rept].[spSubscribedSystems] with the total from the [rept].[systemActivities]
April 25, 2013 at 6:31 am
Just an idea, maybe not the perfect solution but I might change both of these routines like so:
systemActivities:
INSERT INTO #RESULTS
(
ROW_SOURCE,
dtuStart,
total
)
SELECT
'systemactivities' ROW_SOURCE,
[dtuStart]
,COUNT(systemNum) AS total
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
spsubscribedsystems:
INSERT INTO #RESULTS
(
ROW_SOURCE,
dtuStart,
total
)
SELECT
'spsubscribedsystems' ROW_SOURCE
dtuStart,
COUNT(DISTINCT(systemNum)) AS [Total Systems]
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
and then create a third routine to call them both after creating temp table #RESULTS
CREATE PROCEDURE callthetwo
AS
BEGIN
CREATE TABLE #RESULTS
(
ROW_SOURCE VARCHAR(50),
dtuStart VARCHAR(50), -- seems like this is varchar from your dynamic sql snippit
total int
)
EXEC systemActivities
EXEC spsubscribedsystems
-- do whatever with result set in #RESULTS
END
April 25, 2013 at 6:43 am
You can't do much with the results of a stored procedure, other than insert them into a table. You'd be better rewriting the procs as functions, the results of which you can manipulate as you please by JOINing, UNIONing and so on.
John
April 25, 2013 at 6:47 am
Hi Guys, I have managed to got it all working. I am inserting values into 2 different tables in the stored procedure. I have simply joined these two tables to get the results I was looking for.
if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..##temp1')
)
DROP TABLE ##temp1;
if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..##temp2')
)
DROP TABLE ##temp2;
SELECT [dtuStart]
,COUNT(systemNum) AS [Total Activities]
INTO ##temp1
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
SELECT dtuStart
,COUNT(DISTINCT (systemNum)) AS [Total Number of Systems]
INTO ##temp2
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
SELECT ##temp2.dtuStart AS [Start Date]
,##temp1.[Total Activities]
,##temp2.[Total Number of Systems]
FROM ##temp1
FULL OUTER JOIN ##temp2 ON ##temp1.dtuStart = ##temp2.dtuStart
ORDER BY [Start Date]
Thank you for your time and suggestions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy