Migrating to SQL 2005: min

Showing posts with label min. Show all posts

Monday, March 19, 2012

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/max of x minutes

I am trying to develop a sql statement that will create a recordset of the min (or max) values in x minute increments over a period of time.

e.g. over a period of 7 days, I have data that was collected in 1 minute intervals. I need to know the min (or max) value in each 10 minute interval over that same period of time.

Is there an efficient way of doing this?

Try something like this:

DECLARE @.dt_from DATETIME, @.dt_to DATETIME;
SET @.dt_from = '2006-03-21T00:00:00.000';
SET @.dt_to = '2006-03-28T00:00:00.000';

SELECT MIN(dt) AS dt,
MIN(val) AS min_val, MAX(val) AS max_val
FROM tbl
WHERE dt >= @.dt_from
AND dt < @.dt_to
GROUP BY FLOOR(DATEDIFF(MINUTE,@.dt_from,dt)/10) ;

--
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
--

|||Just recently had a chance to try this out. It worked perfectly. Thanks!

MIN/MAX issue

/*
I have posted DDL below for the issue i have. When you look at the output
from marc1, I would like to retrieve the minimum and maximum vehicle_id's
based on Vh_make_id
so as my output would look something like:
min max
-- --
3040501 3131887
3040443 3049312
How can I do this?
*/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[marc1]
GO
CREATE TABLE [dbo].[marc1] (
[Vehicle_id] [int] NULL ,
[Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_mfg_yr_id] [smallint] NULL ,
[Vh_mfg_yr_band_id] [tinyint] NULL ,
[Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_mfg_age_id] [tinyint] NULL ,
[Vh_group_id] [smallint] NULL ,
[Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_registration_id] [int] NULL ,
[Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_make_id] [smallint] NULL ,
[Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into marc1 values(3040501, '- QMVOG 02 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3131887, '- QMVOG 03 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3040443, '- QMVOG 01 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
insert into marc1 values(3049312, '- QMVOG 02 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
select * from marc1See if this helps
select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
group by Vh_make_desc
order by [max] desc
BTW, your table has no primary key nor designed well in terms of
normalization.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||sorry im having a sleepy day
select min(vehicle_id), max(vehicle_id), Vh_make_id from marc1 group by
Vh_make_id|||SELECT MIN(Vehicle_Id), MAX(Vehicle_Id)
FROM marc1
GROUP BY Vh_make_id
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||You mean
select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
group by Vh_make_desc
order by [max] desc
--
Regards
R.D
--Knowledge gets doubled when shared
"Uri Dimant" wrote:

> See if this helps
> select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
> group by Vh_make_desc
> order by [max] desc
> BTW, your table has no primary key nor designed well in terms of
> normalization.
>
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
>
>|||Hi Marc,
try this:
Select Vh_make_id, MIN(Vehicle_id) as minvalue, MAX(Vehicle_id) as
maxvalue
>From marc1
GROUP BY Vh_make_id
HTH, jens Suessmeyer.|||Yep, sorry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:42FF4DAE-810D-47B7-908B-CE2EC6D70B85@.microsoft.com...
> You mean
> select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
> group by Vh_make_desc
> order by [max] desc
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Uri Dimant" wrote:
>

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
Maury
Maury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury
|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury
|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2...lesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
MauryMaury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury|||"Tracy McKibben" wrote:
> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>> Look up COALESCE in Books Online...
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury|||Maury Markowitz wrote:
> INTP56" wrote:
>> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
>> ISNULL(salesq4,0)
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because it
s
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null
":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
MauryMaury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||COALESE will only return the first non-null value. Think of it as a shorthan
d
way of doing a CASE statement that checks each value in order for a NULL, an
d
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to rol
l
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand f
or
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/...realsqlguy.com|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

Min(NULL) shows up as 12/30/1899 in cube

Hi!

I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.

I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.

Any help would be greatly appreciated. Thanks!

Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.

In a relational DB 1 + NULL = NULL

In an OLAP DB 1 + NULL = 1

You might have to change your approach, so possible avenues to explore would be

use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||

So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:

iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.

I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.

|||

That would work too.

You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.

|||

I've now created a calculated member as:

IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])

and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).

Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?

Thanks for all your help!

|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.

min(date) + 1

I know you can use the min function to get the first date within a
recordset. However, is there a way to get the second date in a
recordset? In other words, is there a way to get "min(date) + 1" or a
"next-to-earliest date" essentially?Try somehthing like...

select min(a.date) from (select date from table b where b.date > (select
min(c.date) from table c)) a

This should get the next earliest date not necessarily the next min date if
there are duplicate min dates.

<imani_technology_spam@.yahoo.com> wrote in message
news:1127338921.903049.218810@.z14g2000cwz.googlegr oups.com...
>I know you can use the min function to get the first date within a
> recordset. However, is there a way to get the second date in a
> recordset? In other words, is there a way to get "min(date) + 1" or a
> "next-to-earliest date" essentially?|||select top 1 a.date from
(select top 2 date from table order by date ) a
order by a.date desc|||On Wed, 21 Sep 2005 23:21:52 GMT, Danny wrote:

>Try somehthing like...
>select min(a.date) from (select date from table b where b.date > (select
>min(c.date) from table c)) a
>This should get the next earliest date not necessarily the next min date if
>there are duplicate min dates.

Hi Danny,

No need to use a derived table, though. This'll work as well:

SELECT MIN(a.date)
FROM table AS a
WHERE a.date > (SELECT MIN(b.date) FROM b.table))

Best, Hugo
--

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

Min(), MAX() Question

Hi,
I have two tables : Table1, Table2
CREATE TABLE [dbo].[Table1] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SaleDate] [datetime] NULL ,
[Price] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportDate] [datetime] NULL ,
[ReportPrice] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-- And here is some data
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',10.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',9.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',9.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 1 2005
12:00:00:000AM',6.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',7.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Oct 10 2005
12:00:00:000AM',30.00,'001')
INSERT INTO [Table2]
([Product],[ReportDate],[ReportPrice],[C
ustomer])VALUES('A','May 1 2005
12:00:00:000AM',0,'001')
I need a query to update ReportPrice From Table2 with the Maximum Saledate
from table1 less then ReportDate from Table2 and the price=Min for that
date for that specific date.
This is my query
UPDATE t2 SET t2.ReportPrice=t1.Price FROM
(
Select Max(SaleDate) as SaleDate,Min(Price) AS PRICE ,Customer,Product From
Table1
GROUP BY Customer,Product) as t1,Table2 as t2
WHERE t1.SaleDate< t2.ReportDate and t1.Product = t2.Product and
t1.Customer=t2.Customer
Select * FROM Table1
Select * FROM Table2
, but is wrong .. I get 6 instead of 7 . I need 7 to be the ReportPrice.
Thanks guys !Do:
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer ) )
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
With a t-SQL TOP clause in the subquery, it should be a bit more simpler
though:
UPDATE table2
SET ReportPrice = (
SELECT TOP 1 Price FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
ORDER BY table1.SaleDate DESC, Price )
WHERE ...
Anith|||Is not working !
The ideea is good but i need ReportPrice to be 7. In this case will be 30 .
I need somewhere a condition MAX( SaleDate ) <ReportDate
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>|||Thanks!
Is working now !
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer and
t1.SaleDate<Table2.ReportDate )
)
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
Select * FROM Table1
Select * FROM Table2
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
Eval
Pleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval
|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId = '1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId => '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

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 with a bit

Hi,

I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.

Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.

Is there an alternative to my query?

Many thanks,
Chrismin(cast(deleted as int))

> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?

> min(cast(deleted as int))

Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...

Cheers,
Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
> Cheers,
> Chris

Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).

Simon|||How a bit could be (-1) ?

"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buocfi$ub9$1@.ucsnew1.ncl.ac.uk...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...

How about .... min(cast(bit as varchar(1))) ?

Pete Brown
Falls Creek
Oz|||When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).

HTH,
Gert-Jan

Not Me wrote:
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1) in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Igor Raytsin" <n&i@.cyberus.ca> wrote in message
news:400fee3f_1@.news.cybersurf.net...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > I'm trying to grab records with a priority over those marked as yes (-1)
> in
> > a certain field.
> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?
> How a bit could be (-1) ?

Ask Bill :o)

Chris|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0401220728.58b967ae@.posting.google.c om...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> > "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> > news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > > operator doesn't work on bits.
> > > > > > Is there an alternative to my query?
> > > min(cast(deleted as int))
> > Thanks for that, I do though get an error when trying it, I guess it's
> > because I'm using an mdb file and linked tables to the sql server... any
> > other ideas? Could create a quick function I guess...
>
> Your question isn't really clear - a bit column can only hold 0,1 or
> NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
> an MSSQL query, then please consider posting the CREATE TABLE
> statement for your table, as well as the exact query that you're
> using, and the output you expect (sample data would also be useful).

Thanks for your help, yes the -1 just seems to be how access likes to
display the info.

The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.

So, a table could show

id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games

and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)

So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).

So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..

Hope you understand the problem!!
Any help is greatly appreciated.

Cheers,
Chris|||Not Me (Not.Me@.faker.fake.fa.ke) writes:
> So, a table could show
> id current job
> #1 yes databases
> #1 no graphics
> #2 no statistics
> #2 no games
> and I would want to return one record for each id#, with a preference of a
> current job (if no current job, any non-current job will do)
> So far I've only managed to do a "select all current jobs union select
> all non-current jobs that don't appear in the current jobs list" The
> problem here is that it becomes very very slow when performing the "jobs
> that don't appear in the current jobs list" (done by where x not in
> (select x from y)).

Here is one way that you may want to try:

DECLARE @.temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)

INSERT @.temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC

SELECT t.id, c.current, t.job
FROM @.temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @.temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id

By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.

I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

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 value

CurrentStage resides in the BUSLOCATION table and UserStage resides in the USERLOCATION table.

I need to find out the MIN difference between these two columns

i.e UserStage-CurrentStage=SHOULD BE THE MINIMUM VALUE.

Userstage is just one value but then it shud be subtracted from all the CurrentStage values in the table and the CurrentStage value which gives me the least difference should be extracted out.

Hope i didn't confuse u all too much :P anyone any ideas?so something like

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage = min(t1.UserStage - t2.CurrentStage )

yeah???|||you might need to go to...

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage =
(select min(t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is )

not sure though....|||Do u think my code would work?

SELECT CurrentStage, PlateNbr from tblBusLocation a, tblUserLocation b
Where a.CurrentStage < b.UserStageNbr AND a.CurrentStage= (SELECT MIN(b.UserStageNbr-a.CurrentStage))|||Sorry here is an updated code. Is there a problem with the MIN function?

SELECT t1.CurrentStage, t1.PlateNbr

From tblBusLocation t1, tblUserLocation t2

Where t1.CurrentStage < t2.UserStageNbr AND

t1.CurrentStage =

(SELECT t1.CurrentStage from tblBusLocation t1, tblUserLocation t2

where MIN (t2.UserStageNbr - t1.CurrentStage)
)|||SELECT t1.CurrentStage, t1.PlateNbr
, MIN (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1
, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
group
by t1.CurrentStage, t1.PlateNbr

rudy|||this might be way off but...

select top 1 (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
Order by (t2.UserStageNbr - t1.CurrentStage)

???

min server memory option and memory paging

I have problem with sql memery usage.

In same case sql paging the data to virtual memory on disk and relese physical memory. And if SQL relese physsical memory, he dont consider min server memory (MB) option(4012MB), after relesingsqlserv physical memory usage is only 500MB. And after sqlserv restart his memory usage is only about 500MB.

Perfmon counters:

Pages/sec1450

%Commited Byte in Use 43%

%UsageTotalPage File87%

Available MBytesMemory6480 MB

My server is:9.00.2153.00SP1Standard Edition (64-bit)

Server memory 8GB

sp_configure:

awe enabled0100

min server memory (MB)0214748364740124012

max server memory (MB)16214748364745004500

I have Enabled the Lock Pages in Memory Option( http://msdn2.microsoft.com/en-us/library/ms190730.aspx )

Can you please provide:

1.The output of "dbcc memorystatus" when the server reaches the described state

2. The 1st 100 lines of the errorlog

Thanks, Ron D.

|||

Hello

1. My results of dbcc memorystatus:

https://www.just.ee/fail/dl/26%2E01%2E2007%5F16%5F37%5F57%5F19017%5Flogi%2Etxt

2. And sql server log:

01/26/2007 15:28:15,spid1s,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) unloaded.
01/26/2007 15:28:14,spid1s,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) is marked for unload due to memory pressure.
01/26/2007 15:17:58,spid6s,Unknown,SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000870. The offset of the latest long I/O is: 0x00000002961000
01/26/2007 15:17:58,spid6s,Unknown,SQL Server has encountered 49 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00001219ee8000
01/26/2007 14:46:23,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf] in database [mssqlsystemresource] (32767). The OS file handle is 0x0000000000000684. The offset of the latest long I/O is: 0x00000001242000
01/26/2007 14:45:12,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000870. The offset of the latest long I/O is: 0x00000002162000
01/26/2007 14:45:12,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x000000000000086C. The offset of the latest long I/O is: 0x00000000482000
01/26/2007 14:45:12,spid6s,Unknown,SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00001c7cd7e000
01/26/2007 14:45:12,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf] in database [tempdb] (2). The OS file handle is 0x0000000000000860. The offset of the latest long I/O is: 0x00000000360000
01/26/2007 14:29:11,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000870. The offset of the latest long I/O is: 0x00000000dc1000
01/26/2007 14:29:01,spid6s,Unknown,SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00000acb1ea000
01/26/2007 14:22:45,spid25s,Unknown,Changing the status to FLUSH for full-text catalog "cat_isikud" (7) in database "kraamat" (5). This is an informational message only. No user action is required.
01/26/2007 14:22:43,spid69,Unknown,Replication-Replication Snapshot Subsystem: agent SQL50\KRIS-kraamat-kraamat50-1 scheduled for retry. The replication agent had encountered an exception.<nl/>Source: Replication<nl/>Exception Type: Microsoft.SqlServer.Replication.SqlDeadlockException<nl/>Exception Message: Transaction (Process ID 71) was deadlocked on lock resources with another process and has be
01/26/2007 14:10:47,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000870. The offset of the latest long I/O is: 0x00000000889000
01/26/2007 14:10:37,spid6s,Unknown,SQL Server has encountered 16 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00000c30650000
01/26/2007 14:00:51,spid6s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000870. The offset of the latest long I/O is: 0x00000000607000
01/26/2007 14:00:41,spid6s,Unknown,SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00000ba0d1c000
01/26/2007 13:54:20,spid72,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) created.
01/26/2007 13:54:18,spid72,Unknown,Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\.

|||Check this KBA is marked for unload due to memory pressureagot get a fix for the referred problem.|||

1. Are you sure these are the 1st 100 lines? The typical errorlog start looks like this:

Jan 20 2007 23:42:11 2005.090.9091.00
Copyright (c) 1988-2006 Microsoft Corporation
Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 1)

2007-01-23 22:52:23.92 Server (c) 2005 Microsoft Corporation.
2007-01-23 22:52:23.92 Server All rights reserved.
2007-01-23 22:52:23.92 Server Server process ID is 5156.
2007-01-23 22:52:23.92 Server Authentication mode is MIXED.
2007-01-23 22:52:23.92 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-01-23 22:52:23.98 Server This instance of SQL Server last reported using a process ID of 4452 at 1/18/2007 12:29:37 PM (local) 1/18/2007 8:29:37 PM (UTC). This is an informational message only; no user action is required.
2007-01-23 22:52:23.98 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-01-23 22:52:23.98 Server Command Line Startup Parameters:
-c
2007-01-23 22:52:24.13 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2. From the dbcc output, it seems that the server is not using locked pages. (AWE allocated is 0). In that case, the server will be subject to paging, and the OS does not respect the min server memory settings, because it doesn't know about them.

Is this a 32-bit or 64-bit server?

Thanks, Ron D.

|||

It is 64bit OS and SQL. According to http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx i dont need switch AWE on to use locked pages.

Here is log again:

01/30/2007 03:02:10,spid68,Unknown,AppDomain 4 (kraamat.dbo[runtime].3) created.
01/30/2007 02:46:00,spid1s,Unknown,AppDomain 3 (kraamat.dbo[runtime].2) unloaded.
01/30/2007 02:46:00,spid1s,Unknown,AppDomain 3 (kraamat.dbo[runtime].2) is marked for unload due to memory pressure.
01/30/2007 00:00:49,spid18s,Unknown,This instance of SQL Server has been using a process ID of 5044 since 29.01.2007 12:38:41 (local) 29.01.2007 10:38:41 (UTC). This is an informational message only; no user action is required.
01/29/2007 18:40:10,spid68,Unknown,AppDomain 3 (kraamat.dbo[runtime].2) created.
01/29/2007 18:31:53,spid1s,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) unloaded.
01/29/2007 18:31:53,spid1s,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) is marked for unload due to memory pressure.
01/29/2007 17:10:47,spid5s,Unknown,SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000904. The offset of the latest long I/O is: 0x00000001222000
01/29/2007 17:10:37,spid5s,Unknown,SQL Server has encountered 72 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x00000000000008D8. The offset of the latest long I/O is: 0x00001e9a434000
01/29/2007 12:48:40,spid5s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf] in database [mssqlsystemresource] (32767). The OS file handle is 0x0000000000000684. The offset of the latest long I/O is: 0x000000013c8000
01/29/2007 12:45:28,spid5s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\distribution153152.MDF] in database [distribution153152] (10). The OS file handle is 0x00000000000008E8. The offset of the latest long I/O is: 0x0000000281c000
01/29/2007 12:44:26,spid5s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf] in database [msdb] (4). The OS file handle is 0x00000000000008D4. The offset of the latest long I/O is: 0x000000001c0000
01/29/2007 12:44:06,spid5s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x0000000000000904. The offset of the latest long I/O is: 0x00000002245000
01/29/2007 12:44:06,spid5s,Unknown,SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [S:\sqldata\kraamat1901_data.mdf] in database [kraamat] (5). The OS file handle is 0x00000000000008D8. The offset of the latest long I/O is: 0x000011affe0000
01/29/2007 12:43:12,spid80,Unknown,AppDomain 2 (kraamat.dbo[runtime].1) created.
01/29/2007 12:43:11,spid80,Unknown,Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\.
01/29/2007 12:39:59,spid66,Unknown,Configuration option 'min server memory (MB)' changed from 2012 to 4012. Run the RECONFIGURE statement to install.
01/29/2007 12:38:59,spid53,Unknown,Using 'xplog70.dll' version '2005.90.2047' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
01/29/2007 12:38:59,spid53,Unknown,Using 'xpsqlbot.dll' version '2005.90.2047' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
01/29/2007 12:38:59,spid53,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
01/29/2007 12:38:41,spid23s,Unknown,Changing the status to FLUSH for full-text catalog "cat_isikud" (7) in database "kraamat" (5). This is an informational message only. No user action is required.
01/29/2007 12:38:41,spid7s,Unknown,Launched startup procedure 'sp_MSrepl_startup'.
01/29/2007 12:38:41,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
01/29/2007 12:38:41,spid7s,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
01/29/2007 12:38:38,spid21s,Unknown,Recovery is writing a checkpoint in database 'kraamat' (5). This is an informational message only. No user action is required.
01/29/2007 12:38:38,spid21s,Unknown,Analysis of database 'kraamat' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
01/29/2007 12:38:34,spid26s,Unknown,Starting up database 'distribution153152'.
01/29/2007 12:38:34,spid24s,Unknown,Starting up database 'white'.
01/29/2007 12:38:34,spid20s,Unknown,Starting up database 'msdb'.
01/29/2007 12:38:34,spid21s,Unknown,Starting up database 'kraamat'.
01/29/2007 12:38:34,spid25s,Unknown,Starting up database 'distribution'.
01/29/2007 12:38:34,spid23s,Unknown,Starting up database 'pink'.
01/29/2007 12:38:34,spid22s,Unknown,Starting up database 'ASPState'.
01/29/2007 12:38:34,spid52,Unknown,Using 'xpstar90.dll' version '2005.90.2047' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
01/29/2007 12:38:33,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
01/29/2007 12:38:33,Server,Unknown,The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
01/29/2007 12:38:33,spid14s,Unknown,Service Broker manager has started.
01/29/2007 12:38:33,spid14s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
01/29/2007 12:38:33,spid14s,Unknown,The Service Broker protocol transport is disabled or not configured.
01/29/2007 12:38:33,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\SQL50\MSSQL$kris\sql\query ].
01/29/2007 12:38:33,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\KRIS ].
01/29/2007 12:38:33,Server,Unknown,Server is listening on [ 10.1.6.185 <ipv4> 2188].
01/29/2007 12:38:33,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
01/29/2007 12:38:33,spid11s,Unknown,Starting up database 'tempdb'.
01/29/2007 12:38:33,spid11s,Unknown,Clearing tempdb database.
01/29/2007 12:38:33,spid11s,Unknown,Starting up database 'model'.
01/29/2007 12:38:33,spid7s,Unknown,The NETBIOS name of the local node that is running the server is 'SQLNODE7'. This is an informational message only. No user action is required.
01/29/2007 12:38:33,spid7s,Unknown,Server name is 'SQL50\KRIS'. This is an informational message only. No user action is required.
01/29/2007 12:38:32,spid7s,Unknown,The resource database build version is 9.00.2153. This is an informational message only. No user action is required.
01/29/2007 12:38:32,spid7s,Unknown,Starting up database 'mssqlsystemresource'.
01/29/2007 12:38:32,spid7s,Unknown,SQL Trace ID 1 was started by login "sa".
01/29/2007 12:38:32,spid7s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
01/29/2007 12:38:31,spid7s,Unknown,Starting up database 'master'.
01/29/2007 12:38:31,Server,Unknown,Database mirroring has been enabled on this instance of SQL Server.
01/29/2007 12:38:31,Server,Unknown,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,Multinode configuration: node 1: CPU mask: 0x0000000000000003 Active CPU mask: 0x0000000000000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,Multinode configuration: node 0: CPU mask: 0x000000000000000c Active CPU mask: 0x000000000000000c. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,Detected 4 CPUs. This is an informational message; no user action is required.
01/29/2007 12:38:28,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
01/29/2007 12:38:28,Server,Unknown,-l S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
01/29/2007 12:38:28,Server,Unknown,-e S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
01/29/2007 12:38:28,Server,Unknown,-d S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
01/29/2007 12:38:28,Server,Unknown,Registry startup parameters:
01/29/2007 12:38:28,Server,Unknown,This instance of SQL Server last reported using a process ID of 3984 at 29.01.2007 12:38:20 (local) 29.01.2007 10:38:20 (UTC). This is an informational message only; no user action is required.
01/29/2007 12:38:28,Server,Unknown,Logging SQL Server messages in file 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
01/29/2007 12:38:28,Server,Unknown,Server process ID is 5044.
01/29/2007 12:38:28,Server,Unknown,All rights reserved.
01/29/2007 12:38:28,Server,Unknown,(c) 2005 Microsoft Corporation.
01/29/2007 12:38:28,Server,Unknown,Microsoft SQL Server 2005 - 9.00.2153.00 (X64) <nl/> May 9 2006 13:58:37 <nl/> Copyright (c) 1988-2005 Microsoft Corporation<nl/> Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

Tiitl

|||

Here are memory monitoring pictures: https://www.just.ee/fail/dl/30%2E01%2E2007%5F13%5F02%5F27%5F20736%5Fmemory%2Edoc

Server have enough free memory, but sqlserv paging the data to virtual memory and after relesingsqlserv physical memory usage is only 500MB.

|||

You are correct that 64-bit SQL Server ignores the AWE setting. What you need to do (for a start), is give the "Lock Pages in Memory" right to the SQL Server Service account. This will keep the OS from paging and let SQL Server control it.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

Then, you should see whether you are having problems with single use, ad-hoc queries bloating your procedure cache, and causing internal memory pressure. See the link below for more detail.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!366.entry

Monday, March 19, 2012

Min/Maximum Grouping Query

min/max of x minutes

MIN/MAX issue

MIN, MAX and "non-zero" functions?

MIN, MAX and "non-zero" functions?

MIN, MAX and "non-zero" functions?

Min(NULL) shows up as 12/30/1899 in cube

min(date) + 1

Min(), MAX() Question

MIN() + MAX() Deadlock ?

MIN() + MAX() Deadlock ?

MIN() + MAX() Deadlock ?

Min within a Group query

min with a bit

min value for a date

min value for a date

Min value

min server memory option and memory paging

Migrating to SQL 2005

Blog Archive

About Me