mirroring

hello

i used following query to create endpoint

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

i got the following error msg:

Msg 1498, Level 16, State 2, Line 11

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.

any help plzz

thanks in advance

Hello

Support for Database mirroring in SQL Server 2005 as a feature was officially availble in SQL Server 2005 Service Pack 1. Before Service Pack 1 in RTM build you could still use this feature but after Enabling Trace Flag 1400 DBCC TRACEON(1400,-1). I would suggest upgrading to SQL Server 2005 SP1 or SP2.

Hope this helps

Vishal Gandhi

|||

thanks vishal

i downloaded sp2 i check its created the endpoints properly, let me check whether mirroring will work proper or not.

sql

Minute table?

I got no responses to the 'Complex query' thread, which probably
contained too much information. I'll try simplifying it:
I have 2 tables
create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
smalldatetime, MI_RequiredStaff tinyint)
this table contains the required staff between certain time.
MI_RequiredStaff is the column that contains the number of employees
required at that time.
create table PlannedWork (PW_ID int, PW_Date smalldatetime,
PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
this table contains times when an employee will work.
Now I'm looking for a query which can give me the Minima which aren't
fullfilled for a certain day. Of course if an employee is used to fill
a certain minima he/she can't be used for another one.
I have no clue on how to begin with this. But I was thinking maybe
it's possible to use something like a minute-table (like you have a
table with all the days). I don't know if that has ever been used and
if it's appropriate for this.
Thanks in advance,
Stijn Verrept.Stijn,
There is no 'easy' fix for this. I feel you need to identify the smallest
period of time to use as slots, unless you have already defined this in the
XX_BeginTime and XX_EndTime inasmuch that such times will always match for
any time of the day (e.g. a standard 3 shift system commencing at 0700 and
running a stright 8 hours each). Any such system will need to be the same fo
r
both the employee and required time slots.
Once identified, you can create a temp table of the required slots for a
given period of time (be it a single shift, day, w, etc) and then update
those records with the counts of staff matching that shift.
Example: I use a straight 3 shift system of 0700-1500, 1500-2300 and
2300-0700.
Day 1 requires 3,5,2 (respectively).
The Temp table will have Date, Start, End, Required, Planned columns.
The Date, Start, End, Required columns are populated immediately from the
Minima table, and then each row is updated with the count of Employtees
matching those times/dates from the PlannedWork table.
The final act is to calculate against the Required and Planned columns to
determine overages or shortfalls in the numbers.
The whole code would be contained in a Stored Procedure with the relevent
Params to determine any period of time.
This is a *very* basic and simplified version of what you may find to the a
solution, but you could find it beneficial to break the problem down into
steps as above to assist you.
Hope this assists,
Tony
"Stijn Verrept" wrote:

> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
>|||Stijn Verrept wrote:
> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
Hi Stijn,
No keys at all in your tables? Is every column really nullable or do
you expect us to guess? Surely you could do better...
Assuming you add some sensible keys and constraints and assuming you
don't allow PlannedWork for any employee to overlap (easily prevented
with a trigger), try something like the following query.
I don't know why you would put date and time in separate columns so
I've ignored PW_Date altogether.
SELECT mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
SUM(DATEDIFF(MINUTE,work_start,work_end)
) AS work_time
FROM
(SELECT M.mi_id, M.mi_begintime, M.mi_endtime, M.mi_requiredstaff,
mi_requiredstaff*DATEDIFF(MINUTE, mi_begintime, mi_endtime)
AS required_time,
CASE WHEN M.mi_begintime > P.pw_begintime
THEN M.mi_begintime ELSE P.pw_begintime END AS work_start,
CASE WHEN M.mi_endtime < P.pw_endtime
THEN M.mi_endtime ELSE P.pw_endtime END AS work_end
FROM Minima AS M
LEFT JOIN PlannedWork AS P
ON M.mi_begintime < P.pw_endtime
AND M.mi_endtime > P.pw_begintime) AS T
GROUP BY mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
required_time
HAVING SUM(DATEDIFF(MINUTE,work_start,work_end)
)< required_time ;
However, this won't prevent an employee being allocated to more than
one Minima. To do that I think you will have to pre-allocate employees
to each Minima on a best-fit basis. I don't think you'll be able to
find the best-fits using only declarative SQL because I'm pretty
certain this is an NP-complexity problem - I think there's even a
mathematical name for it.
I discussed a logically similar best-fit problem in the following
thread. My solution could help you get an arbitrary match between
Minima and PlannedWork but an arbitrary match I'd suggest isn't what
you are looking for. I guess you'll need to build a table of possible
assignments and then pull out the minimum cost ones. If you post some
sample data someone may be able to help further.
http://groups.google.co.uk/group/co...2c682dab331565c
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony Scott wrote:

> There is no 'easy' fix for this. I feel you need to identify the
> smallest period of time to use as slots, unless you have already
> defined this in the XX_BeginTime and XX_EndTime inasmuch that such
> times will always match for any time of the day (e.g. a standard 3
> shift system commencing at 0700 and running a stright 8 hours each).
> Any such system will need to be the same for both the employee and
> required time slots.
This could indeed simplify things (if the employee and required slots
are the same. I'm going to ask if this will be the case.

> This is a very basic and simplified version of what you may find to
> the a solution, but you could find it beneficial to break the problem
> down into steps as above to assist you.
True, thanks a lot!
Stijn Verrept.|||David Portas wrote:

> No keys at all in your tables? Is every column really nullable or do
> you expect us to guess? Surely you could do better...
Indeed I can, and I also did (see the 'complex query' thread) however I
got no replies there and now I get 2. Sometimes it's better to provide
less info ;). The tables are of course completely fictional, just to
give the general idea of the problem.

> However, this won't prevent an employee being allocated to more than
> one Minima.

> To do that I think you will have to pre-allocate employees
> to each Minima on a best-fit basis. I don't think you'll be able to
> find the best-fits using only declarative SQL because I'm pretty
> certain this is an NP-complexity problem - I think there's even a
> mathematical name for it.
Thank you very much, I'm going to look up that NP-complexity problem
(which sounds complex ;). And look into the thread you provided! The
possible assignments indeed seems a good idea, the benefit is I would
only need to do this for the Minima and the ServiceGratings which will
speed up the final query.
Thanks again,
Stijn Verrept.|||No problem at all.
Just as a sideline to this, if you should find that the 'slots' do not
match, then you may be forced to use 'compromise' or 'lowest denominator'
slots, those being the smallest unit of time that can fit within both sets o
f
data. Example would be that if the Employees and shifts always start on the
hour and work in hours, then the unit would be hours, and you would then nee
d
to use a single row per hour. If the employees or shifts could start on the
half-hour, then that would be the unit of measure.
I would be interested in your findings and final solution, as this is the
type of problem I deal with day-in and day-out
Tony
"Stijn Verrept" wrote:

> Tony Scott wrote:
>
> This could indeed simplify things (if the employee and required slots
> are the same. I'm going to ask if this will be the case.
>
> True, thanks a lot!
> --
> Stijn Verrept.
>

minute count query

I need a query that gives me the sum of every rows (time column) with lower 'rownr'

the result:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70

current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10

and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime

thx for all help

//MrDo you have the URL or a PDF for this assignment?

-PatP|||:( no im making a database to store my divelogs in and this is the accumulated time im trying to calculate...|||I have no idea if this is what you are looking for. Nevertheless, to try and guess what the solution to your problem might be I went ahead and created a test database called MiscTests.

Now, I created a table similar to yours but instead of having the time column as datetime I've changed it to an INT. Here's the table code:

USE [MiscTests]
GO
/****** Object: Table [dbo].[Times] Script Date: 08/22/2007 17:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Times](
[rownr] [int] IDENTITY(1,1) NOT NULL,
[time] [int] NOT NULL CONSTRAINT [DF_Times_time] DEFAULT ((0)),
CONSTRAINT [PK_Times] PRIMARY KEY CLUSTERED
(
[rownr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

With this table in place I've made a small scalar-valued function to parse the time column. The code of the SVF is as follows:

USE [MiscTests]
GO
/****** Object: UserDefinedFunction [dbo].[parseTime] Script Date: 08/22/2007 17:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[parseTime]
(
@.Time AS Int
)
RETURNS VarChar(10)
AS
BEGIN
DECLARE @.hours AS Int
, @.parsedTime AS VarChar(10);

SET @.hours = 0;

WHILE (@.Time >= 60)
BEGIN
SET @.hours = (@.hours + 1);

SET @.Time = (@.Time - 60);
END

IF (@.hours = 0)
BEGIN
SET @.parsedTime = '0';
END
ELSE
BEGIN
SET @.parsedTime = CAST(@.hours AS VarChar(7));
END

SET @.parsedTime = @.parsedTime + ':';

IF (@.Time < 10)
BEGIN
SET @.parsedTime = @.parsedTime + '0'
END

SET @.parsedTime = @.parsedTime + CAST(@.Time AS VarChar(2));

return @.parsedTime;
END
GO

With this done I went ahead and filled the dbo.Times table with a couple of datarows. After that I ran a simple query to return the desired output. Here's the query SQL

SELECT
t.rownr AS rownr
, t.time AS [time]
, dbo.parseTime((
SELECT
sum(st.time)
FROM
dbo.Times st
WHERE
st.rownr <= t.rownr
)) as timesum
FROM
dbo.Times t
ORDER BY
t.rownr ASC;

The resultset should be something like this:
1 0 0:00
2 10 0:10
3 10 0:20
4 10 0:30
5 10 0:40
6 10 0:50
7 10 1:00
8 10 1:10
9 10 1:20
10 10 1:30
11 10 1:40
12 10 1:50
13 10 2:00
14 10 2:10
15 10 2:20

Hope this helps ;)|||declare @.sample table (rownr int, time int)

insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10

select s1.rownr,
s1.time,
convert(varchar(5), dateadd(minute, sum(coalesce(s2.time, 0)), 0), 108) as timesum
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time|||Peso, your solution is insufficiently complex. How do you ever expect to make a living at this with such concise solutions?
Obfuscate! Obfuscate! Obfuscate!|||thanks alot for all the effort you guys put into this, but i can't solve this. Its my experience that fails here... i really tryed Diabolic's solutions which im sure works but again, i can't apply that solutiuons at my database...

is it possible to make a query that gives me the result i want? or do i need a temp table or similar to accomplish my goal?

//Mr|||It appears that outer theta joins are too elementary for blindman now... :rolleyes:

Not to mention the use of temporal functions which, according to Pat are rather risky, but he's under NDA so can't tell us why...|||MRPCGuy, Peso's method is the standard solution to this common problem, and should work fine.|||declare @.sample table (rownr int, time int)

insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 500 union all
select 5, 10 union all
select 6, 10 union all
select 7, 4000000 union all
select 8, 10

select s1.rownr,
s1.time,
convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time
order by s1.rownr,
s1.time|||would somebody -- and i volunteer mrpcguy for this -- please post the DDL to create a table using rownr=int, time=datetime as specified in post #1

it's fine and dandy to set up test cases using ints, but mrpcguy says he's using datetimes|||im learning, im leraning, alright? Thx Peso for your help...|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?with a query, similar to the ones you've seen in this thread

any chance you could give us some real data, not fake data? i.e. data with datetimes, not integers|||thing is that i dont have any real data yet, im building this database and gona fill it with divelogs later on. But this function to be able to count accumulated time is important. i only have fake data.
My idea was to have datetime at that columt because logg gonna look like "00:30" for 30min. Maybe its better to use int and put in "30" for 30min...
How can i achive what i wont with my first post and use int instead of datetime?|||For int you just have to log the number of mins (int), but that will turn innacurate soon enough. Since you're storing the number of minutes in integer you will soon realise that if your dive was 2.5mins you'll have to round it.

To store the time as an int it's prolly better to store the number of seconds.|||your correct about that, but since i always round it up to whole minutes i don't need seconds...|||you should be able to use the solutions presented here, namely peso's one, if you're going to use int.|||Ok,
I got this:
current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10

and I want this:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70

I run this:
declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

What if I got a table with 10000 rows… that’s many select statements

I’m very aware that it’s my skills is the biggest problem here, so plz help…|||no, it's still only one SELECT statement, no matter how many rows|||well in my test table i have 50 rows and the output from above gives me 8 rows?!|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)|||is this doable in ms access? certainly is|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)

of for the love of....|||...pancakes?|||well in my test table i have 50 rows and the output from above gives me 8 rows?!

You are getting 8 rows because you are using the wrong query. If I didn't missunderstand your post, you are using the following SQL to retrieve the desired results:

declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

Well, it's only natural that you are only getting 8 rows. If you look closely you'll see that you are selecting the results from the temporary table called @.sample. To retrieve your real data you should use this sql statement:

select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from <MyTableName> as s1
left join <MyTableName> as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

Please, remember to replace <MyTableName> with the actual name of your dive logs table.|||Please, remember to replace <MyTableName> with the actual name of your dive logs table.and you will also have to change this nonsense --convert(varchar, sum(coalesce(s2.time, 0)) / 60)
+ ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)into the equivalent ms access nonsense

:)|||thx alot it works fine... I'l take your query to another forum and get it translated into access when im have gone nuts trying to translate it myself...|||Good Lord.

It's only 12:08 PM and I am now exhausted.

and the thread is only two pages :)|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?My problems are numerous and well documented, but are primarily out of scope in the current discussion. One of those I would consider IN scope, however, is my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.

Sarcasm? Guilty as charged.
Humiliation? Nah, I've seen not one virtual wedgie applied here.

Lighten up. If you think this forum is bad, it's the first one you've been to.|||... my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.then you will just have to make another visit to http://icanhascheezburger.com/

it will make sense eventually

:)|||and the sarcasm and humiliation at this forum is extraordinary...
LOL.. Thats why I keep coming back!|||http://icanhascheezburger.com/

lol @. that site :beer:|||then you will just have to make another visit to http://icanhascheezburger.com/

it will make sense eventually

:)
I must admit, I only looked at the first couple pages then got busy with *coff* work *coff* and forwarded the link to myself at home. I will catch up with the rest of the class over the weekend.

Mining Query Builder

On pages 123, 124 of Data Mining with SQL Server 2005, I can't finish the example. When I add 'Monthly' to the end of the expression "[Home Owners].[Theater Freq]" in the Criteria/Argument column, I usually get an error message saying the syntax is incorrect when I switch to the Results view. If I place a dot after the original expression, like this: [Home Owners].[Theater Freq].Monthly, I get an error message that "The dot expression is not allowed in the context at line 4, column 23. Use sub-SELECT instead." What am I doing wrong?

Thanks!

Try formating your query as

SELECT t.CustomerID, [Movie Trees].[Theater Freq],
PredictProbability([Movie Trees].[Theater Freq], 'Monthly') as ProbMonthly FROM ...

This returns the probability for the state 'Monthly' of the [Movie Trees].[Theater Freq]

sql

minimum memory per query: how does it work?

Hi,
How does this "minimum memory per query" option work in SQL Server 2005?
Does it have any influence on performance? When it is put higher than the
standard value, does this mean that more memory will be reserved for each
query, and he will have a hard time to run multiple queries at the same
time?
thanks a lot and kind regards,
PieterYou can use sp_configure to set this a desired value. Check SQL Server Books
Online for the exact syntax.
For light weight databases, it may be a good option to try. However, if you
have a large database with long running transactions on massive datasets or
large number of concurrent connections, using min memory settings is not
recommended. In most cases, it is better to leave it to default since the
query processor usually can figure out the optimal memory allocation for
simple queries.
--
Anith

minimum and maximum in a time range (was "SQL query")

Hi all,
If I have the table:

Date/Time X
2/12/2004 07:23:00 100
2/12/2004 07:30:00 200
2/12/2004 09:30:00 500
2/12/2004 14:45:00 600
2/12/2004 15:02:00 100

I want to get X where it's time interval between the minimum time and the maximum time in the period 06:00:00 to 15:15:00
My target to get the minimum X and the maximum X between 06:00:00 and 15:15:00 and substarct them.

Please help
Thanksselect max(x)-min(x)
from some_table
where datetime between timestamp '2004-12-02 06:00:00' and timestamp '2004-12-02 15:15:00';

Of course, specification of dates and times varies from DBMS to DBMS, so the syntax may vary there.

Min/Maximum Grouping Query

I know this has been posted before, but I can't find the previous threads so please bear with me...

I want to grab the very 1st record of each product in a table like this

ID CLIENTID PRODID
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3

so that I'd get a record set like:

ID CLIENTID PRODID
1 a 1
4 a 2
7 a 3

Thanks for the hellp guru'sSELECT t1.ID, t1.CLIENTID, t1.PRODID
FROM table t1
INNER JOIN (
SELECT MIN(ID) AS ID, PRODID
FROM table
ORDER BY PRODID) t2 ON t1.ID = t2.ID
AND t1.PRODID = t2.PRODID|||You did mean GROUP BY, not ORDER BY, right?

SELECT t1.ID, t1.CLIENTID, t1.PRODID
FROM table t1
INNER JOIN (
SELECT MIN(ID) AS ID, PRODID
FROM table
GROUP BY PRODID) t2 ON t1.ID = t2.ID
AND t1.PRODID = t2.PRODID|||Yep. Been sniped. (grin)|||Thanks guys, exactly what I was after. :)

Min within a Group query

Hi
We have a table structure for storing away Hires in a SQL Server database.
Related to this table is another table that stores events/logs that have
occurred on the Hires table. I.e. Record Created, Modified, Price Changed,
etc. We are trying to do a query that will return the first log for each
hire and then return a few extra fields too. The basic table structure is
below
Hires
--
HireID
ClientID
Status
Cancelled
HireLog
--
HireLogID
HireID
LogDate
Comment
EventType
OperatorID
At first I thought we could do the following:
SELECT dbo.Hires.HireID, MIN(dbo.HireLog.HireLogID) AS HireLogID,
dbo.HireLog.LogDate, dbo.HireLog.Comment, dbo.HireLog.EventType,
dbo.HireLog.OperatorID
FROM dbo.Hires INNER JOIN dbo.HireLog ON dbo.Hires.HireID =
dbo.HireLog.HireID
GROUP BY dbo.Hires.HireID, dbo.HireLog.LogDate, dbo.HireLog.Comment,
dbo.HireLog.EventType, dbo.HireLog.OperatorID
ORDER BY dbo.Hires.HireID DESC
This works great with just the Hire ID field and the Min(HireLogID), but as
soon as you add the other fields the Group By causes the query to return all
the other Logs for the Hire too.
Is there any way around it?What is HireLogID? If that's an IDENTITY column then it's probably
unwise to rely on it to determine the earliest row. The reason is that
you don't always have full control over the order in which IDENTITY
values are assigned. IDENTITY should be used only as an arbitrary
surrogate key without any ascribed business meaning.
In this case it looks like you'll wanr to use LogDate to determine the
first row for each Hire. Declare (hireid, logdate) as unique to ensure
you have a unique sequence.
SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
FROM HireLog AS L
WHERE logdate =
(SELECT MIN(logdate)
FROM HireLog
WHERE hireid =L.hireid)
David Portas
SQL Server MVP
--|||Chris,
Just one minor, (probably unnecessary) addition..
If you are storing Date and TIme in LogDate, then the chance of anyone
recording two records in HireLog with the same HireID and LogDate is very
unlikely, and probabl;y impossible, ignore this, David's solution should wor
k
fine...
but if your application logic is only storing the date, without the time
portion, in logDate, then you will need to handle the case where are multipl
e
records with the same value for both HireID and LogDate.
The only way to do that, given your schema, is to use the HireLogID as a
discriminant. (David's comment about no guarantees as to which is REALLY
earliest apply here, but, if you don;t have the time portion of the date
stored, then there's no way to distinquish among multiple records on a
specific day anyway.)
Select hirelogid, hireid, logdate,
comment, eventtype, operatorid
From HireLog L
Where hirelogid =
(Select Min(hirelogid)
From HireLog
Where hireid = L.hireid
And logdate = (Select Min(LogDate)
From HireLog
Where hireid = L.hireid))
"David Portas" wrote:

> What is HireLogID? If that's an IDENTITY column then it's probably
> unwise to rely on it to determine the earliest row. The reason is that
> you don't always have full control over the order in which IDENTITY
> values are assigned. IDENTITY should be used only as an arbitrary
> surrogate key without any ascribed business meaning.
> In this case it looks like you'll wanr to use LogDate to determine the
> first row for each Hire. Declare (hireid, logdate) as unique to ensure
> you have a unique sequence.
> SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
> FROM HireLog AS L
> WHERE logdate =
> (SELECT MIN(logdate)
> FROM HireLog
> WHERE hireid =L.hireid)
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for you help.
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110365299.387914.40640@.g14g2000cwa.googlegroups.com...
> What is HireLogID? If that's an IDENTITY column then it's probably
> unwise to rely on it to determine the earliest row. The reason is that
> you don't always have full control over the order in which IDENTITY
> values are assigned. IDENTITY should be used only as an arbitrary
> surrogate key without any ascribed business meaning.
> In this case it looks like you'll wanr to use LogDate to determine the
> first row for each Hire. Declare (hireid, logdate) as unique to ensure
> you have a unique sequence.
> SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
> FROM HireLog AS L
> WHERE logdate =
> (SELECT MIN(logdate)
> FROM HireLog
> WHERE hireid =L.hireid)
> --
> David Portas
> SQL Server MVP
> --
>|||> but, if you don;t have the time portion of the date
> stored, then there's no way to distinquish among multiple records on
a
> specific day anyway
... and therefore the business requirement to display only the earliest
row would be fatally flawed, and anyway, what would be the natural key
of the table in that scenario? That is indeed the price you pay for
tables without proper keys.
David Portas
SQL Server MVP
--|||This is a date + time field.
Thanks.
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110367501.066963.91030@.z14g2000cwz.googlegroups.com...
> a
> ... and therefore the business requirement to display only the earliest
> row would be fatally flawed, and anyway, what would be the natural key
> of the table in that scenario? That is indeed the price you pay for
> tables without proper keys.
> --
> David Portas
> SQL Server MVP
> --
>

min value for a date

Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
100268231002008-02-08 00:00:00.000
100268241502008-02-08 00:00:00.000
100268241502008-02-09 00:00:00.000
100268231002008-02-10 00:00:00.000
100268231002008-02-11 00:00:00.000
100268231002008-02-13 00:00:00.000
100268231002008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?
Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
|||Hi
it seems that rate_id is not needed in the group by
selectmin(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group byA.date
order byA.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
selectmin(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group byA.date
) b ON b.date = c.date AND b.minrate = s.rate
order byb.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:

> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147-- Legacy falcon
> and R.room_type_id = 72-- Double room
> and R.board_type = 6-- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
> @.year
> and r.min_nights = 1-- Standard type
> and a.date >= getDate()-- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 100268231002008-02-08 00:00:00.000
> 100268241502008-02-08 00:00:00.000
> 100268241502008-02-09 00:00:00.000
> 100268231002008-02-10 00:00:00.000
> 100268231002008-02-11 00:00:00.000
> 100268231002008-02-13 00:00:00.000
> 100268231002008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>
|||On Feb 11, 2:41Xpm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15Xpm, Rippo <i...@.rippo.co.uk> wrote:
>
>
>
>
>
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> X X X X r.Rate_id, min(r.rate) MinRate, A.date
> from
> X X X X hotel_acc_rates R
> inner join
> X X X X hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and R.room_type_id = 72 X X X X -- Double room
> X X X X and R.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(R.valid_from_date) <= @.month and
> year(R.valid_from_date) <=
> @.year
> X X X X and month(R.valid_to_date) >= @.month and year(R.valid_to_date)>=
> @.year
> X X X X and r.min_nights = 1 X X X X X X-- Standard type
> X X X X and a.date >= getDate() X X X X X X X-- wedont go back in
> time
> group by
> X X X X r.Rate_id, A.date
> having min(r.rate) =
> (select min(s.rate)
> from
> X X X X hotel_acc_rates S
> inner join
> X X X X hotel_acc_rate_avail B on S.rate_id = B.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and S.room_type_id = 72 X X X X -- Double room
> X X X X and S.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(S.valid_from_date) <= @.month and
> year(S.valid_from_date) <=
> @.year
> X X X X and month(S.valid_to_date) >= @.month and year(S.valid_to_date)>=
> @.year
> X X X X and s.min_nights = 1 X X X X X X-- Standard type
> X X X X and b.date >= getDate() X X X X X X X-- wedont go back in
> time
> X X X X and R.Rate_id = S.Rate_id
> X X X X and A.date = B.date
> group by
> X X X X s.Rate_id, B.date
> )
> order by
> X X X X A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
...
and A.date = B.date
group by
B.date
)
HTH

min value for a date

Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <= @.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >= @.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
10026823 100 2008-02-08 00:00:00.000
10026824 150 2008-02-08 00:00:00.000
10026824 150 2008-02-09 00:00:00.000
10026823 100 2008-02-10 00:00:00.000
10026823 100 2008-02-11 00:00:00.000
10026823 100 2008-02-13 00:00:00.000
10026823 100 2008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?|||Hi
it seems that rate_id is not needed in the group by
select min(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
order by A.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
select min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
) b ON b.date = c.date AND b.minrate = s.rate
order by b.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>|||On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
> Hi,
> I have the following query which returns
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.valid_=from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date) >=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> order by
> =A0 =A0 =A0 =A0 A.date, minRate
> rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
Hi,
I have re-written your query a bit. You might try this although
untested.
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and R.room_type_id =3D 72 -- Double room
and R.board_type =3D 6 -- Bed and breakfast
and month(R.valid_from_date) <=3D @.month and
year(R.valid_from_date) <=3D
@.year
and month(R.valid_to_date) >=3D @.month and year(R.valid_to_date)
>=3D
@.year
and r.min_nights =3D 1 -- Standard type
and a.date >=3D getDate() -- we dont go back in
time
group by
r.Rate_id, A.date
having min(r.rate) =3D
(select min(s.rate)
from
hotel_acc_rates S
inner join
hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and S.room_type_id =3D 72 -- Double room
and S.board_type =3D 6 -- Bed and breakfast
and month(S.valid_from_date) <=3D @.month and
year(S.valid_from_date) <=3D
@.year
and month(S.valid_to_date) >=3D @.month and year(S.valid_to_date)
>=3D
@.year
and s.min_nights =3D 1 -- Standard type
and b.date >=3D getDate() -- we dont go back in
time
and R.Rate_id =3D S.Rate_id
and A.date =3D B.date
group by
s.Rate_id, B.date
)
order by
A.date, minRate|||On Feb 11, 2:41=A0pm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
>
>
> > Hi,
> > I have the following query which returns
> > select
> > =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> > from
> > =A0 =A0 =A0 =A0 hotel_acc_rates R
> > inner join
> > =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> > where
> > =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> > =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room=
> > =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and= breakfast
> > =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.vali=d_from_date) <=3D
> > @.year
> > =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_=to_date) >=3D
> > @.year
> > =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standar=d type
> > =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- =we dont go back in time
> > group by
> > =A0 =A0 =A0 =A0 r.Rate_id, A.date
> > order by
> > =A0 =A0 =A0 =A0 A.date, minRate
> > rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> > However I want to exclude row 2 as it is the same day but a higher
> > rate. I am working on an extremley old legacy system which allows lots
> > of different rates for the same day to entered which does not help!.
> > The structure cannot be changed so I am stuck with what I have.
> > How can I rewrite my query without creating a temp table?
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and
> year(R.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> having min(r.rate) =3D
> (select min(s.rate)
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates S
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and S.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and S.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(S.valid_from_date) <=3D @.month and
> year(S.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(S.valid_to_date) >=3D @.month and year(S.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and s.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and b.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> =A0 =A0 =A0 =A0 and R.Rate_id =3D S.Rate_id
> =A0 =A0 =A0 =A0 and A.date =3D B.date
> group by
> =A0 =A0 =A0 =A0 s.Rate_id, B.date
> )
> order by
> =A0 =A0 =A0 =A0 A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
=2E...
and A.date =3D B.date
group by
B.date
)
HTH

Min not working

Hi all:
Because me not this functioning the following one query.

simply me not the function respects min, because?, that I am doing badly?

Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime

Thanks.:confused:the problem is because you have c.Link4 in the GROUP BY but not in the SELECT

also, please note: DISTINCT is not a function

try this --select Min(c.gentime) as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
group
by a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
order
by a.CardNumber
, c.recvtime|||thanks by answering, the problem is, that the inner join does not respect me the rank dated, I need a value by each employee, by each I gave of the rank.

Thanks.:confused:|||okay, try this --select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
and c.gentime =
( select min(c.gentime)
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10 )
order
by a.CardNumber
, c.recvtime|||thanks but the following error marks me:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

:eek:|||i'm going to move this query to the SQL Server forum

i don't understand the error message, maybe someone else does|||Not completely sure what is going on here, but trying moving the subquery to the JOIN clause:select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
inner
join --subquery
(select link3,
min(c.gentime) as gentime
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10
group by link3) subquery
on subquery.link3 = b.recordid
and subquery.gentime = c.gentime
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
order
by a.CardNumber
, c.recvtime

min memory per query

I have a VB.NET application which reads thousands of records from a MSDE
Database, processes them, and writes them against a MYSQL Database. I'm
using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
test the application on my lap top (512MB RAM; MSDE Memory limited to 260MB)
I don't get any errors. When I test the same application on another lap top
(2GB Ram; no limits) I get the error message, that there isn't enough memory
for the querz and I should reduce "min memory per query", which is already
at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
Server with 2GB Ram the application runs fine, but the MSDE reserves about
1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
the error message.
On every computer there is installed: Win XP Pro (or Win XP Server for the
server) SP1, MSDE 2000 (same version on each computer), same MySQL
DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
installed. I'm working with VS 2002, which is only installed on my lap top.
I didn't get the errors when writing to the MySQL database with ODBC, but I
can't use ODBC because of the MySQL Database version I have to access.
For processing the first third of the records the memory which is used by
the MSDE is about 250MB on each computer. After that the memory usage on my
lap top and on the desktop pc stays at a low level, but on the other lap top
and on the server the memory usage goes through the roof.
I don't have any glue what the problem can be, it seems that the release of
the memory is handled differently on those machines. Can someone give me a
hint where to look for, what to do, or how to recreate the problem on the
other machines? I can't even look for the problem if I can't recreate the
error on my development machine (my lap top).
Any tipps are welcome!
Thanks
Peter
Hi Peter
I don't think the error is the fact that the min value is too high but the
fact that there is not enough memory to complete the query with what's
there.
See
http://msdn.microsoft.com/library/de...rr_2_65pt.asp.
If you look at the query plan, it could be that your stats are out of date,
or that you are missing indexes or unecessary/excessive hashing/sorting is
occuring
http://msdn.microsoft.com/library/de...onfig_68q6.asp
John
"Peter Zentner" <peter@._REM_zentner-online.de> wrote in message
news:%23DvjjHCxEHA.2632@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET application which reads thousands of records from a MSDE
> Database, processes them, and writes them against a MYSQL Database. I'm
> using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
> test the application on my lap top (512MB RAM; MSDE Memory limited to
> 260MB)
> I don't get any errors. When I test the same application on another lap
> top
> (2GB Ram; no limits) I get the error message, that there isn't enough
> memory
> for the querz and I should reduce "min memory per query", which is already
> at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
> Server with 2GB Ram the application runs fine, but the MSDE reserves about
> 1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
> the error message.
> On every computer there is installed: Win XP Pro (or Win XP Server for the
> server) SP1, MSDE 2000 (same version on each computer), same MySQL
> DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
> installed. I'm working with VS 2002, which is only installed on my lap
> top.
> I didn't get the errors when writing to the MySQL database with ODBC, but
> I
> can't use ODBC because of the MySQL Database version I have to access.
> For processing the first third of the records the memory which is used by
> the MSDE is about 250MB on each computer. After that the memory usage on
> my
> lap top and on the desktop pc stays at a low level, but on the other lap
> top
> and on the server the memory usage goes through the roof.
> I don't have any glue what the problem can be, it seems that the release
> of
> the memory is handled differently on those machines. Can someone give me a
> hint where to look for, what to do, or how to recreate the problem on the
> other machines? I can't even look for the problem if I can't recreate the
> error on my development machine (my lap top).
> Any tipps are welcome!
> Thanks
> Peter
>

Min except for zero

We have a complex query with many aggregate functions.
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:

>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim

MIN and MAX strange results

I want to get the MIN and MAX value of a table column from a specified period of time. I execute a query and it return the result. The problem is that the values returned by MIN and MAX are not always correct!!

This is the result table

Date Statement From To

1 2007-01-03 00:00:00 Invoice 1 2 Correct
2 2007-01-04 00:00:00 Receipt 1 1 Correct
3 2007-01-04 00:00:00 Invoice 10 9 Wrong
4 2007-01-05 00:00:00 Receipt 2 5 Correct
5 2007-01-05 00:00:00 Invoice 100 99 Wrong
6 2007-01-08 00:00:00 Invoice 124 175 Correct
7 2007-01-09 00:00:00 Invoice 176 224 Correct
8 2007-01-10 00:00:00 Invoice 225 265 Correct

From =From Statement Number

To= To statement Number

The odd behavior happens when the number of digits changes. If the range of the column is 1 digit ie from 0 to 9 the values reported are ok. If the digits change then there is a problem as in line 3 and 5.

Any ideas why this odd behavior happens?

rectis:

I think you need to provide (1) the SQL Statement that is not working correctly and the definition of the table (or at least the relevant columns). My knee-jerk guess would be that you are coming to grief because your "From" and "To" fields are defined as varchar instead of numeric (or integer).

If in fact your "from" and "to" fields are defined as varchar you first need to make a determination to the usage of these fields -- that is see if the definition needs to be modified such that columns are reformatted into numeric (or integer) columns. You may need to compute your max as MIN(CONVERT(INTEGER, FROM)) and MAX(CONVERT(INTEGER,TO))

|||You are right. Thank you very much. I think my brain was stopped.The field was defined as varchar. Now the values are ok!

milliseconds in query analyzer

I'm attempting to optomize my stored procedures by attempting different join
orders and indexes. I'd like to know if there is a way to see milliseconds i
n
the query analyezr? (or elsewhere)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesTry using the profiler to run a trace, and it will give you milleseconds.
Archer
"WebBuilder451" wrote:

> I'm attempting to optomize my stored procedures by attempting different jo
in
> orders and indexes. I'd like to know if there is a way to see milliseconds
in
> the query analyezr? (or elsewhere)
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||I see milliseconds just fine:
SELECT GETDATE()
-- code here
WAITFOR DELAY '00:00:01.6'
SELECT GETDATE()
Maybe you could be more specific?
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:C062B7BA-C54E-480C-9B2B-C2FA80F4CBD0@.microsoft.com...
> I'm attempting to optomize my stored procedures by attempting different
> join
> orders and indexes. I'd like to know if there is a way to see milliseconds
> in
> the query analyezr? (or elsewhere)
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||where are you seeing the miliseconds? I see only seconds at the bottom. I'll
try to be more specific. Using the tool that ships with mssqlserver called
"SQL Query Analyzer" I create a query and run it. This produces out put and
this process takes a messureable amount of time. This time currently only
displays in seconds. I would like to know if it's possible to see this
displayed in milliseconds? I can create an artifical timer that will produce
a time difference, however, i would perfer not to introduce unnecessary
elements in queries to be analyized.
thnaks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Aaron Bertrand [SQL Server MVP]" wrote:

> I see milliseconds just fine:
> SELECT GETDATE()
> -- code here
> WAITFOR DELAY '00:00:01.6'
> SELECT GETDATE()
> Maybe you could be more specific?
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:C062B7BA-C54E-480C-9B2B-C2FA80F4CBD0@.microsoft.com...
>
>|||> where are you seeing the miliseconds? I see only seconds at the bottom.
> I'll
> try to be more specific. Using the tool that ships with mssqlserver called
> "SQL Query Analyzer" I create a query and run it. This produces out put
> and
> this process takes a messureable amount of time. This time currently only
> displays in seconds.
Yes, I know what Query Analyzer is. Is there a way you can take a screen
shot, post it somewhere, and send the URL?|||On Mon, 12 Sep 2005 10:40:05 -0700, "WebBuilder451"
<WebBuilder451@.discussions.microsoft.com> wrote:
>I'm attempting to optomize my stored procedures by attempting different joi
n
>orders and indexes. I'd like to know if there is a way to see milliseconds
in
>the query analyezr? (or elsewhere)
Run it ten times?
J.|||thanks,
that's an answer.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"bagman3rd" wrote:
> Try using the profiler to run a trace, and it will give you milleseconds.
> Archer
> "WebBuilder451" wrote:
>|||I guess then the answer is "No there is no setting in the program that
changes the display"
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"jxstern" wrote:

> On Mon, 12 Sep 2005 10:40:05 -0700, "WebBuilder451"
> <WebBuilder451@.discussions.microsoft.com> wrote:
> Run it ten times?
> J.
>|||I guess then the answer is "No there is no setting in the program that
changes the display"
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, I know what Query Analyzer is. Is there a way you can take a screen
> shot, post it somewhere, and send the URL?
>
>|||>I guess then the answer is "No there is no setting in the program that
> changes the display"
No, but if I can understand how exactly you're USING Query Analyzer in such
a way that SELECT GETDATE() does not yield milliseconds, I might be able to
answer more constructively.

mildly complex query. need senior dba advise.

Hi there,
I have this stored procedure I am trying to optimize:
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
CREATE TABLE #ResultsOutput (
rowID int not null identity(1,1),
Rank int,
MemberID int,
MemberCode varchar(50),
State varchar(5),
MemberName varchar(510),
StateID int,
TotalScore int,
TotalEvents int,
PRIMARY KEY (TotalScore, MemberID, rowID),
UNIQUE(TotalScore, MemberID, rowID)
)
CREATE TABLE #TotalScoreCount (
rowID int not null identity(1,1),
TotalScore int,
TotalScoreCount int
PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
UNIQUE (TotalScoreCount, TotalScore, rowID)
)
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' + Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID = Results.MemberID
INNER JOIN Event ON Results.EventID = Event.EventID
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
INNER JOIN State ON Region.StateID = State.StateID
WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
@.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID
ORDER BY TotalScore DESC, Results.MemberID ASC
INSERT INTO #TotalScoreCount
SELECT TotalScore,
COUNT(TotalScore) AS TotalScoreCount
FROM #ResultsOutput
GROUP BY TotalScore
ORDER BY TotalScore DESC
UPDATE #ResultsOutput
SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
#TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
FROM #ResultsOutput RO
SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
DROP table #ResultsOutput
DROP table #TotalScoreCount
END
I did a SQL Execution plan and saw no table scans on my selects/updates
(Only on my inserts ofcourse)
The query still runs poorly it takes up to 7 seconds to run!
Members has 44621 rows
Results has 121158
Venue has 336
Event has 3298
Region has 58
I am quite new and poor at SQL so any help would be appreciated.
Thanks!to be perfectly honest... its not 'that' bad already.
You could try using table variable (sql2k +) instead of temp table and also
remove the order by on the insert as you are ordering the select after
anyway.
<robert@.relate.com.au> wrote in message
news:1147350229.650320.155550@.g10g2000cwb.googlegroups.com...
> Hi there,
> I have this stored procedure I am trying to optimize:
>
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
>
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = Results.MemberID
> INNER JOIN Event ON Results.EventID = Event.EventID
> INNER JOIN Venue ON Event.VenueID = Venue.VenueID
> INNER JOIN Region ON Venue.RegionID = Region.RegionID
> INNER JOIN State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
>
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
>
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
>
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!
>|||Well, 7 seconds is hardly poor.
Anyway, make sure you have the following indexes:
- on Members(MemberID)
- on Results(EventID, MemberID, Valid)
- on Results(MemberID, EventID, Valid)
- on Event(EventID, VenueID, EventType)
- on Event(VenueID, EventID, EventType)
- on Venue(VenueID, RegionID)
- on Venue(RegionID, VenueID)
- on Region(RegionID, StateID)
- on Region(StateID, RegionID)
- on State(StateID)
and then try the SP below. Of course, you can remove the indexes that
you created above if they are not used in the query below.
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ResultsOutput
(MemberID int
,MemberCode varchar(50)
,State varchar(5)
,MemberName varchar(510)
,StateID int
,TotalScore int
,TotalEvents int
)
INSERT INTO #ResultsOutput (MemberID, MemberCode, State, MemberName,
StateID, TotalScore, TotalEvents)
SELECT Results.MemberID
, MAX(Members.MemberCode)
, MAX(Members.State)
, MAX(Members.FirstName + ' ' + Members.Surname)
, MAX(State.StateID)
, SUM(Results.Score)
, COUNT(Results.ResultsID)
FROM Members
INNER JOIN Results ON Results.MemberID = Members.MemberID
INNER JOIN Event ON Event.EventID = Results.EventID
INNER JOIN Venue ON Venue.VenueID = Event.VenueID
INNER JOIN Region ON Region.RegionID = Venue.RegionID
INNER JOIN State ON State.StateID = Region.StateID
WHERE Results.Valid = 1
AND Event.EventType = 0
AND State.StateID = @.StateID
GROUP BY Results.MemberID
Declare @.SQL varchar(1000)
Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
as varchar(12))+' ON #ResultsOutput(TotalScore)'
EXEC (@.SQL)
SELECT
ISNULL((
SELECT COUNT(*)
FROM #ResultsOutput R2
WHERE R2.TotalScore > R1.TotalScore
),0)+1 AS Rank
,MemberID
,MemberCode
,State
,MemberName
,StateID
,TotalScore
,TotalEvents
FROM #ResultsOutput R1
ORDER BY TotalScore DESC, MemberID ASC
DROP TABLE #ResultsOutput
END
Hope this helps,
Gert-Jan
"robert@.relate.com.au" wrote:
> Hi there,
> I have this stored procedure I am trying to optimize:
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = R
esults.MemberID
> INNER JOIN Event ON Results.EventI
D = Event.EventID
> INNER JOIN Venue ON Event.
VenueID = Venue.VenueID
> INNER JOIN Region
ON Venue.RegionID = Region.RegionID
> INNER JOIN
State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0
AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FRO
M
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID AS
C
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!|||Try moving the filters from the where clause because they get applied
after building the set of rows from the JOIN statements. Also, the
JOIN to States doesn't appear to be needed because the StateID can be
filltered directly from Results.
This may be faster:
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' +
Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID =
Results.MemberID AND Results.Valid = 1
INNER JOIN Event ON Results.EventID = Event.EventID AND
Event.EventType = 0
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
AND Region.StateID = @.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID|||On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:

> Declare @.SQL varchar(1000)
> Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
>as varchar(12))+' ON #ResultsOutput(TotalScore)'
> EXEC (@.SQL)
Hi Gert-Jan,
Why are you addind the spid to the index name here? I have always been
under the assumption that you only have to prefix indexes on temp tables
with the # symbol, just like you do with temp tables.
In fact, I just tested this. I executed the code below from two
different connections in SSMS and didn't get an error:
create table #test (a int)
go
create nonclustered index #ind on #test(a)
go
Hugo Kornelis, SQL Server MVP|||Thanks for the tip. It was about time that I learnt something new :-)
Gert-Jan
Hugo Kornelis wrote:
> On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:
>
> Hi Gert-Jan,
> Why are you addind the spid to the index name here? I have always been
> under the assumption that you only have to prefix indexes on temp tables
> with the # symbol, just like you do with temp tables.
> In fact, I just tested this. I executed the code below from two
> different connections in SSMS and didn't get an error:
> create table #test (a int)
> go
> create nonclustered index #ind on #test(a)
> go
> --
> Hugo Kornelis, SQL Server MVP|||Thanks for everyone's help everyone. Immy, i originally was using table
variables but was gettign table scans because of lack of indexing.
I guess i'm going to have to setup a scheduled task to run a stored
proc that will run every half an hour to do that 1 query and select it
all into a table with every field in one row and already sorted, that
way when my website application goes to run it only has to do a simple
select.
Thanks again all for confirming nothings majorly wrong.