DELETE FROM HourlyScoringHistory_All
WHERE DayID = @DayId AND HourNum = @HourNum AND BatchID = @JobID;
DECLARE @UsersInLiveSegments TABLE
(
UserType varchar(1),
UserCount int
);
INSERT INTO @UsersInLiveSegments
SELECT
cosmosResult.UserType,
sum(cosmosResult.[Count])
FROM
KPI_SegIdUserTypeCountryAggregate cosmosResult INNER JOIN
Segment
ON cosmosResult.SegmentAuthoringId = Segment.SegmentAuthoringId
WHERE Segment.Live = 1
GROUP BY cosmosResult.UserType;
DECLARE @UsersInTestSegments TABLE
(
UserType varchar(1),
UserCount int
);
INSERT INTO @UsersInTestSegments
SELECT
cosmosResult.UserType,
sum(cosmosResult.[Count])
FROM
KPI_SegIdUserTypeCountryAggregate cosmosResult INNER JOIN
Segment
ON cosmosResult.SegmentAuthoringId = Segment.SegmentAuthoringId
WHERE Segment.Live = 0
GROUP BY cosmosResult.UserType;
INSERT INTO HourlyScoringHistory_All
(
DayID,
HourNum,
BatchID,
NumUsersInSegments,
NumUsersInLiveSegments,
NumUsersInTestSegments,
UserType
)
SELECT
@DayId,
@HourNum,
@JobID,
ISNULL(test.UserCount,0)+ISNULL(live.UserCount,0),
ISNULL(live.UserCount,0),
ISNULL(test.UserCount,0),
ISNULL(test.UserType,live.UserType)
FROM
@UsersInTestSegments test FULL OUTER JOIN @UsersInLiveSegments live
ON test.UserType = live.UserType;