Mining XML from file

Right now, when i want to load contents of XLM file, i have to use openXML which looks this way:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc
select *
from openxml (@.idoc, '/root/tag',3 )
( and here is whole select procedure)
insert into (table name)
from openxml (@.idoc, '/root/tag,3 )
(and here it is again)

This is quite "useless" for daily using, unless someone fills the text field with new data.

Is it possible to load content directly from XML file located somewhere on the drive?Is there any other way to open XML file an load its contents into table?|||

What "text field" are you talking about?

Have you looked at the SQL Server Integration Services?

|||Whole content is loaded as text. It has "something" to do with this part:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc

If i wanted an visual studio solution, which i already have, i would put it in visual studio forum.

But still integration package is another file, artificially created. I dont need those.|||You could use bulk load:
1. All data to one field
insert into your_table
select * from openrowset( BULK 'c:\yourfile.txt' SINGLE_BLOB)
2. Split data to few rows
insert into your_table

select * from openrowset( BULK 'c:\yourfile.txt' FORMATFILE='C:\bcpformat.xml')

More info: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Also you could load data into xml column and use xml.nodes function insted of openxml for split xml-data to rows/filed|||

I have the same issue as errpop although i am running SQL 2005 std 1 CPU SP1 on Win 2000 server SP4 (both fully patched clean installs). The errors i am getting in the sql server logs are below and occur immediately after the sql server service starts and the number of cpu's are detected and written to the sql logs.

Error: 8313, Severity: 16, State: 1.

Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.

Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

I have followed the instructions in KB300956 and nothing changed. I noticed executing lodctr sqlctr.ini runs but i dont get any messages on screen. It is like it is not working. The reason i am so interested in the performance counters is i am trying to troubleshoot a local HP SureStore DLT1 tape drive (14 hours to backup 70GB) and i need the SQL backup device counter. I dont know if this is related or not?

Any help is greatly appreciated as this is entirely new territory to me.

Mining XML from file

Right now, when i want to load contents of XLM file, i have to use openXML which looks this way:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc
select *
from openxml (@.idoc, '/root/tag',3 )
( and here is whole select procedure)
insert into (table name)
from openxml (@.idoc, '/root/tag,3 )
(and here it is again)

This is quite "useless" for daily using, unless someone fills the text field with new data.

Is it possible to load content directly from XML file located somewhere on the drive?Is there any other way to open XML file an load its contents into table?|||

What "text field" are you talking about?

Have you looked at the SQL Server Integration Services?

|||Whole content is loaded as text. It has "something" to do with this part:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc

If i wanted an visual studio solution, which i already have, i would put it in visual studio forum.

But still integration package is another file, artificially created. I dont need those.|||You could use bulk load:
1. All data to one field
insert into your_table
select * from openrowset( BULK 'c:\yourfile.txt' SINGLE_BLOB)
2. Split data to few rows
insert into your_table

select * from openrowset( BULK 'c:\yourfile.txt' FORMATFILE='C:\bcpformat.xml')

More info: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Also you could load data into xml column and use xml.nodes function insted of openxml for split xml-data to rows/filed|||

I have the same issue as errpop although i am running SQL 2005 std 1 CPU SP1 on Win 2000 server SP4 (both fully patched clean installs). The errors i am getting in the sql server logs are below and occur immediately after the sql server service starts and the number of cpu's are detected and written to the sql logs.

Error: 8313, Severity: 16, State: 1.

Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.

Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

I have followed the instructions in KB300956 and nothing changed. I noticed executing lodctr sqlctr.ini runs but i dont get any messages on screen. It is like it is not working. The reason i am so interested in the performance counters is i am trying to troubleshoot a local HP SureStore DLT1 tape drive (14 hours to backup 70GB) and i need the SQL backup device counter. I dont know if this is related or not?

Any help is greatly appreciated as this is entirely new territory to me.

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(), 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 or max over multiple columns

Hi all,
A short question about min and max functions. Let's say I have the following
table:
contract sales_2005 sales_2006
1234 100 120
5678 870 560
Now, I want to build a query that gives the following result:
contract maximum_sales
1234 120
5678 870
So I want to get the maximum yearly sales per contract over the years 2005
and 2006. A normal max-function won't work because it only calculates the
maximum row in a given column. Is there a function that does what I'm lookin
g
for? Also to get the minimum yearly sales per contract.
Of course, it can be solved with a greater than/less than construction but
I'm looking for a more efficient solution.
Thanks in advance!
regards,
MaartenWhy is your data not properly normalized?
You can normalize it for the purpose of the aggregation by using a UNION
query (untested, since you haven't provided proper DDL):
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
...then use this as a derived table and apply the aggregation:
select max(Normalized.Sales) as MaxSales
,min(Normalized.Sales) as MinSales
from (
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
) Normalized
Is this what you need?
ML
http://milambda.blogspot.com/|||I would use a greater than/less than for this.
Something like (you would have to add Coalesce or IsNull function calls if
either sales_2005 or sales_2006 can be NULL):
Select contract,
Max(Case When sales_2005 > sales_2006 Then sales_2005 Else sales_2006 End)
As maximum_sales
From ...
Group By contract
Order By contract
If you absolutely want to avoid greater than/less than, you could do:
Select contract,
Max(sales) As maximum_sales
From (Select contract, sales_2005 As sales From ...
Union All Select contract, sales_2006 As sales From ...) x
Group By contract
Order By contract
but that would probably be less efficient than the first query.
Tom
"maarten_dtg" <u23468@.uwe> wrote in message news:625ad6dff69a4@.uwe...
> Hi all,
> A short question about min and max functions. Let's say I have the
> following
> table:
> contract sales_2005 sales_2006
> 1234 100 120
> 5678 870 560
> Now, I want to build a query that gives the following result:
> contract maximum_sales
> 1234 120
> 5678 870
> So I want to get the maximum yearly sales per contract over the years 2005
> and 2006. A normal max-function won't work because it only calculates the
> maximum row in a given column. Is there a function that does what I'm
> looking
> for? Also to get the minimum yearly sales per contract.
> Of course, it can be solved with a greater than/less than construction but
> I'm looking for a more efficient solution.
> Thanks in advance!
> regards,
> Maarten|||See the execution plan for the three "select" statements as a batch.
create table dbo.t1 (
contract int not null primary key,
sales_2005 money not null,
sales_2006 money not null
)
go
insert into dbo.t1
select
*
from
(
select 1234 as contract, 100 as sales_2005, 120 as sales_2006
union all
select 5678, 870, 560
) as t2
go
select
contract,
case when sales_2005 > sales_2006 then sales_2005 else sales_2006 end as
max_sales
from
dbo.t1
select
contract,
max(sales) as max_sales
from
(
select contract, sales_2005 as sales from dbo.t1
union all
select contract, sales_2006 as sales from dbo.t1
) as t2
group by
contract
select
dbo.t1.contract,
max(case when t2.c1 = 1 then dbo.t1.sales_2005 else dbo.t1.sales_2006 end)
as max_sales
from
dbo.t1
cross join
(
select 1 as c1 union all select 2
) as t2
group by
dbo.t1.contract
go
drop table dbo.t1
go
AMB
"maarten_dtg" wrote:

> Hi all,
> A short question about min and max functions. Let's say I have the followi
ng
> table:
> contract sales_2005 sales_2006
> 1234 100 120
> 5678 870 560
> Now, I want to build a query that gives the following result:
> contract maximum_sales
> 1234 120
> 5678 870
> So I want to get the maximum yearly sales per contract over the years 2005
> and 2006. A normal max-function won't work because it only calculates the
> maximum row in a given column. Is there a function that does what I'm look
ing
> for? Also to get the minimum yearly sales per contract.
> Of course, it can be solved with a greater than/less than construction but
> I'm looking for a more efficient solution.
> Thanks in advance!
> regards,
> Maarten
>|||This is a cross tabs and not a table. You have split an attribute,
time, over two columns.
In Oracle, you would use GREATEST (sales_2005, sales_2006), but we have
to do more work:
SELECT X,contract_nbr, MAX(X.sales_annual)
FROM (SELECT S1.contract_nbr, S1.sales_2005
FROM SalesCrossTab AS S1
UNION ALL
SELECT S2.contract_nbr, S2.sales_2006
FROM SalesCrossTab AS S2)
AS X(contract_nbr, sales_annual)
GROUP BY X,contract_nbr;

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

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

This is the result table

Date Statement From To

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

From =From Statement Number

To= To statement Number

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

Any ideas why this odd behavior happens?

rectis:

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

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

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

Min and Max from one dimension based on Grouping from another dimension

Hi all,

I have 2 Dimensions D1 and D2. They both have a common Attribute "ID".

For one id in D1 there are multiple records in D2. This is like a parent child relationship with Product as parent Dimension and Product category as child dimension. For each product there are multiple product categories.

I want to get the Min and Max from child Dimension for each ID in Parent Dimension.

In sql this can be written as

select Distinct D1.ID,Min(D2.AttributeName) from D1,D2 where

D1.ID=D2.ID group by D2.ID

Can anybody write a MDX based on this?

Thanks

Girija.

Girija,

When there is Parent-Child Relationship between D1 and D2 as described Product and Product Category, I feel there should be Hierarchy in Cube for D1 and D2. And hoping you have set Order By Property to Key which stores ID and then simply you could use FirstChild for Min and LastChild for Max ID.

Bhudev

|||

Hi bhudev,

I gave parent - child reationship just as an example..... There is no hierarchy between those two dimensions.... They are connected only through ID.

Regards...

Girija Shankar

|||

Girija

I'm giving you the way how you can do it. Simply make Calculated Members for these ID under Measures Dimension and apply Min and Max function, I hope you will get yourself.

Bhudev

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

Miltivalue Parameters with Max Pool connections

I am trying to write a report with many different records needed (way the database was designed). Client will need multivalue parameter and I have reached max pool connections(can anyone please give me the number).

Normally I would handle this with a stored procedure to create a temp table with the needed information for each section(one row per section), but this will not work with the multivalued parameters(more than one in this report).

Any help on this issue would be appreciated.

Thanks!

Terry

I'm not sure I understand the issue why you are running out of connections, but if all datasets are based on the same data source, you could select the "Use Single Transaction" checkbox on the data source dialog. In that case, all datasets running against that data source will use the same connection. See also: http://msdn2.microsoft.com/en-us/library/ms181198.aspx

-- Robert

|||

I have found the data source, but not the single transaction area. Any help in this area would be appreciated. Will have to solve the multivalue parameter issue later. Idea without multivalue parameter would be a stored procedure to fix this.

Have one other idea with @.t table useage.

Thanks for the information.

Terry

|||

The "Use single transaction" checkbox is only available in report designer. You cannot change this setting once the report is published.

-- Robert

|||

That answered the question. I will have to find it at the start of the development for a new system.

Thanks!

Terry