Re write this if you can and have spare time

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

Transact-SQL (2000)

Re write this if you can and have spare time

Author

Topic

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-15 : 03:16:46

/*
Hey

I just wrote some nasty code, decided on using 3 cursors to get the job done. It runns fast enought. I however would be very interrested in if someone can understand it and make it better for me as a lering point of view. I would appreciate that very much but I do of course understand if none or few has that time to spare. Or maybe it's relly simple to make it better since you might be way more experienced then I am.

*/

DECLARE @InsuranceID INT
SELECT @InsuranceID=19418 -- To get it going and test my code.

DECLARE @BenefitGroupDates TABLE(
BenefitGroupID INT,
FromDate DATETIME,
ToDate DATETIME
)
INSERT INTO @BenefitGroupDates
SELECT TOP 1 BenefitGroupID, [RegDate] AS FromDate, [ChangeDate] AS ToDate
FROM [TEmployee_Archive]
WHERE [DeletedDate] IS NULL AND [insuranceid]=@InsuranceID
ORDER BY [ChangeDate]
INSERT INTO @BenefitGroupDates
SELECT BenefitGroupID,MIN([ChangeDate]) AS FromDate,MAX([ChangeDate]) AS ToDate
FROM [TEmployee_Archive] WHERE [DeletedDate] IS NULL AND [insuranceid]=@InsuranceID
GROUP BY [BenefitGroupID]
INSERT INTO @BenefitGroupDates
SELECT [BenefitGroupID],[ChangeDate]AS FromDate,GETDATE() AS ToDate
FROM temployee WHERE [DeletedDate] IS NULL AND [insuranceid]=@InsuranceID

DECLARE @BenefitGroupID INT
DECLARE @FromDate1 DATETIME
DECLARE @ToDate1 DATETIME

DECLARE @BenefitGroupTableDates TABLE(
[HealthInsurance] BIT,
[PremiumInOfBounds] BIT,
FromDate DATETIME,
ToDate DATETIME
)

DECLARE crs CURSOR FOR
SELECT [BenefitGroupID], MIN([FromDate]) AS FromDate, MAX([ToDate]) AS ToDate
FROM @BenefitGroupDates
GROUP BY BenefitGroupID
OPEN crs
FETCH NEXT FROM crs
INTO @BenefitGroupID,@FromDate1,@ToDate1
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @BenefitGroupTableDates
SELECT TOP 1 [HealthInsurance], [PremiumInOfBounds], [Regdate] AS [FromDate], [ChangeDate] AS [ToDate]
FROM [TBenefitGroup_Archive]
WHERE [BenefitGroupID]=@BenefitGroupID
ORDER BY [ChangeDate]
INSERT INTO @BenefitGroupTableDates
SELECT [HealthInsurance], [PremiumInOfBounds], MIN([ChangeDate]) AS FromDate, MAX([ChangeDate]) AS ToDate
FROM [TBenefitGroup_Archive]
WHERE [BenefitGroupID]=@BenefitGroupID
GROUP BY [Regdate],[HealthInsurance],[PremiumInOfBounds]
INSERT INTO @BenefitGroupTableDates
SELECT [HealthInsurance], [PremiumInOfBounds], [ChangeDate] AS FromDate, GETDATE() AS [ToDate]
FROM [TBenefitGroup]
WHERE [BenefitGroupID]=@BenefitGroupID

FETCH NEXT FROM crs
INTO @BenefitGroupID,@FromDate1,@ToDate1
END
CLOSE crs
DEALLOCATE crs

DECLARE @Health2 BIT
DECLARE @PremiumInOf2 BIT
DECLARE @FromDate2 DATETIME
DECLARE @ToDate2 DATETIME
DECLARE @ToDate3 DATETIME

DECLARE @BenefitGroupTableDates2 TABLE(
[HealthInsurance] BIT,
[PremiumInOfBounds] BIT,
FromDate DATETIME,
ToDate DATETIME
)

DECLARE crs1 CURSOR FOR
SELECT [HealthInsurance],[PremiumInOfBounds], [FromDate], [ToDate]
FROM @BenefitGroupTableDates
GROUP BY [HealthInsurance],[PremiumInOfBounds], [FromDate], [ToDate]
OPEN crs1
FETCH NEXT FROM crs1
INTO @Health2,@PremiumInOf2,@FromDate2,@ToDate2
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @ToDate3 = (SELECT MIN(FromDate) FROM @BenefitGroupTableDates WHERE [FromDate]>@FromDate2)
INSERT INTO @BenefitGroupTableDates2
SELECT @Health2 as [HealthInsurance],@PremiumInOf2 AS [PremiumInOfBounds],@FromDate2 AS [FromDate],
CASE WHEN
@ToDate3>@ToDate2 THEN @ToDate3 ELSE @ToDate2 END AS ToDate
FROM @BenefitGroupTableDates AS ben

FETCH NEXT FROM crs1
INTO @Health2,@PremiumInOf2,@FromDate2,@ToDate2
END
CLOSE crs1
DEALLOCATE crs1

DECLARE @PremiumHistory TABLE(
TotalPremiumHistory BIT,
PremiumHistoryID INT,
InsuranceID INT,
PremiumAmount DECIMAL,
StartDate DATETIME
)

DECLARE @Health bit, @PremiumInOf bit, @From DATETIME, @To DATETIME

DECLARE crs2 CURSOR FOR
SELECT [HealthInsurance], [PremiumInOfBounds], FromDate, ToDate
FROM @BenefitGroupTableDates2

OPEN crs2

FETCH NEXT FROM crs2
INTO @Health, @PremiumInOf, @From, @To
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Health=1 AND @PremiumInOf=1)
BEGIN
INSERT INTO @PremiumHistory
SELECT 1, [TotalPremiumHistoryID] AS PremiumHistoryID, InsuranceID, [TotalPremiumAmount] AS PremiumAmount, StartDate
FROM [TTotalPremiumHistory]
WHERE [StartDate]>=@From AND [StartDate]<=@To
AND [TTotalPremiumHistory].[InsuranceID]=@InsuranceID
END
ELSE
INSERT INTO @PremiumHistory
SELECT 0, PremiumHistoryID, InsuranceID, PremiumAmount, StartDate
FROM [TPremiumHistory]
WHERE [StartDate]>=@From AND [StartDate]<=@To
AND [TPremiumHistory].[InsuranceID]=@InsuranceID
FETCH NEXT FROM crs2
INTO @Health, @PremiumInOf, @From, @To
END
CLOSE crs2
DEALLOCATE crs2

DECLARE @PremiumHistory1 TABLE(
TotalPremiumHistory BIT,
PremiumHistoryID INT,
InsuranceID INT,
PremiumAmount DECIMAL,
StartDate DATETIME
)
INSERT INTO @PremiumHistory1
SELECT TotalPremiumHistory, MAX([PremiumHistoryID]),[insuranceid],PremiumAmount,StartDate FROM @PremiumHistory
GROUP BY TotalPremiumHistory,InsuranceID,PremiumAmount, [StartDate]
ORDER BY [StartDate]

DECLARE @TotalPremiumHistory3 bit
DECLARE @PremiumHistoryID3 BIT
DECLARE @InsuranceID3 INT
DECLARE @PremiumAmount3 DECIMAL
DECLARE @StartDate3 DATETIME

DECLARE @TotalPremiumHistory4 bit
DECLARE @PremiumHistoryID4 BIT
DECLARE @InsuranceID4 INT
DECLARE @PremiumAmount4 DECIMAL
DECLARE @StartDate4 DATETIME

DECLARE @PremiumHistory2 TABLE(
PremiumHistoryID INT,
InsuranceID INT,
PremiumAmount DECIMAL,
StartDate DATETIME
)

DECLARE @counter INT
SELECT @counter =0

DECLARE crs3 CURSOR FOR
SELECT TotalPremiumHistory, PremiumHistoryID, InsuranceID, PremiumAmount,StartDate
FROM @PremiumHistory1
ORDER BY [StartDate]
OPEN crs3
FETCH NEXT FROM crs3
INTO @TotalPremiumHistory3, @PremiumHistoryID3, @InsuranceID3, @PremiumAmount3, @StartDate3
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @counter = @counter+1
SELECT TOP 1 @TotalPremiumHistory4=TotalPremiumHistory,
@PremiumHistoryID4=ISNULL(PremiumHistoryID,0),
@InsuranceID4 = InsuranceID,
@PremiumAmount4 = PremiumAmount,
@StartDate4 = MIN(StartDate)
FROM @PremiumHistory1 WHERE StartDate>@StartDate3
GROUP BY TotalPremiumHistory,PremiumHistoryID,InsuranceID,PremiumAmount,StartDate
ORDER BY StartDate
INSERT INTO @PremiumHistory2
SELECT @counter, @InsuranceID3, @PremiumAmount3, @StartDate3

IF (@PremiumHistoryID4<>0 AND @TotalPremiumHistory4<>@TotalPremiumHistory3)
BEGIN
IF(@TotalPremiumHistory4=1) --1= ta TTotalPremiumHistory tabellen, annars TPremiumHistory
BEGIN
SELECT @counter=@counter+1
INSERT INTO @PremiumHistory2
SELECT TOP 1 @counter AS PremiumHistoryID,
InsuranceID,
TotalPremiumAmount AS PremiumAmount,
MAX(StartDate)
FROM TTotalPremiumHistory WHERE StartDate<@StartDate4 AND InsuranceID=@InsuranceID
GROUP BY TotalPremiumHistoryID,InsuranceID,TotalPremiumAmount,StartDate
ORDER BY StartDate DESC
END
IF(@TotalPremiumHistory4=0) --1= ta TTotalPremiumHistory tabellen, annars TPremiumHistory
BEGIN
SELECT @counter=@counter+1
INSERT INTO @PremiumHistory2
SELECT TOP 1 @counter AS PremiumHistoryID,
InsuranceID,
PremiumAmount,
MAX(StartDate)
FROM TPremiumHistory WHERE StartDate<@StartDate4 AND InsuranceID=@InsuranceID
GROUP BY PremiumHistoryID,InsuranceID,PremiumAmount,StartDate
ORDER BY StartDate DESC
END

END

FETCH NEXT FROM crs3
INTO @TotalPremiumHistory3, @PremiumHistoryID3, @InsuranceID3, @PremiumAmount3, @StartDate3
END
CLOSE crs3
DEALLOCATE crs3

SELECT * FROM @PremiumHistory2

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-15 : 04:20:09

Can you please provide DDL's of your tables? then sample datas? then desired result.

You could get fast answers if you follow this guidelines in posting such questions:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-15 : 06:41:31

I'd have to describe a whole lot of stuff then and I dont think you need it. If you can, use the conditions from the above code and merge it into something that does not use cursors.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-15 : 07:29:07

quote:
Originally posted by IceDread

I'd have to describe a whole lot of stuff then and I dont think you need it. If you can, use the conditions from the above code and merge it into something that does not use cursors.

Good point, that would require much more effort on your part rather than just cutting and pasting, and it would force you to re-examine both the existing code and the necessary algorithm to help us help you simplify it and rewrite it accurately. A complete waste of time!

- Jeff
http://weblogs.sqlteam.com/JeffS

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-15 : 07:34:27

Since you insist I'll do it, didnt relly think it was needed but okay.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-15 : 08:33:23

Someone offered to provide free assistance, asked for additional information, and you get annoyed.

Wow.

e4 d5 xd5 Nf6

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-15 : 09:19:40

Sorry if it looks like that, but that is not at all how I meant and I think I've made it quite clear every time I ask for something that I very much appreciate help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-15 : 14:04:29

quote:
Originally posted by IceDread

Sorry if it looks like that, but that is not at all how I meant and I think I've made it quite clear every time I ask for something that I very much appreciate help.

Of course. You just don't appreciate it enough to make any effort yourself.

CODO ERGO SUM

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-16 : 02:43:28

I will post it, now doubting if it will matter, but I can't for a while since I dont have the time for it and need to manage some routines and fixes.

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-16 : 03:33:25

The tables contains several values of which I'll write down the one's that are interresting to make it all more focused and clear, I can not provide a dll. Some data is sensitive.

TEmployee
EmployeeID Int,
ChangeDate DateTime,
RegDate DateTime,
DeletedDate DateTime,
InsuranceID int,
BenefitGroupID

In this sp I use the above table to find which benefitgroups it is in and when.
TEmployee_Archive is similar, here a new row gets stored when a row in TEmployee is updated.

TBenefitGroup
BenefitGroupID INT,
ChangeDate DateTIme,
HealthInsurance BIT,
PremiumInOfBounds BIT,
RegDate DateTime,

TBenefitGroup_Archive is similar, here a new row gets stored when a row in TBenefitGroup is updated.

I here check which periods the benefitgroup has HealthInsurance and PremiumInOfBounds = 1.
That determens which table to read premium value from. That value is in this case stored in these tables

This table is of interrest when HealthPremiumInsurance and PremiumInOfBounsd = 1, the highest ToTalPremiumHistoryID is usually of interrest (depening on if the startdate has yet occured snce you can make a change that will take place in the future as well as make a change back in time recalcing some stuff then like the premiuminvoices that are in this sp not of interrest)
TTotalPremiumHistory
TotalPremiumHistoryID INT,
InsuranceID INT,
TotalPremiumAmount Decimal,
StartDate DateTime,
ChangeDate DateTIme,

OtherWise this table is of interrest.
TPremiumHistory
PremiumHistoryID INT,
InsuranceID INT,
PremiumAmount Decimal,
StartDate DateTime,
ChangeDate DateTIme,

---------------------

In the sp I took a simple approach to, simply do first what in my mind I found easy.

First check which benefitgroups the employee has and when.
Then check the benefitgroups values (HealthInsurance and PremiumInOfBounds) and when they are active.
From that I check the values in TTotalPremiumHistory and/or TPremiumHistory that are of interrest with the correct times. Since an insurance with an employee in a benefitgroup in different times gets the Premium from eather TTotalPremiumHistory and/or TPremiumHistory and can have a mix of these if say the Benefitgroup first has health and inofbounds but later does not.
After that I then check if a change from TTotalPremiumHistory to TPremiumHistory occurs (and also the other way around). If so I need to find the PremiumAmount that is not included in my search this far. Since if a change occurs in 2007-02-01 and the Premium table that is now of interrest has a startdate in 2007-03-01 there is no startdate that I have selected to cover for the time betweeen 2007-03-01 and 2007-02-01 and that is what I cover in cursor crs3.

Maybe this is enought info maybe it's not, I hope it helps, otherwise I'll have to rethink. I also see now that I need to findtune some of the first selects to cover all times that gets selected, think I have missed some when I select tbenefitgroup times.

What I am mostly interrested in is if I've made some screwup (all thou one needs to know this db and how it's used I guess, and pressed on time as I is I am not 100% done to cover all times) but more interrested in how this sp would look if not written with cursors and some input on what would relly be the fastest/best way to go about with an sp like this. (Hence I thought that some peeps might be able to take my code and use the conditions and selects etc and revrite it)

Sorry again for my approach to you guys offers of help, if there is someone however that can give me pointers on how to relly make this code better and how to remove the cursors with alternatives that would be very very much appreciated. That would make me see a case on how I can improve and rethink things.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-11-16 : 04:06:30

You didn't read this did you ?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources