Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Is this possible to do w/o a cursor?

Author  Topic 

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2006-08-01 : 19:58:01
I really don't like to use cursors, but I need to accomplish the following. The code below works perfect, but is there any way to accomplish this w/o using a cursor? If so, I would greatly appriciate learning how.

Regards...

Declare @GenerationdateID int,@EmployerID int,@Generationdate datetime,@EStatusID int,@BillingCycleID int
Declare dbcursor cursor
READ_ONLY
FOR
SELECT a.GenerationdateID,a.EmployerID,a.Generationdate,a.BillingCycleID
FROM TAC_Generationdate a inner join TMI_Plan b on
a.PlanID = b.PlanID inner Join TMI_BenefitType c
on b.BenefitTypeID = c.BenefitTypeID
WHERE c.BillingTypeID = 2 and not exists(Select Generationdate From TAC_EmployeeSetRate a3 where a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID)
OPEN dbcursor
FETCH NEXT FROM DBcursor INTO @GenerationdateID,@EmployerID,@Generationdate,@BillingCycleID
WHILE (@@fetch_status <> -1)
BEGIN
If Not exists (Select a.Generationdate From TAC_EmployeeSetRate a where a.EmployerID = @EmployerID and a.Generationdate = @Generationdate and a.BillingCycleID = @BillingCycleID)
Begin
IF (Select top 1 EstatusID from TMI_EmployerStatus b
where b.EffectiveDate <= @Generationdate and b.EmployerID = @EmployerID
order by b.EffectiveDate desc) = 1
BEGIN
Insert Into TAC_EmployeeSetRate(Generationdate,EmployerID,EmployeeID,Fee,EStatusID,GeneratedStatusID,BillingCycleID)
Select @Generationdate,@EmployerID,a1.EmployeeID,0,isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),
isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),@BillingCycleID
From TMI_Employee a1
Where a1.EmployerID = @EmployerID
END
end
FETCH NEXT FROM DBcursor INTO @GenerationdateID,@EmployerID,@Generationdate,@BillingCycleID
END

Close dbcursor;
DeAllocate dbcursor;

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-02 : 01:45:43
Explain the logic behind the algorithm.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2006-08-02 : 02:06:34
It's very confusing, but here it goes as simple as I can get..
This is for a system that deals with employers and their employees.
There are two main tables we are working with in the above example. One of which stores the dates to generate the invoices (TAC_Generationdate)(Each employer has differant recurring billing options, so this table stores that information along with it serving as a verifcation that the employer has been billed properly. For this scenerio above it is used to retrieve the date to generate a invoice) these invoices represent the employees that work for the employer, Each employer is billed upon how many employees they have, each employee that belongs to the employer gets added to TAC_EmployeeSetRate. Basically each employee ultimatly serves in generating a line item for the invoice.

I hope this helps a little.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-02 : 03:35:23
Ok, I am with you so far.

Can you give us the DDL of the tables together with some sample data. Can you also give us an example of the result wanted, based on the provided sample data? I am almost sure this can be solved with a set-based solution.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-08-02 : 11:04:19
And refer this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2006-08-02 : 16:03:26
/*Here is the DDL that was requested. I hope this helps */

CREATE TABLE [dbo].[TAC_GenerationDate](
[GenerationDateID] [int] NOT NULL,
[EmployerID] [int] NULL,
[PlanID] [int] NULL,
[PlanBenWWaitID] [int] NULL,
[GenerationDate] [datetime] NULL,
[BillingCycleID] [int] NULL,
[Generated] [int] NULL CONSTRAINT [DF_TAC_BenefitGenerationDate_Generated] DEFAULT ((2)),
[CreateDate] [datetime] NULL CONSTRAINT [DF_TAC_BenefitGenerationDate_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[TMI_Plan](
[PlanID] [int] NOT NULL,
[BenefitTypeID] [int] NOT NULL,
[PlanTypeID] [int] NOT NULL,
[CoverageTypeID] [int] NOT NULL,
[StatusID] [int] NOT NULL CONSTRAINT [DF_TMI_Plan_StatusID_1] DEFAULT ((1)),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_TMI_Plan_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[TMI_BenefitType](
[BenefitTypeID] [int] NOT NULL,
[BenefitTypeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AccountID] [int] NOT NULL,
[BillingTypeID] [int] NOT NULL,
[BillMonth] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_MonthsBehind] DEFAULT ((0)),
[BillbyDateRangeEmployer] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_BillbyDateRange] DEFAULT ((2)),
[BillByDateRangeEmployee] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_BillByDateRangeEmployee] DEFAULT ((2)),
[BillEmployeeDirect] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_BillEmployeeDirectly] DEFAULT ((2)),
[SetRateAtEmployee] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_SetRateAtEmployee] DEFAULT ((2)),
[MultipleCatagory] [int] NULL CONSTRAINT [DF_TMI_BenefitType_MultipleCatagory] DEFAULT ((2)),
[BypassStatus] [int] NOT NULL CONSTRAINT [DF_TMI_BenefitType_BypassStatus] DEFAULT ((2)),
[CreateDate] [datetime] NULL CONSTRAINT [DF_TMI_BenefitType_CreateDate] DEFAULT (getdate()),
CONSTRAINT [PK_TMI_BenefitType] PRIMARY KEY CLUSTERED
(
[BenefitTypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TMI_EmployerStatus](
[EmployerStatusID] [int] NULL,
[EmployerID] [int] NULL,
[EStatusID] [int] NULL,
[StatusID] [int] NULL,
[EffectiveDate] [datetime] NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_TMI_EmployerStatus_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[TAC_EmployeeSetRate](
[EmployeeSetRateID] [int] identity(1,1) NOT NULL,
[GenerationDate] [datetime] NULL,
[EmployerID] [int] NULL,
[EmployeeID] [int] NULL,
[Fee] [int] NULL,
[EStatusID] [int] NULL,
[GeneratedStatusID] [int] NULL,
[BillingCycleID] [int] NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_TAC_EmployeeSetFee_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[TMI_EmployeeStatus](
[EmployeeStatusID] [int] NULL,
[EmployeeID] [int] NULL,
[EStatusID] [int] NULL,
[StatusID] [int] NULL,
[EffectiveDate] [datetime] NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_TMI_EmployeeStatus_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[TMI_Employee](
[EmployeeID] [int] NOT NULL,
[EmployerID] [int] NULL,
[EmployeeSSN] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeFName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeLName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeMI] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeBillID] [int] NULL,
[EmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateofBirth] [datetime] NULL,
[IDFromEmployer] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ext1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ext2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ext3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_TMI_Employee_CreateDate] DEFAULT (getdate()),
[Locked] [int] NULL CONSTRAINT [DF_TMI_Employee_Locked] DEFAULT ((1)),
[LockedByUserID] [int] NULL,
CONSTRAINT [PK_TMI_EmployeeMaster] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



Insert Into TAC_GenerationDate
Select 1,53,106,95,getdate(),1,2,GetDate() Union All
Select 2,53,108,100,getdate(),1,2,GetDate() Union All
Select 2,53,109,100,getdate(),1,3,GetDate()

Insert Into TMI_Plan
Select 106,55,0,0,1,getdate() Union All
Select 108,57,0,0,1,getdate() Union ALL
Select 109,58,0,0,1,getdate()

Insert Into TMI_BenefitType
select 55,'SetRateEmployeeLevelRate',1,2,0,2,2,2,1,2,2,getdate() Union All
select 57,'SetRate2',1,2,0,2,2,2,2,2,2,getdate() union All
select 58,'Hourly Rate',1,3,0,2,2,2,2,2,2,getdate()

insert into TMI_EmployerStatus
Select 26,53,2,1,'07/30/2005',getdate() Union All
Select 27,53,1,1,'07/01/2006',getdate()

Insert Into TMI_EmployeeStatus
Select 92,196,2,1,'07/30/2005',getdate() Union All
Select 93,196,1,1,'07/30/2006',getdate() Union All
Select 94,200,2,1,'07/30/2005',getdate() Union All
Select 95,200,1,1,'07/30/2006',getdate() Union All
Select 96,201,1,1,'07/30/2005',getdate() Union All
Select 97,201,2,1,'07/30/2006',getdate()

Insert Into TMI_Employee
select 196,53,'***-***-****','John','Doe','',53,'123@1234.com','03/03/1980','','123','555-555-5555','','','','',getdate(),2,null union All
select 200,53,'***-***-****','John1','Doe1','',53,'123@1234.com','03/03/1980','','123','555-555-5555','','','','',getdate(),2,null union All
select 201,54,'***-***-****','John2','Doe2','',53,'123@1234.com','03/03/1980','','123','555-555-5555','','','','',getdate(),2,null


/* WANTED RESULTS in dbo.TAC_EmployeeSetRate
EmployeeSetFeeID,GenerationDate,EmployerID,EmployeeID,Fee,EStatusID,GeneratedStatusID,BillingCycleID,CreateDate
1,'08/02/2006',53,196,0,1,1,1,'08/02/2006
2,'08/02/2006',53,200,0,1,1,1,'08/02/2006
Thanks in advance*/
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2006-08-03 : 14:57:02
Since there have been no recent responses, I'm assuming that I do need to use a cursor on this, is that correct?
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2006-08-04 : 01:52:34
Try this out:

Declare @GenerationdateID int,
	@EmployerID int,
	@Generationdate datetime,
	@BillingCycleID int

SET @GenerationdateID = 0
WHILE EXISTS (SELECT NULL	FROM TAC_Generationdate a 	inner join TMI_Plan b on a.PlanID = b.PlanID 
				inner Join TMI_BenefitType c on b.BenefitTypeID = c.BenefitTypeID
				left join TAC_EmployeeSetRate a3 on a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID	
		WHERE c.BillingTypeID = 2 AND a3.Generationdate IS NULL
		AND a.GenerationdateID > @GenerationdateID) BEGIN


	SELECT TOP 1 @GenerationdateID = a.GenerationdateID, @EmployerID = a.EmployerID, @Generationdate = a.Generationdate, @BillingCycleID = a.BillingCycleID
	FROM TAC_Generationdate a 	inner join TMI_Plan b on a.PlanID = b.PlanID 
					inner Join TMI_BenefitType c on b.BenefitTypeID = c.BenefitTypeID
					left join TAC_EmployeeSetRate a3 on a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID	
	WHERE c.BillingTypeID = 2 AND a3.Generationdate IS NULL
	AND a.GenerationdateID > @GenerationdateID
	ORDER BY a.GenerationdateID ASC


	IF (	Select 	top 1 EstatusID from TMI_EmployerStatus b
		where 	b.EffectiveDate <= @Generationdate and b.EmployerID = @EmployerID
		order by b.EffectiveDate desc) = 1 BEGIN

		Insert Into TAC_EmployeeSetRate(Generationdate,EmployerID,EmployeeID,Fee,EStatusID,GeneratedStatusID,BillingCycleID)
			Select @Generationdate,@EmployerID,a1.EmployeeID,0,
			isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),
			isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),
			@BillingCycleID
		From TMI_Employee a1
		Where a1.EmployerID = @EmployerID
	END
end


NB: I've removed your 'IF NOT EXISTS' statement because it's the same as the one in the outer-most statement.
It could probably do with a bit of optimising, especially in the bit that gets the EStatusID.

HTH,

Tim
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site