Help designing view

Hi folks,
I've got a view, illustrated at www.dbconsult.dk/ms/sqlquestion.jpg.
There must be a smarter way of doing it, but I can't figure it out:
I wish to get rid of the CASE's.
For each row in tblFejlantal I wish for each row in tblFejlKoder to be
listet as a column.
In the illustration the alias F10/F11/etc equals tblFejlKode.FejlKode,
but with a prefix of "F".
Thanks in advance for your efforts! ;-)
Regards /SnedkerIf you can't alter the design of this database and/or you can't or simply
don't want to do this kind of transformations on the client (I'm guessing
it's for presentation purposes) maybe you might find a few pointers in this
article by Itzik Ben-Gan:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML|||On Tue, 13 Sep 2005 04:19:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
If you have suggestions to the design, I'm all ears! Anything goes.
Regards /Snedker

>If you can't alter the design of this database and/or you can't or simply
>don't want to do this kind of transformations on the client (I'm guessing
>it's for presentation purposes) maybe you might find a few pointers in this
>article by Itzik Ben-Gan:
>http://www.windowsitpro.com/Article...15608.html?Ad=1
>
>ML|||Either post the requirements regarding this specific element of your system
or post DDL and some sample data.
In any case when designing a data-centric solution follow these basic
principles:
1) identifiy all entities, starting with the essential ones;
2) identify relationships between entities; and
3) identify attributes of all entities, with special attention to those
required by genuine business needs.
ML|||On Tue, 13 Sep 2005 05:17:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
Thanks for your response.
As for the database design, normalizing should be okay.
Let's say we have two tables, tblPrimary and tblForeign.
tblPrimary has three columns with one row:
PrimaryID ErrValue ForeignID
1 21.7 5
tblForeign has two columns with 5 rows
ForeignID ErrorCode
1 10
2 20
3 30
4 40
5 50
My view should look like:
PrimaryID Err10 Err20 Err30 Err40 Err50
1 0 0 0 0 21.7
If a row (6, 55) is added to tblForeign the result would be
PrimaryID Err10 Err20 Err30 Err40 Err50
Err55
1 0 0 0 0 21.7 0
I want each row in tblForeign represented in my view as a column.
/Snedker

>Either post the requirements regarding this specific element of your system
>or post DDL and some sample data.
>In any case when designing a data-centric solution follow these basic
>principles:
>1) identifiy all entities, starting with the essential ones;
>2) identify relationships between entities; and
>3) identify attributes of all entities, with special attention to those
>required by genuine business needs.
>
>ML|||Based on the sample you posted I strongly believe the solution described in
Itzik's article is the way to go for you.
Now, turning it into a view is a bit trickier. One way that I can think of
(and works) is a not-really-best-practice solution involving OPENQUERY.
E.g.:
create view dbo.CrossTabbedView
as
select <column list>
from openquery(<server_name>, 'exec <procedure name> <parameters>')
As I said it might work as expected, yet it's pretty resource-intensive. On
the other hand, if you expect it to be fast, it may not "work as expected".
:)
ML

Help Cursor Based Stored Procedure Is Getting Slower and Slower!

I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.

The procedure works like this.
The first Cursor stores a unique account and duedate combination from
the view.
It then finds all the accts in the view that have that account duedate
combo and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because a
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups.

Here is my procedure I hope someone can shead some light on this. My
boss is giving me heck about it. (I think he thinks Girls cant code!)
I got this far I hope someone can help me optimize it further.

CREATE PROCEDURE dbo.sp_PromiseStatus
AS

BEGIN
SET NOCOUNT ON

/* Global variables */
DECLARE @.tot_pay money
DECLARE @.rec_upd VARCHAR(1)
DECLARE @.todays_date varchar(12)
DECLARE @.mActivityDate2_temp datetime
DECLARE @.tot_paydate datetime
/* variables for cursor ACT_CUR1*/
DECLARE @.mAcct_Num1 BIGINT
DECLARE @.mDueDate1 datetime

/* variables for ACT_CUR2 */
DECLARE @.mAcct_Num2 BIGINT
DECLARE @.mActivity_Date2 datetime
DECLARE @.mPromise_Amt_1 money
DECLARE @.mPromise_Status varchar(3)
DECLARE @.mCurrent_Due_Amt money
DECLARE @.mDPD int
DECLARE @.mPromise_Date datetime

SELECT @.todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''

DECLARE ACT_CUR1 CURSOR FOR
SELECT DISTINCT
A.ACCT_NUM,
A.DUE_DATE
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS A
OPEN ACT_CUR1
FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.rec_upd = 'N '

DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)
WHERE B.ACCT_NUM = @.mAcct_Num1
ANDB.DUE_DATE = @.mDueDate1
ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASE
B.Time_Obtained
WHEN 0 THEN 0
ELSE 1
END Desc, B.Time_Obtained

OPEN ACT_CUR2
FETCH NEXT FROM ACT_CUR2
INTO @.mAcct_Num2 ,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

--CHECK----------------------
--DECLARE @.PrintVariable2 VARCHAR (8000)
--SELECT @.PrintVariable2 = CAST(@.MACCT_NUM2 AS VARCHAR)+'
'+CAST(@.MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@.MPROMISE_AMT_1 AS
VARCHAR)+' '+CAST(@.MPROMISE_STATUS AS VARCHAR)+'
'+CAST(@.mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@.mDPD AS VARCHAR)+'
'+CAST(@.mPromise_Date AS VARCHAR)
--PRINT @.PrintVariable2
--END
CHECK------------------

IF @.mDPD >= 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE < @.mActivity_Date2 + 15
--CHECK----------------------
--DECLARE @.PrintVariable3 VARCHAR (8000)
--SELECT @.PrintVariable3 ='Greater=30 DOLLARS COLLECTED'
--PRINT @.PrintVariable3
--END
CHECK------------------
END
ELSE IF @.mDPD < 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE BETWEEN @.mActivity_Date2 AND
@.mPromise_Date + 5
--CHECK---------------------
--DECLARE @.PrintVariable4 VARCHAR (8000)
--SELECT @.PrintVariable4 ='Less 30 DOLLARS COLLECTED'
--PRINT @.PrintVariable4
--END CHECK------------------
END

------------MY REVISED
LOGIC-----------------
IF @.rec_upd = 'N'
BEGIN

IF @.mDPD >= 30
BEGIN

SELECT @.mActivityDate2_temp = @.mActivity_Date2 + 15

--DECLARE @.PrintVariable5 VARCHAR (8000)
--SELECT @.PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'
--PRINT @.PrintVariable5

END
ELSE IF @.mDPD < 30
BEGIN
SELECT @.mActivityDate2_temp = @.mPromise_Date + 5

--DECLARE @.PrintVariable6 VARCHAR (8000)
--SELECT @.PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'
--PRINT @.PrintVariable6

END

IF @.tot_pay >= 0.9 * @.mCurrent_Due_Amt--used to be promise amt
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_COLL = @.tot_pay
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PK.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

SELECT @.rec_upd = 'Y '
END
IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS NULL)
AND( @.mActivityDate2_temp > @.todays_date )--need to put 1day
of month here for snapshot9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into OP which is the original Activity Date.
--The record will hold this date until it goes into PK,PB,or
IP.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.mActivity_Date2
WHERE CURRENT OF ACT_CUR2
END

END
ELSE IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS
NULL)
AND( @.mActivityDate2_temp <= @.todays_date )--need to put 1day
of month here for snapshot 9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'PB',
TOTAL_DOLLARS_COLL = case when @.tot_pay is null
then 0 else @.tot_pay end
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PB.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END
END
ELSE IF @.rec_upd = 'Y'
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'IP',
TOTAL_DOLLARS_COLL = 0
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into IP.
IF @.mPromise_Status NOT IN ('IP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END

FETCH NEXT FROM ACT_CUR2 INTO @.mAcct_Num2
,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date
END
CLOSE ACT_CUR2
DEALLOCATE ACT_CUR2

FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1
END
CLOSE ACT_CUR1
DEALLOCATE ACT_CUR1

SET NOCOUNT OFF

END
GOWithout getting into code, I think you should consider using faster type
of cursor if possible. FORWARD ONLY for example|||If you are a beginner then avoid cursors. They are rarely a necessary or
good solution to a problem in SQL. You will learn bad practices if you mess
with cursors before you can write good set-based SQL.

It doesn't look like you are doing anything in your code for which a cursor
makes sense so I recommend you start again from scratch. If you need help,
try to pare the problem down to it's essential features and then post again
with more information (DDL, some sample data, required results). This
article explains how best to ask for help:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||>> I am beginner at best so I hope someone that is better can help. <<

Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!

>> The problem is that this procedure is taking longer and longer to
run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.

>> The first Cursor [magnetic tape file] stores a unique account and
duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.

>> It then finds all the accts in the view that have that account
duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)

>> .. and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromises
SET status_date = CURRENT_TIMESTAMP,
promise_status = CASE WHEN ..END,
total_dollars_coll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromises AS A, Applicablepayments AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromises.acct_num = A.account_num
AND ApplicablePromises.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.|||Philip Mette (philipdm@.msn.com) writes:
> I am begginner at best so I hope someone that is better can help.
> I have a stored procedure that updates a view that I wrote using 2
> cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> do it using reqular transact SQL.
> The problem is that this procedure is taking longer and longer to run.
> Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records every month.

While cursor-based solutions sometimes may be defendible and even be
the only solution, the cost for a cursor can be pretty severe. The
corresponding set-based solution is often several magnitudes faster.
Processing 30000 rows with a cursor is not going to be anywhere near
fast.

Just like David I would recommend you to start with a clean paper. I
started to look at your code, but, frankly, all the uppercase and the
poor formatting is quite deterring. And not knowing the tables and not
having sample data is not making things any easier.

In the short run, you may make some improvements by making the cursors
INSENSITIVE by adding this keyword before CURSOR, and by replacing
WHERE CURRENT OF with the actual key values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok well I have been yelled at worse;) No I see what you are saying. I
should have used Char variable not Varchar(1).That was a adjustment
later on that frankly wasnt changed properly. I used Cursors based on
a recommendation by a "SQL Guru" Obviously this is not a good
description of him. I am going to try this using the skeleton you gave
me.It is pointing me in a right direction I think.
Again please understand I am a beginner Query writer when it comes to
SQL and I am very good with VB ,.Net and Java(Used to do front end
work and analysis not DB.)I quess I was thinking of this like code
language,ie the use of arrays etc. Thanks for your 'direct' honesty
and help.

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0411242243.2c96bbf0@.posting.google.com>...
> >> I am beginner at best so I hope someone that is better can help. <<
> Virutally everything you have done is wrong.
> 1) The data element names violate ISO-11179 standards. What does a
> VARCHAR(1) mean?
> 2) You should never write a cursor in an applications. Thye are for
> utility programs.
> 3) You are using FLOAT and MONEY for currency amounts. And you got
> the CAST() syntax wrong.
> 4) Unlike the 3GL languages, SQL has temporal data types. Why did you
> avoid them in favor of COBOL-style string processing? You even load
> the current timestamp into a string!
> >> The problem is that this procedure is taking longer and longer to
> run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records [sic] every month. <<
> No. The basic algorithm you have written is a COBOL tape file merge.
> 30K rows is tiny.
> >> The first Cursor [magnetic tape file] stores a unique account and
> duedate combination from the view. <<
> The view name has a silly "vw_" prefix in violation of ISO-11179. And
> a table has no records!!! Rows are not records! Until you understand
> that you will never write good SQL. A table is a set, and in SQL we
> process data in whole sets, not single records.
> >> It then finds all the accts in the view that have that account
> duedate combo .. <<
> No, you write a query that finds the set of (account_nbr, duedate)
> >> .. and loads them into Cursor 2 this groups them together for data
> manipulation. The accounts have to be grouped this way because an
> account can have different due dates and multiple records within each
> account due date combo and they need to be looked at this way as
> little singular groups. <<
> Think about the phrase "single group" for a minute :) A GROUP BY is
> used to build grouped tables.
> What you seem to be trying to do is update a table of promises. That
> should be one UPDATE statement. The skeleton for the statement would
> be something like this:
> CREATE PROCEDURE PromiseStatus (..)
> AS
> UPDATE ApplicablePromises
> SET status_date = CURRENT_TIMESTAMP,
> promise_status = CASE WHEN ..END,
> total_dollars_coll
> = (SELECT SUM(P.pay_amt),
> FROM ApplicablePromises AS A, Applicablepayments AS P
> WHERE P.acct_num = A.acct_num
> AND ApplicablePromises.acct_num = A.account_num
> AND ApplicablePromises.due_date = A.due_date
> AND P.activity_date BETWEEN ...
> AND GROUP BY A.acct_num, A.due_date)
> WHERE ... ;
> You put all the IF_THEN_ELSE logic into a CASE expression. You put
> the total into a scalar subquery. You use temporal functions to get
> the current timestamp.
> Quit thinking about one column at a time. That is how you process a
> field in a record. A table is made of rows, and you think of the row
> as the unit of work.|||Thanks Erland.
A few Questions:
1.What does insensitive do?
2.Instead of Where Current Of
If I used Activity_Record_Num(The Primary Key)
which is distinctive would that work?
3.I use a statement called (UPDLOCK) in this part of the code:
DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWPROMISEACTIVITYRECORDS B (UPDLOCK)

Does that effect performance speed as well? This is the only process
that runs against this table so I am wondering if it is needed.

Just so you know I am working on a rewrite like you suggested. In the
short, I would like to add these suggestions that you made to the
cursor based SP.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ACF2C23D6DBYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > I am begginner at best so I hope someone that is better can help.
> > I have a stored procedure that updates a view that I wrote using 2
> > cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> > do it using reqular transact SQL.
> > The problem is that this procedure is taking longer and longer to run.
> > Up to 5 hours now! It is anaylizing about 30,000 records. I think
> > partly because we add new records every month.
> While cursor-based solutions sometimes may be defendible and even be
> the only solution, the cost for a cursor can be pretty severe. The
> corresponding set-based solution is often several magnitudes faster.
> Processing 30000 rows with a cursor is not going to be anywhere near
> fast.
> Just like David I would recommend you to start with a clean paper. I
> started to look at your code, but, frankly, all the uppercase and the
> poor formatting is quite deterring. And not knowing the tables and not
> having sample data is not making things any easier.
> In the short run, you may make some improvements by making the cursors
> INSENSITIVE by adding this keyword before CURSOR, and by replacing
> WHERE CURRENT OF with the actual key values.|||Philip Mette (philipdm@.msn.com) writes:
> Thanks Erland.
> A few Questions:
> 1.What does insensitive do?

The meaning of INSENSITIVE is that the dataset does not change
once the cursor has been created. SQL Server copies the data to a
worktable. This may sound like some overhead, and it probably is, but
it's a small overhead in comparison with the iterative processing.

The default cursor type is keyset. I think it means that the keys are
stable, but updates in non-key columns will be reflected. But I have
never fully grasped keyset cursors so I could be wrong. What I have
seen is some really horrible query plans to set up a keyset-driven
cursor. Sure, it was in 6.5, but I see no reason to try it again.

> 2.Instead of Where Current Of
> If I used Activity_Record_Num(The Primary Key)
> which is distinctive would that work?

I didn't look to close on your code, but you should pick data from
the cursor which identifies the current row(s) being processed. (Note
that you could run a cursor over an aggregate, and thus update several
rows at same time. There are situations when this is a possible solution.

> 3.I use a statement called (UPDLOCK) in this part of the code:
>...
> Does that effect performance speed as well?

Not very likely. Since you don't seem to have an active transaction, the
effect of the UPDLOCK is none anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

I got the process run time to 2:00 minutes! The solution was to create
a composite index on Account and Due_date in the tables, which is what
is in my where clauses in my select statements. That and changing the
cursors to Insensitive, made this query fast. This amount of time is
more than acceptable. Thanks for pointing me in the right direction. I
am going to continue to look into a transact SQL solution in the
future.
Thanks Again,

Jill
(Using Phil's Login Name. Dont have a google acct.)

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ADDFEC37F0BYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > Thanks Erland.
> > A few Questions:
> > 1.What does insensitive do?
> The meaning of INSENSITIVE is that the dataset does not change
> once the cursor has been created. SQL Server copies the data to a
> worktable. This may sound like some overhead, and it probably is, but
> it's a small overhead in comparison with the iterative processing.
> The default cursor type is keyset. I think it means that the keys are
> stable, but updates in non-key columns will be reflected. But I have
> never fully grasped keyset cursors so I could be wrong. What I have
> seen is some really horrible query plans to set up a keyset-driven
> cursor. Sure, it was in 6.5, but I see no reason to try it again.
> > 2.Instead of Where Current Of
> > If I used Activity_Record_Num(The Primary Key)
> > which is distinctive would that work?
> I didn't look to close on your code, but you should pick data from
> the cursor which identifies the current row(s) being processed. (Note
> that you could run a cursor over an aggregate, and thus update several
> rows at same time. There are situations when this is a possible solution.
> > 3.I use a statement called (UPDLOCK) in this part of the code:
> >...
> > Does that effect performance speed as well?
> Not very likely. Since you don't seem to have an active transaction, the
> effect of the UPDLOCK is none anyway.|||Hello guys,

I have may be similar problem.
I need to recalculate a progressive balance based on a table with the
following structure:

EcrID varchar(15)
AccountNumber varchar(15)
EcrDate DateTime
EcrAmount float
ProgBalance float

The program has to calculate the progbalance which is for each row :
progbalance=progbalance + EcrAmount
Rows are sorted by AccountNumber and EcrDate.

Is it possible to that by a single update statement or do I have to
use cursors?

(Sorry for my english)

Many thanks for your help.|||On 29 Nov 2004 10:11:58 -0800, badiane wrote:

>Hello guys,
>I have may be similar problem.
>I need to recalculate a progressive balance based on a table with the
>following structure:
>EcrID varchar(15)
>AccountNumber varchar(15)
>EcrDate DateTime
>EcrAmount float
>ProgBalance float
>The program has to calculate the progbalance which is for each row :
>progbalance=progbalance + EcrAmount
>Rows are sorted by AccountNumber and EcrDate.
>Is it possible to that by a single update statement or do I have to
>use cursors?
>(Sorry for my english)
>Many thanks for your help.

Hi badiane,

The best option is to remove ProgBalance from the table and calculate it
in a view instead. If it's a column in the table, you'll have to keep
updating large chunks of data in the table whenever rows are inserted (or
even changed) in a non-chronological order.

CREATE VIEW MyView
AS
SELECT a.EcrID, a.AccountNumber, a.EcrDate, a.EcrAmount,
(SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate) AS ProgBalance
FROM MyTable AS a
go
(untested)

If you really must store it in a table, you can use the same technique for
the update:

UPDATE MyTable
SET ProgBalance = (SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = MyTable.AccountNumber
AND b.EcrDate <= MyTable.EcrDate)
WHERE ......
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The best option is to remove ProgBalance from the table and calculate it
> in a view instead. If it's a column in the table, you'll have to keep
> updating large chunks of data in the table whenever rows are inserted (or
> even changed) in a non-chronological order.

On the other hand, this may never happen in a table like this. (That
would be a busines rule that the balance shown is always in booked order.)
Computing the balance on each invocation may be very expensive, for an
account with many transactions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

badiane (alioune_badiane@.yahoo.com) writes:
> I have may be similar problem.
> I need to recalculate a progressive balance based on a table with the
> following structure:
> EcrID varchar(15)
> AccountNumber varchar(15)
> EcrDate DateTime
> EcrAmount float
> ProgBalance float
> The program has to calculate the progbalance which is for each row :
> progbalance=progbalance + EcrAmount
> Rows are sorted by AccountNumber and EcrDate.
> Is it possible to that by a single update statement or do I have to
> use cursors?

Standard remark:

If you in your posting include the following:

o CREATE TABLE statement for your table(s).
o INSERT statements with sample data.
o The desired result given that sample.

You have good chances to get a tested solution.

This is an untested solution:

UPDATE tbl
SET ProgBalance = (SELECT SUM(EcrAmount)
FROM tbl b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate)
FROM tbl b

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 29 Nov 2004 22:30:51 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The best option is to remove ProgBalance from the table and calculate it
>> in a view instead. If it's a column in the table, you'll have to keep
>> updating large chunks of data in the table whenever rows are inserted (or
>> even changed) in a non-chronological order.
>On the other hand, this may never happen in a table like this. (That
>would be a busines rule that the balance shown is always in booked order.)
>Computing the balance on each invocation may be very expensive, for an
>account with many transactions.

Hi Erland,

Indeed. That is why I did include the UPDATE statement as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I think that the view is a good solution, I tested with the following
data and think that it is OK. Using a view is it a good solution for
performance point of vue?

The table:
---

CREATE TABLE [dbo].[test2_h_ecr] (
[numero_operation] [nvarchar] (14) NOT NULL ,
[numero_ecriture] [smallint] NOT NULL ,
[numero_compte] [nvarchar] (13) NULL ,
[date_ecriture] [datetime] NULL ,
[montant] [float] NULL ,
[solde_progressif] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test2_h_ecr] WITH NOCHECK ADD
CONSTRAINT [PK_test2_h_ecr] PRIMARY KEY CLUSTERED
(
[numero_operation],
[numero_ecriture]
) ON [PRIMARY]
GO

The sample Data :
------

10003350136522000811012003-12-16 12:41:10.000-425690.0-425690.0
10004012025622000811012004-01-12 00:00:00.00042569.0-383121.0
10004041103822000811012004-02-10 00:00:00.00012795.0-370326.0
10004041103922000811012004-02-10 00:00:00.00029774.0-340552.0
10004070030822000811012004-03-10 00:00:00.00042569.0-297983.0
10004106011322000811012004-04-15 00:00:00.00042569.0-255414.0
10004138009222000811012004-05-17 00:00:00.00042569.0-212845.0
10004167017422000811012004-06-15 00:00:00.00042569.0-170276.0
10004197033722000811012004-07-15 00:00:00.00021840.0-148436.0
10004197033822000811012004-07-15 00:00:00.00020729.0-127707.0
10004229009222000811012004-08-16 00:00:00.00042569.0-85138.0

The view (modified a bit):
--------
CREATE VIEW MyView
AS
SELECT a.numero_operation, a.numero_ecriture, a.numero_compte,
a.date_ecriture,
a.montant,
(SELECT SUM(b.montant)
FROM test2_h_ecr AS b
WHERE b.numero_compte = a.numero_compte
AND b.date_ecriture <= a.date_ecriture
AND b.numero_operation <= a.numero_operation
AND b.numero_ecriture <= a.numero_ecriture) AS ProgBalance
FROM test2_h_ecr AS a
go

The compared result sql :
--------

select * from MyView order by
date_ecriture,numero_operation,numero_ecriture

select * from test2_h_ecr order by
date_ecriture,numero_operation,numero_ecriture

I obtained the same result is it. Is it correct?

Many thanks guys.|||On 1 Dec 2004 11:22:14 -0800, badiane wrote:

>I think that the view is a good solution, I tested with the following
>data and think that it is OK.

Hi badiane,

I would test it with different values and different combinations of values
for numero_compte, date_ecriture, numero_operation and numero_ecriture;
your current test set is way too limited and I'm not sure if the view will
always return the results you expect.

> Using a view is it a good solution for
>performance point of vue?

I *think* it is, but it does depend. If you often change data without
following the chronological order, the view would definitely win (as such
a change would require updates to many rows in the table). On the other
hand, if most changes are for "recent" dates, the total change frequency
is low and the ProgBalance is used extensively, you might find prefer to
store the ProgBalance and use triggers to recalculate ProgBalance for
changed rows (and rows that come "later").

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help coverting a stored procedure to a view

Can someone help me convert this stored procedure to a view? It is using two UDFs.

I appreciate this very much!

@.Startdatetime, @.End datetime ASSELECTC.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,dbo.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel, dbo.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description FROMtblClients CINNERJOIN tblPMTs PON C.Client_ID = P.Client_IDINNERJOIN tblPMTReceipts PRON P.PMT_ID = PR.PMT_IDINNERJOINtblClientAddresses AON C.Client_ID = A.Client_IDLEFTOUTER JOINtblSoftCreditsPMTS SCON C.Client_ID = SC.SC_Client_IDWHERE(PR.PaymentDateBETWEEN @.StartAND @.End)GROUPBY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.TitleORDERBY pmtLevelRETURN

Hi,

That stored procedure cannot be converted into a View because of the the parameters in the WHERE clause and the fact that there is a Group By that hides the PaymentDate from the results.

SQL Views do not support parameters. Because the PaymentDate is not part of the output the users of your View would not be able to provide filter by PaymentDate.

The alternative is to convert it to a SQL function that returns a table:

CREATE FUNCTION dbo.MyFunction(@.Startdatetime,
@.End datetime)

RETURNS TABLE
AS
RETURN

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description
FROM

tblClients CINNERJOIN
tblPMTs P

ON C.Client_ID = P.Client_IDINNERJOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_IDINNERJOIN
tblClientAddresses A

ON C.Client_ID = A.Client_IDLEFTOUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDateBETWEEN @.StartAND @.End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

This would allow you to pass parameters to the function from a SELECT statement a get a result set without the need to run a stored procedure.

Example of how you would use this function:

SELECT * FROM dbo.MyFunction('1/1/2007','6/1/2007')

Hope this helps!

David

|||

Thank you for sharing with me. I don't think I will be able to use the Table Valued Function with the reporting tool I have. It only works with tables and views.

|||

What is your reporting tool?

Help again

Ok I was able to create a view through the GamingCommissiondb for all the other tables in the LicensingActions.dbo except for this table can someone help me our here?My first guess would be a typo.

After that, a permissions problem.

Then things start to get interesting!

-PatP

HELP ! CoCreate of DSO for MSDAORA.1

I have configured an Linked Server from SQL Server to Oracle 9i
database.
With this link Server, i have create in a SQL Server Database a new
view who querying a view on this Linked Server (Oracle).
Also, i have an application that open a connection via ADODB with
a SQL Server database who have a view who refer to an object on Oracle
Linked Server. The connection to SQL Server is make via NT
Authentification.
When i'm connect as a domain administrator on the computer an i run
this application and i consult the view, i have no problem.
But when i'm connect with another user (who is not domain
administrator) and i run the same application i obtain this error
"[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
[Non-interface error: CoCreate of DSO for MSDAORA.1 returned
0x80040154]"
I have search a lot on this problem but with not a lot of
solutions.
Thank !!
Jocelyn"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401150610.4efeb024@.posting.google.com...
> I have configured an Linked Server from SQL Server to Oracle 9i
> database.
> With this link Server, i have create in a SQL Server Database a new
> view who querying a view on this Linked Server (Oracle).
> Also, i have an application that open a connection via ADODB with
> a SQL Server database who have a view who refer to an object on Oracle
> Linked Server. The connection to SQL Server is make via NT
> Authentification.
> When i'm connect as a domain administrator on the computer an i run
> this application and i consult the view, i have no problem.
> But when i'm connect with another user (who is not domain
> administrator) and i run the same application i obtain this error
> "[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
> [Non-interface error: CoCreate of DSO for MSDAORA.1 returned
> 0x80040154]"
> I have search a lot on this problem but with not a lot of
> solutions.
>
How is security set up for the linked server?
See sp_addlinkedsrvlogin an explanation of this topic.
David|||Hi !!
I've set Linked server security as "They will be mapped to" with
Oracle username and password (Remote user and Password).
This linked server is set with "Collation Compatible" (for
performance) and "Data Access".
Also, i mapped the same NT user ex. : "Domain\User" with the remote
user and password for Oracle.
Thanks !!
Jocelyn|||"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401151052.6281fecf@.posting.google.com...
> Hi !!
> I've set Linked server security as "They will be mapped to" with
> Oracle username and password (Remote user and Password).
> This linked server is set with "Collation Compatible" (for
> performance) and "Data Access".
> Also, i mapped the same NT user ex. : "Domain\User" with the remote
> user and password for Oracle.
> Thanks !!
> Jocelyn
It almost sounds like the server process is impersonating your domain user
when it makes the CoCreate call (that's the COM call that creates the Oracle
OleDb driver object).
Try doing this with a SQL login mapped to the same user and see if it makes
any difference.
The thing is this: The Oracle OleDb driver loads the OracleClient, which
needs certian file and registry permissions on the box. If you try to load
the OracleClient while impersonating a user who cannot do this, then you
might get the failure you noticed.
I do not know why the server would be impersonating the domain user when it
accesses the linked server, but it might be trying to pass the windows
identity on to the Oracle server.
David

Help - View Trigger or Table Trigger

Hello All,
My application takes data readings every few seconds and stores a
couple of data values for piece of equipment in a table for historic
trending. The table is simlar to:
CREATE TABLE [DataTest] (
[DataID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EquipmentID] [int] NOT NULL,
[DataTimestamp] [datetime] NOT NULL ,
[DataReading1] [decimal](18, 4) NOT NULL,
[DataReading2] [decimal](18, 4) NOT NULL
[DataReading3] [decimal](18, 4) NOT NULL
)
My application needs to alert users when data values fall outside of
acceptable thresholds. It also must allow users to view a history of
when these "alerts" have triggered.
I would like to utilize the database (SQL Server 2000) as much as
possible for this functionality. I originally thought I could create a
new View for each alert that users set. For example,
CREATE VIEW dbo.Alert_25
AS
SELECT * FROM DataTest WHERE DataReading1 > 200 AND DataReading2 < 500
WHERE EquipmentID = 1
Each view would then contain the history of when that alert triggered.
I then thought I could add a trigger to that View to notify the user
that the data has gone out of range. This is where things fall apart.
The inserts are happening on the main table not the view, so a trigger
on the view does not fire.
SO...here are my questions.
1. I would like to keep the trigger associated with the View if
possible. That way, if the user deletes the View the triggers can
easily be removed as well. Is there anyway for the trigger to fire if
it is on the View?
2. I guess my alternative would be to have both Views for the alert
histories and multiple triggers on the data table. Is this an
acceptable approach? Say the user where to create 100 different alerts.
What impact on the server would this have if this created 100 different
triggers on the same data table?
I appreciate your opinions and advice. If I am way off base all
together, I appreciate any other suggestions.
Thanks,
YofnikOn 15 Aug 2005 13:09:39 -0700, Yofnik wrote:
(snip)
>SO...here are my questions.
>1. I would like to keep the trigger associated with the View if
>possible. That way, if the user deletes the View the triggers can
>easily be removed as well. Is there anyway for the trigger to fire if
>it is on the View?
Hi Yofnik,
No. A view can have only an INSTEAD OF trigger defined on it, and that
will only fire when an insert, update or delete is executed with the
view as target.

>2. I guess my alternative would be to have both Views for the alert
>histories and multiple triggers on the data table. Is this an
>acceptable approach? Say the user where to create 100 different alerts.
>What impact on the server would this have if this created 100 different
>triggers on the same data table?
It would cause a dramatic slowdown of your data modifications. Keeping
it all in one trigger would also cause a slowdown, but not as much.
Anyway, I think you should also reconsider the first stetp. You mention
"alerting users" without going into the specifics, but I have a hunch
that you plan to send out an email. And sending email from a trigger is,
as Orwell would say, doubleplusungood practice. Not only because it will
slow down your inserts tremendously, but also becuase you are mingling
in-transaction and out-transaction actions. What happens if the
transaction fails and is rolled back immediately after the mail is sent?
Your best bet is probably to investigate what Notification Services can
do for you.
If NS can't be used in your case, then create your views, write a script
that checks for entries in each of the views without matching entry in
the "alert history" table and sends a mail if it finds one. Use Agent to
schedule this as a job that runs as often as you need it (depends on how
much time may pass between the measurement and the alert).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I suppose I did not mention HOW the trigger would alert users. I
definitely was not planning on sending an email from the trigger. I
would like the trigger to simply put a new row in the AlertHistory
table with the AlertID and a flag indicating if it has been processed.
Then an external application can regularly poll this one table and grab
all alert IDs that have not been processed and send out an email. The
external application will then set the flag to indicate the alerts have
been processed.
With that said, is your last suggestion my best approach? Is there an
easy way to check for new rows in ALL views? I guess that would require
dynamic SQL then, huh?|||On 15 Aug 2005 13:42:25 -0700, Yofnik wrote:

>Sorry, I suppose I did not mention HOW the trigger would alert users. I
>definitely was not planning on sending an email from the trigger. I
>would like the trigger to simply put a new row in the AlertHistory
>table with the AlertID and a flag indicating if it has been processed.
>Then an external application can regularly poll this one table and grab
>all alert IDs that have not been processed and send out an email. The
>external application will then set the flag to indicate the alerts have
>been processed.
>With that said, is your last suggestion my best approach? Is there an
>easy way to check for new rows in ALL views? I guess that would require
>dynamic SQL then, huh?
Hi Yofnik,
If you only want to insert a row in the AlertHistory table, I'd probably
go for a trigger. But just ONE trigger - nod hundreds of 'em!
Either hard-code the limits that will cause an alert in the trigger (and
make sure that allchange requests go through one channel - i.e. you). If
you must have flexibility for the ussers to add, remove or change the
upper and lower limits for alerts, than create a second table to hold
all the limits, and join to that in the trigger to determine if any
alerts are generated. If you need more help on how to design this table
and how to code the trigger, give us some more information on your
current tables and data. Check out www.aspfaq.com/5006 to find out what
inforamtion we need to best address your question.
WRT your last question - steer clear of dynamic SQL! If you want to know
why, read http://www.sommarskog.se/dynamic_sql.html.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help - Contiguous Date Range Query

I have a table that contains (among other things) start and end range datetimes. The ranges can overlap. View these datetimes as segments of coverage on a number line. Rather than returning each line segment individually, I'd like the query to return the start and the end of the contiguous segments. For example, the desired output of the query on the below table would be:

/* Start SQL */

/* Desired output: range_start_datetime range_end_datetime
01/01/2003 01/06/2003
01/08/2003 01/12/2003
*/

create table #ranges (range_start_datetime datetime, range_end_datetime datetime)

/* Range 1: 01/01/2003 to 01/06/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/01/2003', '01/02/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/03/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/06/2003')

/* Gap - no data at 01/07/2003 */

/* Range 2: 01/08/2003 to 01/12/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/08/2003', '01/09/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/09/2003', '01/10/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/10/2003', '01/12/2003')

select * from #ranges
drop table #ranges

/* End SQL */This version will work in Oracle at least:

SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );

RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||This works in Sybase as well. I had to change the first select to distinct when I added a few more test cases, but it works! I'm impressed with your skills.

If you get a moment, could you explain what your query is doing? I didn't even consider a 4-join solution.

Gump

Originally posted by andrewst
This version will work in Oracle at least:

SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );

RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||Probably easiest to start from here:

select range_start_datetime,
from ranges r1
where not exists
( select range_end_datetime from ranges r2
where r2.range_end_datetime >= r1.range_start_datetime
and r2.range_start_datetime < r1.range_start_datetime
);

All I have done is remove the second column from the main select (which was a "scalar subquery").

This query gets the start of each contiguous range, by finding all the start dates for which there does not exist a preceding record that meets or overlaps it, i.e.:

RANGE_STAR
----
01/01/2003
01/08/2003

Now let's look at the scalar subquery:

select min(range_end_datetime)
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
and r3.range_end_datetime >= r1.range_start_datetime

This sort of does the opposite of the query above: it finds the end of each contiguous range, i.e. all the end dates for which there does not exist a following record that meets or overlaps it, i.e.

select range_end_datetime
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)

RANGE_END_
----
01/06/2003
01/12/2003

It then finds the MIN of those end dates where the end_date is on or after the start date of each record in the main query (i.e. corellated): for start date 01/01/2003 that will be 01/06/2003, and for start date 01/08/2003 that will be 01/12/2003.

Does that make sense?

HELP - Combining Rows in a View

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

HenryRemember, you asked! I'd use:CREATE TABLE tHenry (
grade INT
, name VARCHAR(20)
)

INSERT INTO tHenry (grade, name)
SELECT 8, 'John'
UNION ALL SELECT 8, 'Mike'
UNION ALL SELECT 8, 'Ed'
UNION ALL SELECT 9, 'Tom'
UNION ALL SELECT 9, 'Greg'
UNION ALL SELECT 10, 'Jack'
UNION ALL SELECT 10, 'Tony'
GO

CREATE FUNCTION dbo.fHenry(@.piGrade INT) RETURNS VARCHAR(200) AS
BEGIN
DECLARE @.cList VARCHAR(8000)

SELECT @.cList = Coalesce(@.cList + ', ' + name, name)
FROM tHenry
WHERE grade = @.piGrade

RETURN @.cList
END
GO

CREATE VIEW vHenry AS SELECT DISTINCT TOP 100 PERCENT
grade, dbo.fHenry(grade) AS students
FROM tHenry
ORDER BY grade
GO

SELECT * FROM vHenry
GO

DROP VIEW vHenry
DROP FUNCTION dbo.fHenry
DROP TABLE tHenry-PatP