August 10, 2010 at 12:05 am
CREATE PROC dbo.sp_Build_Member_Eligibility_Step1B AS
TRUNCATE TABLE dbo.Audit_Err_30_Detail
DECLARE
@CNT INT,
@CNTR INT,
@CNTPRD INT,
@ENDPRD datetime,
@BEGPRD datetime,
@CNT2 INT
SET @CNT = 0
SET @CNTR = 0
SET @CNTPRD = DATEDIFF(D,'2005-12-31',GETDATE())
WHILE @CNTR < @CNTPRD
BEGIN
SET @CNT2 = 0
SET @CNTR = @CNTR + 1
SET @BEGPRD = DATEADD(DAY,@CNTR ,'12/31/2008 00:00:00.000')
SET @ENDPRD = DATEADD(DAY,@CNTR ,'12/31/2010 00:00:00.000')
INSERT INTO Audit_Err_30_Detail
(Subscriber_Id)
SELECT Subscriber_Id
FROM Stage_Member_Eligibility
WHERE @BEGPRD BETWEEN Start_Date AND End_Date AND
NOT Subscriber_Id IN(SELECT Subscriber_Id FROM Audit_Err_30_Detail)
GROUP BY Subscriber_Id
HAVING COUNT(Subscriber_Id) >1
END
Table's Structure
============
CREATE TABLE [dbo].[Stage_Member_Eligibility](
[Source_Id] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Subscriber_Id] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Meme_Ck] [bigint] NULL,
[Sbsb_Ck] [bigint] NULL,
[Cms_Contract_Id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Group_Id] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubGroup_Key] [int] NULL,
[Pbp_Key] [int] NULL,
[Pbp_Id] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Plan_Id] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Product_Key] [tinyint] NULL,
[Product_Id] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Class_Id] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Eligible_Ind] [tinyint] NOT NULL,
[Explain_Cd] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Explain_Reason] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Explain_Type] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Reason_Cd] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Start_Date] [datetime] NOT NULL,
[End_Date] [datetime] NOT NULL,
[Created_Date] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created_Datetime] [datetime] NOT NULL,
[Last_Ind] [tinyint] NOT NULL,
[DW_Altered_Ind] [tinyint] NULL,
[DW_Altered_Type_Cd] [tinyint] NULL,
[DW_Created_Ind] [tinyint] NULL,
[DW_Created_Type_Cd] [tinyint] NULL
) ON [Data]
GO
CREATE TABLE [dbo].[Audit_Err_30_Detail](
[Subscriber_Id] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Audit_Err_30_Detail] PRIMARY KEY CLUSTERED
(
[Subscriber_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
==Data For testing Try to insert into Stage_member_eligibility Table
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','063570056', 1217950,1217950,'R3444','MOR3444', 31,4,'004','GOPR0000',31,'MOGOPR01','XL01',1,'','','','',cast('2007-01-01 00:00:00.000' as datetime),cast('2008-06-29 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:38.190' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071690112',391550,391550,'R9896','SCR9896', 45,7,'007','PLPR0000',42,'SCPLPR01','XL01',1,'','','','',cast('2007-07-01 00:00:00.000' as datetime),cast('2008-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:31:38.290' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','070990159',118200,118200,'R3444','MOR3444', 31,4,'004','GOPR0000',31,'MOGOPR01','XL01',1,'','','','',cast('2007-04-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:18.643' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071440305',531400,531400,'R3444','MOR3444', 31,7,'007','PLPR0100',39,'MOPLPR01','XL01',1,'','','','',cast('2007-06-01 00:00:00.000' as datetime),cast('2008-01-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:27.063' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071060083',409200,409200,'R3444','MOR3444', 31,6,'006','PLPL0000',59,'MOPLPL01','XL01',1,'','','','',cast('2007-05-01 00:00:00.000' as datetime),cast('2007-05-01 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:24.910' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071370230',154100,154100,'R9896','SCR9896', 45,7,'007','PLPR0100',42,'SCPLPR01','XL01',1,'','','','',cast('2007-09-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:31:23.570' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071730203',890450,890450,'R3444','MOR3444', 31,6,'006','PLPL0000',59,'MOPLPL01','XL01',1,'','','','',cast('2007-07-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:32.440' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071730203',890450,890450,'R3444','MOR3444', 31,7,'007','PLPR0000',39,'MOPLPR01','XL01',1,'','','','',cast('2008-01-01 00:00:00.000' as datetime),cast('2008-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:32.440' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','070170179',1991000,1991000,'R3444','MOR3444', 31,7,'007','PLPR0100',39,'MOPLPR01','XL01',1,'','','','',cast('2007-02-01 00:00:00.000' as datetime),cast('2008-12-31 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:53.267' as datetime),0,NULL,NULL,NULL,NULL)
insert into dbo.Stage_Member_Eligibility_1234 values('TZG','071390035',190500,190500,'R3444','MOR3444', 31,7,'007','PLPR0000',39,'MOPLPR01','XL01',1,'','','','',cast('2007-06-01 00:00:00.000' as datetime),cast('2007-09-30 00:00:00.000' as datetime),'06/26/2009',cast('2009-06-26 17:19:19.517' as datetime),0,NULL,NULL,NULL,NULL)
Plzz help me out...
Thanks in Advance
Anil 🙂
August 10, 2010 at 4:59 am
Thanks for the table structure and inserts... good to see that you've learned!
How does this work for you?
DECLARE @CNTPRD INT;
SET @CNTPRD = DATEDIFF(D,'2005-12-31',GETDATE());
-- these two lines of code are to handle running outside of a procedure
if object_id('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
if object_id('tempdb..#Tally') IS NOT NULL DROP TABLE #Tally;
-- build a tally table of sequential numbers for the number of dates that you need.
-- See Jeff Moden's article on Tally tables and how they replace loops
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/
SELECT TOP (@CNTPRD)
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2;
-- build and populate a table of dates
-- NOTE: I don't see where you are using the EndDate (@EndPrd),
-- but I'm building that date in this table for you anyway.
-- If you don't need it, just remove it.
CREATE TABLE #Dates (BeginDate datetime PRIMARY KEY CLUSTERED, EndDate datetime);
INSERT INTO #Dates (BeginDate, EndDate)
SELECT dateadd(d, N, '20081231'), dateadd(d, N, '20101231')
FROM #Tally;
-- Change your insert into a set-based insert based on the tables.
INSERT INTO Audit_Err_30_Detail (Subscriber_Id)
SELECT sme.Subscriber_Id
FROM Stage_Member_Eligibility sme
JOIN #Dates d
ON d.BeginDate BETWEEN sme.Start_Date and sme.End_Date
LEFT JOIN Audit_Err_30_Detail ae
ON sme.Subscriber_Id = ae.Subscriber_Id
WHERE ae.Subscriber_Id IS NULL
GROUP BY sme.Subscriber_Id
HAVING COUNT(sme.Subscriber_Id) >1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 10, 2010 at 5:01 am
BTW, you've been posting in the SQL 7/2000 forums. Is it safe to assume that this code is for SQL 2000? If not, what version are you using?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 10, 2010 at 5:20 am
Hey Wayne..
Thanks a lotttttttt for your help....
i am using the Sqlserver 2005 Standard Edition..
Thnx
Anil:-D
August 10, 2010 at 5:31 am
Anil,
This method, just for SQL 2005+, utilizes a CTE to build a dynamic tally table. Not much else is different.
-- declare and initialize variables
DECLARE @CNTPRD INT;
SET @CNTPRD = DATEDIFF(D,'2005-12-31',GETDATE());
-- this line of code is to handle running outside of a procedure
if object_id('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
-- build and populate a table of dates
-- NOTE: I don't see where you are using the EndDate (@EndPrd),
-- but I'm building that date in this table for you anyway.
-- If you don't need it, just remove it.
CREATE TABLE #Dates (BeginDate datetime PRIMARY KEY CLUSTERED, EndDate datetime);
-- populate temp table with the dates you're interested in
WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO #Dates (BeginDate, EndDate)
SELECT TOP (@CNTPRD) dateadd(d, N, '20081231'), dateadd(d, N, '20101231')
FROM TALLY
ORDER BY N
-- Change your insert into a set-based insert based on the tables.
INSERT INTO Audit_Err_30_Detail (Subscriber_Id)
SELECT sme.Subscriber_Id
FROM Stage_Member_Eligibility sme
JOIN #Dates d
ON d.BeginDate BETWEEN sme.Start_Date and sme.End_Date
LEFT JOIN Audit_Err_30_Detail ae
ON sme.Subscriber_Id = ae.Subscriber_Id
WHERE ae.Subscriber_Id IS NULL
GROUP BY sme.Subscriber_Id
HAVING COUNT(sme.Subscriber_Id) >1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 10, 2010 at 11:22 pm
Hi Wayne,
Do i need to change some thing in this code..... this code is giving subscriber_id more than 112600, where as original sproc is not giving single subscriber_id.....
-- declare and initialize variables
DECLARE @CNTPRD INT;
SET @CNTPRD = DATEDIFF(D,'2005-12-31',GETDATE());
-- this line of code is to handle running outside of a procedure
if object_id('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
-- build and populate a table of dates
-- NOTE: I don't see where you are using the EndDate (@EndPrd),
-- but I'm building that date in this table for you anyway.
-- If you don't need it, just remove it.
CREATE TABLE #Dates (BeginDate datetime PRIMARY KEY CLUSTERED, EndDate datetime);
-- populate temp table with the dates you're interested in
WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO #Dates (BeginDate, EndDate)
SELECT TOP (@CNTPRD) dateadd(d, N, '20081231'), dateadd(d, N, '20101231')
FROM TALLY
ORDER BY N
-- Change your insert into a set-based insert based on the tables.
INSERT INTO Audit_Err_30_Detail (Subscriber_Id)
SELECT sme.Subscriber_Id
FROM Stage_Member_Eligibility sme
JOIN #Dates d
ON d.BeginDate BETWEEN sme.Start_Date and sme.End_Date
LEFT JOIN Audit_Err_30_Detail ae
ON sme.Subscriber_Id = ae.Subscriber_Id
WHERE ae.Subscriber_Id IS NULL
GROUP BY sme.Subscriber_Id
HAVING COUNT(sme.Subscriber_Id) >1;
please let me know,where i have modify the code......waiting for your reply..
Thanks in advance
Anil Inampudi
August 11, 2010 at 6:19 am
Hi Anil,
This is actually a good example of why you need to provide accurate test data to demonstrate the issue that you're having. The test data did not work for the date range provided - it wasn't until I changed the dates that I could see the problem that you're having. And why you should also provide what the results should be, based upon the sample data provided.
So, change your insert statement to:
-- Change your insert into a set-based insert based on the tables.
INSERT INTO Audit_Err_30_Detail (Subscriber_Id)
SELECT sme.Subscriber_Id
FROM Stage_Member_Eligibility sme
LEFT JOIN #Audit_Err_30_Detail ae
ON sme.Subscriber_Id = ae.Subscriber_Id
WHERE ae.Subscriber_Id IS NULL
AND EXISTS (SELECT 1 FROM #Dates WHERE BeginDate BETWEEN sme.Start_Date AND sme.End_Date)
GROUP BY sme.Subscriber_Id
HAVING COUNT(sme.Subscriber_Id) >1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply