Do you include weekends and national holidays in your calculation, or just working days?

Changing the != operator to > in the LEFT JOINs gives another 10-15% performance improvement.

CREATE TABLE #PR100 (Prime smallint primary key);

INSERT INTO #PR100

SELECT T.N

FROM Tally T

LEFT...

Barry,

Yep, your improved set-based query is 55% faster than my WHILE loop for primes < 10000 on my machine.

--Andrew

Sorry, I really should pay more attention.

This modification uses a correlated subquery to get the 2 latest rows for each MemberId.

It is untested so there could be more stupid mistakes.

SELECT...

GSquared (4/14/2009)

---

Of course, the real way to get this kind of thing from SQL is to use sets. Like this:

set nocount on;

create table #Numbers (

Number smallint identity primary key);

go

insert...

Instead of the ROW_NUMBER() function you can use the TOP 2 clause in a derived table to get the 2 most recent rows.

SELECT MemberId, AVG(LabValue)

FROM (

SELECT TOP...

Will you have to do this query as part of your normal business processes or is it a one-off?

If the former, you could create a computed column in each of...

I don't understand why you are using the nvarchar data type when doing these datetime calculations. What's wrong with the datetime data type?

Florian Reischl (4/10/2009)

---

maxine (4/10/2009)

---

Flo this also worked great and in fact I did say I would want all records returned if no parameters were selected but in reality this would...

You could try this WHERE clause, but Adi's comment about potentially non-optimal query plans is stil relevant here, so you should test it for acceptable performance against a large data...

What do you want to do if more than one pair of date range parameters are not null?

@LoggedDateFrom NOT NULL

@LoggedDateTo NOT NULL

@PaidDateFrom NOT NULL

@PaidDateTo NOT NULL

@OutcomeDateFrom NULL

@OutcomeDateTo NULL

What do you...

I think this will work.

SELECT

c.value(N'ChargedUserID[1]', N'nvarchar(100)') AS ChargedUserID,

c.value(N'ChargedUserType[1]', N'nvarchar(100)') AS ChargedUserType,

c.value(N'SubscriberName[1]', N'nvarchar(100)') AS SubscriberName,

c.value(N'TrunkID[1]', N'int')...

Please give the structure of the table into which you wish to import this XML data, preferably in the form of a CREATE TABLE statement.

I did it semi-automatically using a regular expression based find-and-replace function in a text editior called TextPad.

There are TSQL scripts available that you can use to automatically generate insert statements...

I would make a couple of minor changes to Mark's query:

1) Use COUNT(a.fscore) rather than COUNT(*) when calculating cn in order to avoid counting rows with NULL fscore values.

...