Trying to avoid cursors - SQL Server Forums

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)

Trying to avoid cursors

Author

Topic

Rimsky
Starting Member

12 Posts

Posted - 2005-11-05 : 11:31:59

I'm trying to avoid cursor use.
Here's the setup:
Record containing date field and interval field (number of days). I'm trying to find a way to show the number of results where the date + X times the interval is in a give year.
It's not hard with a cursor, but slow.
Anyone any ideas how to go about it?

Tnx in advance,

Cees Cappelle

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-11-05 : 12:28:47

Please post some code for us (table script, sample data, desired resultset).

Nathan Skerl

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-05 : 19:18:03

I think this might be what you're looking for:

SELECT * FROM myTable WHERE DateDiff(year, dateCol, DateAdd(day, intervalCol, dateCol))=0

Rimsky
Starting Member

12 Posts

Posted - 2005-11-06 : 01:28:48

Here's some code. I'll try to edit it to the relevant points. Bear with me.
It's meant to ne run as a StoredProcedure of which Crystal Reports can report. CR isn't able to report of WhilePrinting formula's :(
For each cursor row, it first calculates the first date in a give year for this Preventive Maintenance record. It then calculates all occurrences within that year and adds that data into the mcmain.werkbel table.

Here's the scheme for the mcmain.pmmas table:
CREATE TABLE [mcmain].[pmmas] (
[pm_nmr] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[pm_stat] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[pm_desc] [char] (45) COLLATE Latin1_General_CI_AS NULL ,
[pm_site] [char] (20) COLLATE Latin1_General_CI_AS NULL ,
[pm_date] [datetime] NULL ,
[pm_type] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_crew] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[pm_shft] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
[pm_eqnmr] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[pm_cc] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[pm_prio] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
[pm_job] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[pm_insp] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_days] [numeric](4, 0) NULL ,
[pm_hrs] [numeric](6, 0) NULL ,
[pm_cost] [numeric](12, 2) NULL ,
[pm_comm] [text] COLLATE Latin1_General_CI_AS NULL ,
[pm_phrs] [numeric](6, 2) NULL ,
[pm_cphrs] [numeric](6, 2) NULL ,
[pm_pcost] [numeric](12, 2) NULL ,
[pm_preq] [char] (12) COLLATE Latin1_General_CI_AS NULL ,
[pm_prdays] [numeric](2, 0) NULL ,
[pm_serv] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[pm_temp] [char] (80) COLLATE Latin1_General_CI_AS NULL ,
[pm_lstwo] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_wofrmt] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
[pm_wostyp] [numeric](2, 0) NULL ,
[pm_pdate] [datetime] NULL ,
[pm_itype] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[pm_ipdate] [datetime] NULL ,
[pm_ildate] [datetime] NULL ,
[pm_iival] [numeric](4, 0) NULL ,
[pm_idays] [numeric](4, 0) NULL ,
[pm_mbdate] [datetime] NULL ,
[pm_mbval] [numeric](8, 0) NULL ,
[pm_mldate] [datetime] NULL ,
[pm_mlval] [numeric](8, 0) NULL ,
[pm_mival] [numeric](8, 0) NULL ,
[pm_mpdate] [datetime] NULL ,
[pm_mdays] [numeric](4, 0) NULL ,
[pm_mcode] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
[pm_ebdate] [datetime] NULL ,
[pm_ebval] [numeric](8, 0) NULL ,
[pm_eldate] [datetime] NULL ,
[pm_elval] [numeric](8, 0) NULL ,
[pm_eival] [numeric](8, 0) NULL ,
[pm_epdate] [datetime] NULL ,
[pm_edays] [numeric](4, 0) NULL ,
[pm_evnts] [numeric](8, 0) NULL ,
[pm_etype] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
[pm_ecode] [char] (20) COLLATE Latin1_General_CI_AS NULL ,
[pm_adays] [numeric](6, 0) NULL ,
[pm_imin] [numeric](6, 0) NULL ,
[pm_imdate] [datetime] NULL ,
[pm_genwo] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_sdays] [char] (7) COLLATE Latin1_General_CI_AS NULL ,
[pm_ltrg] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[pm_fdate] [datetime] NULL ,
[pm_ftime] [char] (5) COLLATE Latin1_General_CI_AS NULL ,
[pm_vcontr] [char] (20) COLLATE Latin1_General_CI_AS NULL ,
[pm_vendor] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[pm_servcx] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[pm_crft] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_nojbprt] [bit] NULL ,
[pm_stek] [bit] NULL ,
[pm_altwo] [bit] NULL ,
[pm_wotype] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[pm_rowid] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I don't yet know how to supply you with sample data, so if anyone needs that, please help me ;)

// Stored Procedure below here.
-- Eerst check op bestaan SP en tabel
Set NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[mcmain].[mcmsp_werkbelasting]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure mcmain.mcmsp_werkbelasting
if not exists (select * from dbo.sysobjects where id = object_id(N'[mcmain].[werkbel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- drop table [mcmain].[werkbel]
CREATE TABLE [mcmain].[werkbel] (
[wb_nmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_stat] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[wb_pmnmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_wonmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_year] [numeric] (5,0) NULL ,
[wb_week] [numeric] (2,0) NULL ,
[wb_month] [numeric] (5,0) NULL ,
[wb_phrs] [numeric](12, 2) NULL ,
[wb_thrs] [numeric](12, 2) NULL ,
[wb_crnmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_cwnmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_eqnmr] [char] (16) COLLATE Latin1_General_CI_AS NULL ,
[wb_source] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[wb_rowid] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [mcmain].[mcmsp_werkbelasting]
(@Jaar VarChar(9))
/* 04-11-2005 CPE Om tabel werkbelasting aan te maken en te vullen.
1 mogelijke variabele @Jaar
Als deze niet wordt meegegeven, komt er een fout
*/

AS
Set NOCOUNT ON
DELETE FROM mcmain.werkbel where wb_year = @Jaar

Declare @pm_nmr VarChar(9)
Declare @pm_stat VarChar(4)
Declare @pm_date DateTime
Declare @pm_days Numeric(5,0)
Declare @pm_itype VarChar(1)
Declare @pm_iival Numeric(5,0)
Declare @pm_phrs Numeric(5,0)
Declare @pm_crew VarChar(9)
Declare @pm_craft VarChar(9)
Declare @pm_eqnmr VarChar(9)

Declare @EersteDatum DateTime
Declare @JaarStart DateTime

Set @JaarStart = Convert(DateTime,'01-01-'+@Jaar)

Declare DataCursor Cursor FAST_FORWARD READ_ONLY For
Select pm_nmr, pm_stat, pm_date, pm_days, pm_itype, pm_iival, pm_phrs, pm_crew, pm_eqnmr from mcmain.pmmas where pm_date is not null
OPEN DataCursor

FETCH NEXT FROM DataCursor into @pm_nmr, @pm_stat, @pm_date, @pm_days, @pm_itype, @pm_iival, @pm_phrs, @pm_crew, @pm_eqnmr
WHILE @@FETCH_STATUS = 0
BEGIN -- Hier begint het code block
Set @EersteDatum = @pm_date
While @EersteDatum > @JaarStart
BEGIN
If @pm_itype = 'D' Set @EersteDatum = @EersteDatum - @pm_days
Else If @pm_itype = 'W' Set @EersteDatum = DateAdd(ww, -1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'M' Set @EersteDatum = DateAdd(mm, -1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Q' Set @EersteDatum = DateAdd(mm, -3*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Y' Set @EersteDatum = DateAdd(yy, -1*@pm_iival, @EersteDatum)
END
While @EersteDatum < @JaarStart
BEGIN
If @pm_itype = 'D' Set @EersteDatum = @EersteDatum + @pm_days
Else If @pm_itype = 'W' Set @EersteDatum = DateAdd(ww, 1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'M' Set @EersteDatum = DateAdd(mm, 1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Q' Set @EersteDatum = DateAdd(mm, 3*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Y' Set @EersteDatum = DateAdd(yy, 1*@pm_iival, @EersteDatum)
END
While Year(@EersteDatum) = Year(@JaarStart)
BEGIN
-- Print @pm_nmr+' '+@pm_itype+' '+Convert(Char(6),@pm_iival)+' '+Convert(VarChar(32),@EersteDatum,105)
INSERT INTO mcmain.werkbel(wb_pmnmr, wb_stat, wb_year, wb_week, wb_month, wb_phrs, wb_source, wb_cwnmr, wb_eqnmr)
Select @pm_nmr, @pm_stat, Year(@EersteDatum), DatePart(ww,@EersteDatum), Month(@EersteDatum), @pm_phrs, 'P', @pm_crew, @pm_eqnmr
BEGIN
If @pm_itype = 'D' Set @EersteDatum = @EersteDatum + @pm_days
Else If @pm_itype = 'W' Set @EersteDatum = DateAdd(ww, 1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'M' Set @EersteDatum = DateAdd(mm, 1*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Q' Set @EersteDatum = DateAdd(mm, 3*@pm_iival, @EersteDatum)
Else If @pm_itype = 'Y' Set @EersteDatum = DateAdd(yy, 1*@pm_iival, @EersteDatum)
END
END
FETCH NEXT FROM DataCursor into @pm_nmr, @pm_stat, @pm_date, @pm_days, @pm_itype, @pm_iival, @pm_phrs, @pm_crew, @pm_eqnmr
-- Print ''
-- END

END

CLOSE DataCursor
DEALLOCATE DataCursor

Select * from mcmain.werkbel Where Convert(Varchar(4), wb_year) = @Jaar order by wb_year, wb_month, wb_week

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-06 : 22:45:51

The following will let you avoid cursors and loops in your stored procedure:

INSERT INTO mcmain.werkbel(wb_pmnmr, wb_stat, wb_year, wb_week, wb_month, wb_phrs, wb_source, wb_cwnmr, wb_eqnmr)
Select pm_nmr, pm_stat, year(a.pm_date), datepart(week, a.pm_date), month(a.pm_date), pm_phrs, 'P' as source, pm_crew, pm_eqnmr from (
Select pm_nmr, pm_stat,
CASE @pm_itype
WHEN 'D' THEN DateAdd(day, pm_iival, pm_date)
WHEN 'W' THEN DateAdd(week, pm_iival, pm_date)
WHEN 'M' THEN DateAdd(month, pm_iival, pm_date)
WHEN 'Q' THEN DateAdd(quarter, pm_iival, pm_date)
WHEN 'Y' THEN DateAdd(year, pm_iival, pm_date)
END AS pm_date, pm_phrs, pm_crew, pm_eqnmr
from mcmain.pmmas where pm_date is not null) a

I had a hell of a time figuring out your logic, unless I've really missed something, the first two loops are completely unnecessary. The first loop decrements a date if it's greater than the first of this year, then the 2nd loop increments it if it's less; it seems to me you'll end up with the original date regardless. Not to mention that the first loop could potentially run 365 times, that's one additional reason why it's slow.

I did not include the @Jaar variable anywhere as I couldn't determine exactly what you're trying to do with it. It would help if you could post some sample data with the result you're expecting from the procedure.

Rimsky
Starting Member

12 Posts

Posted - 2005-11-08 : 01:29:46

quote:
Originally posted by robvolk

The following will let you avoid cursors and loops in your stored procedure:
INSERT INTO mcmain.werkbel(wb_pmnmr, wb_stat, wb_year, wb_week, wb_month, wb_phrs, wb_source, wb_cwnmr, wb_eqnmr)
......

I had a hell of a time figuring out your logic, unless I've really missed something, the first two loops are completely unnecessary. The first loop decrements a date if it's greater than the first of this year, then the 2nd loop increments it if it's less; it seems to me you'll end up with the original date regardless. Not to mention that the first loop could potentially run 365 times, that's one additional reason why it's slow.

Okay, this helps. It gives me back one werkbel row per one pmmas row. I want to get back several werkbel rows (inserts) per pmmas row.
What I did with the first two loops was to calculate the first date a give maintenance plas was occurring in a give year, by keeping substructing the interval until I'm below the current year and then adding the interval. This way I end up with the first date of the plan in a year.
Typical recordset in would be:
pm_nmr = OM001234
pm_stat = '1000'
pm_date = 01-06-2005
pm_itype = 'M'
pm_iival = 3

Out would come several results:
No.1 The first date in that year: 01-03-2006
No 2. 01-06-2005
No 3. 01-09-2005
No 4. 01-12-2006
All in dd-mm-yyyy format

The week, month and other data can be calculated or copied, so are not relevant to this challenge ;)

So I guess I need to loop on the pm_date field. I think it boils down to: How do I get one source row to result on multiple result rows? Any idea on that?

Cees Cappelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources