USE [Membership]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_NWAgentSignUp_DstSales]
-- Stored Procedure Parameters --
@MemYear NVARCHAR(4)
AS
BEGIN
-- Added to Prevent Extra Result Sets From Interfering With "SELECT" Statements --
SET NOCOUNT ON;
-- Variable Declaration --
DECLARE @LoDateStamp DATETIME
DECLARE @HiDateStamp DATETIME
DECLARE @QuotaYear INT
-- Initialize Quota Year --
SET @QuotaYear = YEAR(CAST(@MemYear+' 12:00:00' AS DATETIME))
-- Create the Main Table (Returned) --
DECLARE @MainTable TABLE
(tt_distcode nvarchar(6),
tt_mgrname nvarchar(30),
tt_distquota int,
tt_agentno nvarchar(9),
tt_ctyname nvarchar(20),
tt_agtfullname nvarchar(65),
tt_count1 int NOT NULL DEFAULT 0,
tt_count2 int NOT NULL DEFAULT 0,
tt_count3 int NOT NULL DEFAULT 0,
tt_count4 int NOT NULL DEFAULT 0,
tt_count5 int NOT NULL DEFAULT 0,
tt_count6 int NOT NULL DEFAULT 0,
tt_count7 int NOT NULL DEFAULT 0,
tt_count8 int NOT NULL DEFAULT 0,
tt_count9 int NOT NULL DEFAULT 0,
tt_count10 int NOT NULL DEFAULT 0,
tt_count11 int NOT NULL DEFAULT 0,
tt_count12 int NOT NULL DEFAULT 0,
tt_Totalct int NOT NULL DEFAULT 0)
-- Create Temp Table #1 --
DECLARE @TempTable1 TABLE
(tt_distcode nvarchar(6),
tt_mgrname nvarchar(30),
tt_distquota int,
tt_agentno nvarchar(9),
tt_ctyname nvarchar(20),
tt_agtfullname nvarchar(65),
tt_count1 int NOT NULL DEFAULT 0,
tt_count2 int NOT NULL DEFAULT 0,
tt_count3 int NOT NULL DEFAULT 0,
tt_count4 int NOT NULL DEFAULT 0,
tt_count5 int NOT NULL DEFAULT 0,
tt_count6 int NOT NULL DEFAULT 0,
tt_count7 int NOT NULL DEFAULT 0,
tt_count8 int NOT NULL DEFAULT 0,
tt_count9 int NOT NULL DEFAULT 0,
tt_count10 int NOT NULL DEFAULT 0,
tt_count11 int NOT NULL DEFAULT 0,
tt_count12 int NOT NULL DEFAULT 0,
tt_Totalct int NOT NULL DEFAULT 0)
-- Create Temp Table #2 --
DECLARE @TempTable2 TABLE
(tt_distcode nvarchar(6),
tt_mgrname nvarchar(30),
tt_distquota int,
tt_agentno nvarchar(9),
tt_ctyname nvarchar(20),
tt_agtfullname nvarchar(65),
tt_count1 int NOT NULL DEFAULT 0,
tt_count2 int NOT NULL DEFAULT 0,
tt_count3 int NOT NULL DEFAULT 0,
tt_count4 int NOT NULL DEFAULT 0,
tt_count5 int NOT NULL DEFAULT 0,
tt_count6 int NOT NULL DEFAULT 0,
tt_count7 int NOT NULL DEFAULT 0,
tt_count8 int NOT NULL DEFAULT 0,
tt_count9 int NOT NULL DEFAULT 0,
tt_count10 int NOT NULL DEFAULT 0,
tt_count11 int NOT NULL DEFAULT 0,
tt_count12 int NOT NULL DEFAULT 0,
tt_Totalct int NOT NULL DEFAULT 0)
-- Set January Date(s) --
SET @LoDateStamp = '01/01/' + @MemYear
SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)
SET @HiDateStamp = '01/31/' + @MemYear
SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)
-- Insert January Data Here (Temp Table #1) --
INSERT INTO @TempTable1
(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1)
SELECT t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
FROM dbo.t_MNS_Nationwide_Signups AS MNS
INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
--WHERE (tdMNSadded between '01/01/xxxx 12:00:00 AM' and '01/31/xxxx 11:59:59 PM') AND
WHERE (tdMNSadded between @LoDateStamp and @HiDateStamp) AND
(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
ORDER BY t_distcode, t_agtfullname
-- Set February Date(s) --
SET @LoDateStamp = '02/01/' + @MemYear
SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)
-- Check For Leap Year --
DECLARE @p_leap_date SMALLDATETIME
DECLARE @p_check_day TINYINT
SET @p_leap_date = CONVERT(VARCHAR(4), @MemYear) + '0228'
SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))
IF (@p_check_day = 29)
-- LEAP Year --
BEGIN
SET @HiDateStamp = '02/29/' + @MemYear
END
ELSE
-- NON LEAP Year --
BEGIN
SET @HiDateStamp = '02/28/' + @MemYear
END
SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)
-- Insert February Data Here (Temp Table #2) --
INSERT INTO @TempTable2
(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count2)
SELECT t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
FROM dbo.t_MNS_Nationwide_Signups AS MNS
INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
--WHERE (tdMNSadded between '02/01/xxxx 12:00:00 AM' and '02/29/xxxx 11:59:59 PM') AND
WHERE (tdMNSadded between @LoDateStamp and @HiDateStamp) AND
(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
ORDER BY t_distcode, t_agtfullname
-- Merge the January & February Totals Into the Main Table --
INSERT INTO @MainTable (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1, tt_count2)
SELECT coalesce(a.tt_distcode, b.tt_distcode) AS tt_distcode,
coalesce(a.tt_mgrname, b.tt_mgrname) AS tt_mgrname,
coalesce(a.tt_distquota, b.tt_distquota) AS tt_distquota,
coalesce(a.tt_agentno, b.tt_agentno) AS tt_agentno,
coalesce(a.tt_ctyname, b.tt_ctyname) AS tt_ctyname,
coalesce(a.tt_agtfullname, b.tt_agtfullname) AS tt_agtfullname,
coalesce(a.tt_count1, b.tt_count1) AS count1,
coalesce(b.tt_count2, a.tt_count2) AS count2
FROM @TempTable1 a
FULL OUTER JOIN @TempTable2 b
ON (a.tt_distcode = b.tt_distcode) AND (a.tt_mgrname = b.tt_mgrname) AND
(a.tt_distquota = b.tt_distquota) AND (a.tt_agentno = b.tt_agentno) AND
(a.tt_ctyname = b.tt_ctyname) AND (a.tt_agtfullname = b.tt_agtfullname)
-- Delete the Data in the Temporary Table(s) --
DELETE FROM @TempTable1
DELETE FROM @TempTable2
-- Copy the Main Table to Temp Table #1 --
INSERT INTO @TempTable1
(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
tt_count10, tt_count11, tt_count12, tt_Totalct)
SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
FROM @MainTable
ORDER BY tt_distcode
-- Delete the Data in the Main Table --
DELETE FROM @MainTable
-- Set March Date(s) --
SET @LoDateStamp = '03/01/' + @MemYear
SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)
SET @HiDateStamp = '03/31/' + @MemYear
SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)
-- Insert March Data Here (Temp Table #2) --
INSERT INTO @TempTable2
(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count3)
SELECT t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
FROM dbo.t_MNS_Nationwide_Signups AS MNS
INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
--WHERE (tdMNSadded between '03/01/xxxx 12:00:00 AM' and '03/31/xxxx 11:59:59 PM') AND
WHERE (tdMNSadded between @LoDateStamp and @HiDateStamp) AND
(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
ORDER BY t_distcode, t_agtfullname
-- Merge the March Totals Into the Main Table --
INSERT INTO @MainTable (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
tt_count1, tt_count2, tt_count3)
SELECT coalesce(a.tt_distcode, b.tt_distcode) AS tt_distcode,
coalesce(a.tt_mgrname, b.tt_mgrname) AS tt_mgrname,
coalesce(a.tt_distquota, b.tt_distquota) AS tt_distquota,
coalesce(a.tt_agentno, b.tt_agentno) AS tt_agentno,
coalesce(a.tt_ctyname, b.tt_ctyname) AS tt_ctyname,
coalesce(a.tt_agtfullname, b.tt_agtfullname) AS tt_agtfullname,
coalesce(a.tt_count1, b.tt_count1) AS count1,
coalesce(b.tt_count2, a.tt_count2) AS count2,
coalesce(b.tt_count3, a.tt_count3) AS count3
FROM @TempTable1 a
FULL OUTER JOIN @TempTable2 b
ON (a.tt_distcode = b.tt_distcode) AND (a.tt_mgrname = b.tt_mgrname) AND
(a.tt_distquota = b.tt_distquota) AND (a.tt_agentno = b.tt_agentno) AND
(a.tt_ctyname = b.tt_ctyname) AND (a.tt_agtfullname = b.tt_agtfullname)
-- Delete the Data in the Temporary Table(s) --
DELETE FROM @TempTable1
DELETE FROM @TempTable2
-- Copy the Main Table to Temp Table #1 --
INSERT INTO @TempTable1
(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
tt_count10, tt_count11, tt_count12, tt_Totalct)
SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
FROM @MainTable
ORDER BY tt_distcode
-- Delete the Data in the Main Table --
DELETE FROM @MainTable