How can I achieve this

Hi,
I created a report to display in invoice when passed an invoice number. How
can I display multiple invoices when i pass either a date range? I am not
sure how to get multiple invoices, one after the other on different page.
ThanksOn Jun 2, 12:53 pm, Chris <C...@.discussions.microsoft.com> wrote:
> Hi,
> I created a report to display in invoice when passed an invoice number. How
> can I display multiple invoices when i pass either a date range? I am not
> sure how to get multiple invoices, one after the other on different page.
> Thanks
There are a few different options here. You can use a subreport that
will automatically print out on different pages or you can use a table/
matrix control and group on invoice number and right-click the control
-> select Properties -> select the Groups tab -> select Edit... -> and
select 'Page break at end.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

how are these queries evaluted differently by sql server?

I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.

I could use the following:

select customerId, item, min(activationDate) from history group by customerId, item

or a sub query

select customerId, item, activationDate

from history h1

where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)

How are these two queries parsed differently by SQL.

They return a different number of results.

Thanks,

karen

I'm guessing that customerId and item do not uniquely define a record in the table, is this true? If so, that would explain the difference in the amount of rows between the two result sets.

-The first query finds the minimum activationDate for each unique customerId and item.

-The second query finds the minimum activationDate for each customerId and item.

-If you add a group by customerId, item to the second query you should have matching result sets.

The inner query of the correlated subquery is processed once per record of the outer query.

|||

If you have the following data in the table:

CustomerID - Item - ActivationDate

1 1 1/1/2007

1 1 1/1/2007

The first query will return one record while the second query will return two. This is because (as the above post indicates), that the first query is returning a true grouping while the second will return duplicate records if there is duplicate data in the table.

|||

Bcs there are duplicate entries available on your database..

You can force DISTINCT class to fix this..

Sample..

Create Table #samplehistory (

[customerId] int ,

[item] int ,

[activationDate] datetime

);

Insert Into #samplehistory Values('1','1','1/1/2007');

Insert Into #samplehistory Values('1','1','1/2/2006');

Insert Into #samplehistory Values('1','1','1/3/2006');

Insert Into #samplehistory Values('1','2','1/11/2007');

Insert Into #samplehistory Values('1','2','1/11/2003');

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate) activationDate

from

#samplehistory

group by

customerId, item

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

After duplicating one of the value.. You are result is correct but there are dupicate data in the result

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate)

from

#samplehistory

group by

customerId, item

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

122002-01-11 00:00:00.000

*/

The group by class force the First query to avoid the duplicates (already distincted values are return).

After Distinct on second query,

Code Snippet

select distinct

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

|||

Thank you very much for your help. I really appreciate the time.

Would you recommend a book for dealing with these kinds of sublties in SQL?

Karen

|||

Inside SQL Server 2005 T-SQL Querying by Itzik Ben-Gan is very good.

|||Ken Henderson's "The Guru's Guide to Transact-SQL" is also very good.

hosted sql 2005 databases

We are hosting multiple sql server 2005 databases on a windows 2003 server.
I've setup restricted growth on the databases to 50 mb. What would be a good
setting for the log files? I have all of the databases setup in Simple
recovery mode. Is Auto Shrink = True a good idea?
Please advise.
Thanks!
Hi
The best way to get these values is to monitor it during you normal working
period (e.g. a week and take the values from that). If the files are often
growing then you may want to increase the size they grow by, or put in a
system when the files are increased at a time when it will not impact users.
For log file sizes hopefully you can set a maximum ceiling that will rarely
be expanded.
Growing the files frequently may lead to disc fragmentation and decreases in
performance, therefore auto shrink is not a good idea.
You should monitor the space available on your disc so that you can prevent
the sort of situation where there is not enough room to expand the database.
MOM is a very useful to monitor your SQL Server in this respect.
John
"mp3nomad" wrote:

> We are hosting multiple sql server 2005 databases on a windows 2003 server.
> I've setup restricted growth on the databases to 50 mb. What would be a good
> setting for the log files? I have all of the databases setup in Simple
> recovery mode. Is Auto Shrink = True a good idea?
> Please advise.
> Thanks!

hosted sql 2005 databases

We are hosting multiple sql server 2005 databases on a windows 2003 server.
I've setup restricted growth on the databases to 50 mb. What would be a good
setting for the log files? I have all of the databases setup in Simple
recovery mode. Is Auto Shrink = True a good idea?
Please advise.
Thanks!Hi
The best way to get these values is to monitor it during you normal working
period (e.g. a week and take the values from that). If the files are often
growing then you may want to increase the size they grow by, or put in a
system when the files are increased at a time when it will not impact users.
For log file sizes hopefully you can set a maximum ceiling that will rarely
be expanded.
Growing the files frequently may lead to disc fragmentation and decreases in
performance, therefore auto shrink is not a good idea.
You should monitor the space available on your disc so that you can prevent
the sort of situation where there is not enough room to expand the database.
MOM is a very useful to monitor your SQL Server in this respect.
John
"mp3nomad" wrote:
> We are hosting multiple sql server 2005 databases on a windows 2003 server.
> I've setup restricted growth on the databases to 50 mb. What would be a good
> setting for the log files? I have all of the databases setup in Simple
> recovery mode. Is Auto Shrink = True a good idea?
> Please advise.
> Thanks!