July 31, 2020 at 7:42 pm
Hello All,
I have a insert update procedure where I am getting data from 2 procedures and putting the data in to a table.
So procedure 1 has data like this
AgentName
Tickets Closed
Agent1
10
Agent2
2
Agent3
4
Agent4
6
Agent5
9
Agent6
12
Procedure 2 has data like this
AgentName
Active Tickets
Agent1
2
Agent9
3
Agent10
1
Agent2
0
Agent3
0
Agent11
1
So now in my insert update procedure I am getting only matching data into destination table from both the procedures.
But I need to get unmatched data also.
Below is my code
-----------------Procedure 1 Data-----------------
BEGIN
insert into Table1
(
Date1,
Agent,
TicketsCompleted
)
exec Procedure1
declare @TicketsCompleted table
(
Date1 datetime,
TicketsCompleted int
)
insert into @TicketsCompleted
exec Procedure1
update Table1
set TicketsCompleted= ptc.TicketsCompleted
from Table1 eps , @TicketsCompleted ptc
where eps.date1=ptc.Date1
and eps.Agent=ptc.Agent
-----------------Procedure 2 Data-----------------
declare @TicketsActive table
(
Date1 datetime,
TicketsActive int
)
insert into @TicketsActive
exec Procedure2
update Table1
set TicketsActive= ptc.TicketsActive
from Table1 eps , @TicketsActive ptc
where eps.date1=ptc.Date1
and eps.Agent=ptc.Agent
Please help
July 31, 2020 at 8:30 pm
Reading through your SP's something is wrong. Your "Procedure 1 Data" calls "EXEC Procedure1" twice but puts the data into 2 different tables with 2 different sets of columns. That's not gonna work. You are also doing some logic that doesn't make sense as you are comparing columns that don't exist (ptc.Agent for example in Procedure2 Data).
If I am understanding what youw ant to do correctly, you want to get the agent, the open tickets and the completed tickets grouped by date? If so, dump the data from both stored procedures into a temp table (one for each), then do a full outer join on the data. Something similar to:
DECLARE @tes1 TABLE
(
[id]INT
, [oticket] INT
);
DECLARE @tes2 TABLE
(
[id]INT
, [cticket] INT
);
INSERT INTO @tes1
(
[id]
, [oticket]
)
VALUES
(
0
, 0
)
, (
1
, 1
)
, (
2
, 2
);
INSERT INTO @tes2
(
[id]
, [cticket]
)
VALUES
(
0
, 0
)
, (
1
, 1
)
, (
3
, 3
);
SELECT
ISNULL( [main].[id]
, [RJ].[id]
)
, ISNULL([FOJ].[cticket]
, 0
)
, ISNULL([mainFULL].[oticket]
, 0
) AS [oticket]
FROM@tes1 AS [main]
FULL OUTER JOIN@tes2 AS [FOJ]
ON [FOJ].[id] = [main].[id];
I think that is similar to what you are looking to do or am I way out to left field?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply