SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Re write this if you can and have spare time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/15/2007 :  03:16:46  Show Profile  Reply with Quote
/*
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

Philippines
300 Posts

Posted - 11/15/2007 :  04:20:09  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
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...

Edited by - jonasalbert20 on 11/15/2007 04:23:48
Go to Top of Page

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/15/2007 :  06:41:31  Show Profile  Reply with Quote
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.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/15/2007 :  07:29:07  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/15/2007 :  07:34:27  Show Profile  Reply with Quote
Since you insist I'll do it, didnt relly think it was needed but okay.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/15/2007 :  08:33:23  Show Profile  Reply with Quote
Someone offered to provide free assistance, asked for additional information, and you get annoyed.

Wow.



e4 d5 xd5 Nf6
Go to Top of Page

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/15/2007 :  09:19:40  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/15/2007 :  14:04:29  Show Profile  Reply with Quote
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
Go to Top of Page

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/16/2007 :  02:43:28  Show Profile  Reply with Quote
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.
Go to Top of Page

IceDread
Yak Posting Veteran

66 Posts

Posted - 11/16/2007 :  03:33:25  Show Profile  Reply with Quote
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.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/16/2007 :  04:06:30  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000