Minimum/Maximum of two values

Hallo Cracks,

what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.

What I try to do:

I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):

Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)

like this sqlstatement:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)

instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.

thanks in advance and kind regards :-)
Cappuhave a look at MAX, MIN, and DATEDIFF functions in SQL Server Books Online|||thanks, but Max and Min exists only as aggregate functions.

I have a solution now with CASE, it works but it looks terrible ;-)

here is it:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) AND
dbo.Phases.phasenabschlussist >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) THEN DATEDIFF(day,
CASE WHEN dbo.Phases.phasenstart >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN dbo.Phases.phasenstart ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) END,
CASE WHEN dbo.Phases.phasenabschlussist <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime)
THEN dbo.Phases.phasenabschlussist ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) END)
+ 1 ELSE 0 END AS TageISTJahr0
FROM dbo.Phases´

it works, but do i understand in two years what i did there? ;-)

Minimum Hardware Requirements For SQL Client

What would be the minumum hardware (and software) requirements to have
SQL client on 10 of our contractors' computer with a maximum database
size of 5GB?
Thank you very much
mike
The client machines can be pretty wimpy because the server process the T-SQL
and just returns the results the clients.
At a minimum I would want a PIII running Win2k with 256MB RAM.
Keith
"Mike" <mj1sql@.yahoo.com> wrote in message
news:9a55bd2d.0410070814.5589c64e@.posting.google.c om...
> What would be the minumum hardware (and software) requirements to have
> SQL client on 10 of our contractors' computer with a maximum database
> size of 5GB?
> Thank you very much
> mike

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/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 and Max Threshold for Column Chart

Hi there,
My project requires me to plot a column chart having minimum and maximum threshold values. I'm not too sure how to implement in the reporting service under the chart option.
I've tried another method where I plot a column chart and then I drew two lines on top of the column chart. When using RDL preview tab, I'm able to see the two lines. However once I deployed to the Reporting Server, the two lines are blocked by the Column Chart. I've tried to bring the two line front and send the column chart back but it is unsuccessful in viewing the lines.
Can anyone help? I'm fine with any method provided.
Thanks In Advnce...:)Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
thresholds in Scale: Minimum and Maximum input textboxes.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> Hi there,
> My project requires me to plot a column chart having minimum and maximum
threshold values. I'm not too sure how to implement in the reporting
service under the chart option.
> I've tried another method where I plot a column chart and then I drew two
lines on top of the column chart. When using RDL preview tab, I'm able to
see the two lines. However once I deployed to the Reporting Server, the two
lines are blocked by the Column Chart. I've tried to bring the two line
front and send the column chart back but it is unsuccessful in viewing the
lines.
> Can anyone help? I'm fine with any method provided.
> Thanks In Advnce...:)|||Hi Ravi,
For the column chart, I need to see the whole range value with sub min and max threshold values in the chart. For example :
Y-axis - Total number of hours an employee worked
The single column bar is made up of : Area 1, Area 2 and Area 3
=> to see how much time each employee spend in the particular Area per day
In my case, Area 1 and Area 2 are consider employee's work area while Area 3 is consider non-work area. Thus by specifying a minimum threshold and maximum threshold, a supervisor is able to get an overview on the employee working performance. Below is a simple diagram, hope you can understand my requirement. Thanks for helping
(hrs)
18| | A3 |
--max threshold
| | A2 |
| | A1 |
-- min threshold
| | A1 |
0 -->days
Mon
where A1 : Area 1
A2 : Area 2
A3 : Area 3
"Ravi Mumulla (Microsoft)" wrote:
> Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
> thresholds in Scale: Minimum and Maximum input textboxes.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Samie" <Samie@.discussions.microsoft.com> wrote in message
> news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> > Hi there,
> >
> > My project requires me to plot a column chart having minimum and maximum
> threshold values. I'm not too sure how to implement in the reporting
> service under the chart option.
> >
> > I've tried another method where I plot a column chart and then I drew two
> lines on top of the column chart. When using RDL preview tab, I'm able to
> see the two lines. However once I deployed to the Reporting Server, the two
> lines are blocked by the Column Chart. I've tried to bring the two line
> front and send the column chart back but it is unsuccessful in viewing the
> lines.
> >
> > Can anyone help? I'm fine with any method provided.
> > Thanks In Advnce...:)
>
>|||You might want to check out the attached sample report below. In particular
look at the "Target" series of the chart which simulates a threshold in a
column chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>Blue</BackgroundColor>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>Yellow</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>Green</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=100000</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>10pt</Default>
</BorderWidth>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<Format>MM/yyyy</Format>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Column</Type>
<Title>
<Caption>Sales / Cost / Target</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)*100+Month(Fields!OrderDate.Va
lue)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!OrderDate.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Target</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

Min and Max for Dimension attribute

Hi all,

I want to get the minimum and maximum among the members of a dimension attribute. This attribute is a date. Can anybody give me a sample MDX query for this..

Regards...

Girija Shankar Beuria

Code Snippet

with member [Measures].[Min Date] as

MIN([Date].[Date].[Date].Members,[Date].[Date].CurrentMember.MemberValue)

member [Measures].[Max Date] as

MAX([Date].[Date].[Date].Members,[Date].[Date].CurrentMember.MemberValue)

select

{[Measures].[Min Date],[Measures].[Max Date]} on 0

from [Adventure Works]

;

Min & Max Memory in 2005

Is there a best practice for setting the Minimum and Maximum Memory settings
in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 2000
we set the minimum and maximum memory to be the same value on our large
clustered SQL Server machines. This seemed to work very well. Is the same
true for 2005?
My experience so far has been you should at least set the max server
memory if you are beyond 2GB range, regardless of x86 or x64.
Note...if you are running x64 and have more than once instance, you
will also should to set it for ALL instances if you have more than one
on the box (or cluster in an active/active situation)...otherwise, the
most greedy instance wins out, creating memory pressure on the OS as
well...or so it goes from my experience. SQL will give up memory when
it recieves the out memory messages from the OS, but in the few times
I tested it during our initial deployment, the memory starvation of
one instance created massive slow downs in performance.
On Jun 15, 9:44 am, "Cgal" <cgalle...@.newsgroups.nospam> wrote:
> Is there a best practice for setting the Minimum and Maximum Memory settings
> in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 2000
> we set the minimum and maximum memory to be the same value on our large
> clustered SQL Server machines. This seemed to work very well. Is the same
> true for 2005?
|||Setting the min and max to the same basically fixes your memory at that
point and doesn't leave room for the OS to share if needed. In general leave
the min at 0 (default) and set the MAX to a value to always leave some for
the OS. Most systems will work fine with MAX at the default if you are not
using AWE but there may be times when you simply want to leave x amount of
memory for the OS and any other apps running on the server. If you are using
AWE and 32 bit you must set it to some value less than MAX because it is not
dynamic and will starve the OS.How much you leave depends on how much you
have and what you are doing.
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:%23AqYlN1rHHA.4768@.TK2MSFTNGP02.phx.gbl...
> Is there a best practice for setting the Minimum and Maximum Memory
> settings in SQL Server 2005 on a dedicated SQL Server machine. With SQL
> Server 2000 we set the minimum and maximum memory to be the same value on
> our large clustered SQL Server machines. This seemed to work very well.
> Is the same true for 2005?
>

Min & Max Memory in 2005

Is there a best practice for setting the Minimum and Maximum Memory settings
in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 2000
we set the minimum and maximum memory to be the same value on our large
clustered SQL Server machines. This seemed to work very well. Is the same
true for 2005?My experience so far has been you should at least set the max server
memory if you are beyond 2GB range, regardless of x86 or x64.
Note...if you are running x64 and have more than once instance, you
will also should to set it for ALL instances if you have more than one
on the box (or cluster in an active/active situation)...otherwise, the
most greedy instance wins out, creating memory pressure on the OS as
well...or so it goes from my experience. SQL will give up memory when
it recieves the out memory messages from the OS, but in the few times
I tested it during our initial deployment, the memory starvation of
one instance created massive slow downs in performance.
On Jun 15, 9:44 am, "Cgal" <cgalle...@.newsgroups.nospam> wrote:
> Is there a best practice for setting the Minimum and Maximum Memory settings
> in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 2000
> we set the minimum and maximum memory to be the same value on our large
> clustered SQL Server machines. This seemed to work very well. Is the same
> true for 2005?|||Setting the min and max to the same basically fixes your memory at that
point and doesn't leave room for the OS to share if needed. In general leave
the min at 0 (default) and set the MAX to a value to always leave some for
the OS. Most systems will work fine with MAX at the default if you are not
using AWE but there may be times when you simply want to leave x amount of
memory for the OS and any other apps running on the server. If you are using
AWE and 32 bit you must set it to some value less than MAX because it is not
dynamic and will starve the OS.How much you leave depends on how much you
have and what you are doing.
--
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:%23AqYlN1rHHA.4768@.TK2MSFTNGP02.phx.gbl...
> Is there a best practice for setting the Minimum and Maximum Memory
> settings in SQL Server 2005 on a dedicated SQL Server machine. With SQL
> Server 2000 we set the minimum and maximum memory to be the same value on
> our large clustered SQL Server machines. This seemed to work very well.
> Is the same true for 2005?
>

Min & Max Memory in 2005

Is there a best practice for setting the Minimum and Maximum Memory settings
in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 2000
we set the minimum and maximum memory to be the same value on our large
clustered SQL Server machines. This seemed to work very well. Is the same
true for 2005?My experience so far has been you should at least set the max server
memory if you are beyond 2GB range, regardless of x86 or x64.
Note...if you are running x64 and have more than once instance, you
will also should to set it for ALL instances if you have more than one
on the box (or cluster in an active/active situation)...otherwise, the
most greedy instance wins out, creating memory pressure on the OS as
well...or so it goes from my experience. SQL will give up memory when
it recieves the out memory messages from the OS, but in the few times
I tested it during our initial deployment, the memory starvation of
one instance created massive slow downs in performance.
On Jun 15, 9:44 am, "Cgal" <cgalle...@.newsgroups.nospam> wrote:
> Is there a best practice for setting the Minimum and Maximum Memory settin
gs
> in SQL Server 2005 on a dedicated SQL Server machine. With SQL Server 200
0
> we set the minimum and maximum memory to be the same value on our large
> clustered SQL Server machines. This seemed to work very well. Is the sam
e
> true for 2005?|||Setting the min and max to the same basically fixes your memory at that
point and doesn't leave room for the OS to share if needed. In general leave
the min at 0 (default) and set the MAX to a value to always leave some for
the OS. Most systems will work fine with MAX at the default if you are not
using AWE but there may be times when you simply want to leave x amount of
memory for the OS and any other apps running on the server. If you are using
AWE and 32 bit you must set it to some value less than MAX because it is not
dynamic and will starve the OS.How much you leave depends on how much you
have and what you are doing.
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:%23AqYlN1rHHA.4768@.TK2MSFTNGP02.phx.gbl...
> Is there a best practice for setting the Minimum and Maximum Memory
> settings in SQL Server 2005 on a dedicated SQL Server machine. With SQL
> Server 2000 we set the minimum and maximum memory to be the same value on
> our large clustered SQL Server machines. This seemed to work very well.
> Is the same true for 2005?
>