There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Question

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Viewing 15 posts - 226 through 240 (of 380 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Jeff Moden (4/16/2009)
Here's the code...
[font="Courier New"]DECLARE @PrevActualCost MONEY,
        @RunningTotal   MONEY,
        @PrevID         INT
SELECT @PrevActualCost = 0,
        @RunningTotal   = 0
UPDATE t
    SET @RunningTotal = RunningTotal = @RunningTotal + ActualCost,
        @PrevID       = ID
   FROM #tmp t WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)
SELECT TOP 1000 * FROM #tmp ORDER BY ID[/font]

Jeff what is the @PrevActualCost variable for, since it doesn't appear to be used?

bruce.trimpop SSC Veteran Points: 249 More actions · Answer 2

Matt Whitfield (4/14/2009)

bruce.trimpop (4/14/2009)
Bruce
Here is the SQL i used on 2000 to re-create the data and tables - some mods were required, so hope you can check.
Is it possible that you could post the cursor you use? I am having trouble understanding how some of the data interacts, and seeing the source would really help.
My initial reaction would be that the table layout would benefit from a bit of a redesign in terms of being somewhere close to 3NF, which would probably have made the set based approach a lot easier in the first place. I presume that you cannot change table structures however, and will respect that in my solution!

It does (need redesign) and I can't. :crying:

Posting the cursor code probably won't help much since I stripped down the functionality to a bare minimum.

The code does a great deal more than what I've posted and would probably confuse the matter even more. I'll try to describe critical part of the process, maybe that will help. :crazy:

A client (dbo.client) receives services (dbo.service) for which a charge is calculated and stored in service.charge.

The client is assessed a fee based on ability to pay (dbo.clientfee) that can be a flat dollar amount or a percentage of the charge. The fee can be determined in 1 of 2 ways. Either a flat amount which is stored in dbo.clientfee or a table (dbo.psfeetablemstr) driven amount based on some client data falling into a set of ranges assigned to that table (dbo.psfeetable). All of this is conditional based on the service date falling within effective date ranges for the client fee (dbo.clientfee) or the fee table (dbo.psfeetablemstr). Also conditional on whether the service is covered by the particular fee (dbo.clientfee or dbo.psfeetablemstr) as determined by service components and their possible values as set up in (dbo.limitcodes) Also conditional on whether the particular client fee is capped by number of services and/or dollar totals and whether the cap has been met. The crux of the problem is the fee table (dbo.psfeetablemstr) is user configurable to use any client related table and any column in that table as the value used to compare against the fee table ranges (dbo.psfeetable) to find what slot the client falls in and what fee to use. I could figure out no way of getting those table/column names out of the fee tables and used in a query without building a dynamic query to return the psfeetable

row that contained the range that that client's data fell into. And given the unlimited number and in any combination of client fees I could figure no way of doing this without using a cursor.

I guess it boils down to:

"Is there a way to do a set based select statement that is based on the tables and columns used in the select being stored in a table?"

example:

If a psfeetablemstr row contains table1_vc = 'dbo.client', column1_vc = 'Income', table2_vc = 'dbo.sometable', column2_vc = 'somecolumn'

from that I need a select that is something like:

SELECT ft.value3

FROM dbo.client t1

JOIN dbo.sometable t2 ON t1.clientid = t2.clientid

JOIN dbo.psfeetable ft ON t1.income >= ft.valuemin1 and t1.income = ft.valuemin2 and t2.somecolumn <= ft.valuemax2

JOIN dbo.psfeetablemstr ftm ON ft.psfeetablemstrid = ftm.uniqueid

JOIN dbo.clientfee cf ON t1.clientid = cf.clientid

JOIN dbo.service s ON s.clientid = t1.clientid

Keeping in mind that a psfeetablemstr row might contain 1 or 2 table/column values and might be different table/column values from row to row.

I can't figure out how to do this other than with a cursor and dynamically building the select based on the table/column names stored in the

psfeetablemstr table for that particular fee table.

Actually I think I can pare down the example even more to get to the crux of my problem.

CREATE TABLE [dbo].[client](

[clientid] [int] not null,

[income] [numeric] (12,2)

)

CREATE TABLE [dbo].[sometable](

[clientid] [int] not null,

[dependents] [int]

)

CREATE TABLE [dbo].[clientfee](

[uniqueid] [int] NOT NULL,

[clientid] [int] not null,

[psfeetablemstrid] int,

CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[psfeetablemstr](

[uniqueid] [int] NOT NULL,

[table1_vc] [varchar](20) NULL,

[column1_vc] [varchar](20) NULL,

[table2_vc] [varchar](20) NULL,

[column2_vc] [varchar](20) NULL,

CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[psfeetable](

[uniqueid] [int] NOT NULL,

[value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),

[value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),

[value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),

[value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),

[value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),

[psfeetablemstrid] [int] NOT NULL,

CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

---Sample data

---dbo.client

INSERT INTO [dbo].[client] ([clientid], [income])

VALUES (1,25000)

INSERT INTO [dbo].[client] ([clientid], [income])

VALUES (2,10000)

---dbo.sometable

INSERT INTO [dbo].[sometable] ([clientid], [dependents])

VALUES (1,2)

INSERT INTO [dbo].[sometable] ([clientid], [dependents])

VALUES (2,1)

--dbo.clientfee

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])

VALUES (1,1,1)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])

VALUES (2,1,2)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])

VALUES (3,2,1)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])

VALUES (4,2,2)

---dbo.psfeetablemstr

INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc])

VALUES (1,'dbo.client','income','dbo.sometable','dependents')

INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc])

VALUES (2,'dbo.sometable','dependents',NULL,NULL)

---dbo.psfeetable