How to flow a table into a second column

s it stands, my table skips the second column on the page and flows
into the first column on the second page, and then the first column on
the third page, ad nauseum. I want the table to fill up both columns on
the page before breaking to the next page. All the docs I've found
state that flowing into multiple columns like this happens
automagically. If so, what's the incantation I'm missing?
This is being attempted on VS2005 Beta 2 on XP.
TIA,
NoelI am having the same problems. I discovered that if you right click on the
report preview and select "print preview" from the pop-up menu, the columns
are magically displayed.
Now how does one publish this report so that the multi-columns display
properly on the web?!!
"Noel Weichbrodt" wrote:
> s it stands, my table skips the second column on the page and flows
> into the first column on the second page, and then the first column on
> the third page, ad nauseum. I want the table to fill up both columns on
> the page before breaking to the next page. All the docs I've found
> state that flowing into multiple columns like this happens
> automagically. If so, what's the incantation I'm missing?
> This is being attempted on VS2005 Beta 2 on XP.
> TIA,
> Noel

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

How to find Second monday of october?

Hi,
Can any one help to get the date of second monday of october for the given year.
Ex: the input will be 10, 2006
and output should be 9, where 9 is the second monday dateThis may be a way to do that:

create function dbo.second_monday_on_oct (@.ra_year int)
returns int
as

begin
declare @.full_date datetime
declare @.day int

set @.full_date=convert(smalldatetime, '1.10.'+cast(@.ra_year as char(4)), 104)
set @.day= 16- datepart(dw, @.full_date)

if datepart(dw, @.full_date)=1 set @.day=@.day-7
if @.@.datefirst<>1 set @.day =@.day+ 8 - @.@.datefirst

return @.day
end

Best regards,
Adriana|||EDIT: very nice, adriana, you beat me to it, i was busy testing my solution while you posted yours :)

but i can do it without an IF test by using modulo arithmetic

here's my thought process:

-----------

okay, we solve this using simple logic

if the 1st of october is a monday, the second monday is the 8th -- the point being that the second monday can't be any earlier than the 8th

if the 1st of october is a tuesday, the second monday is the 14th -- the point being that the second monday can't be any later than the 14th

so the second monday of october for any year is between the 8th and the 14th

okay, so let's look at october 8th --

if october 8th is a sun, then the 9th is the second monday of october
if october 8th is a mon, then the 8th is the second monday of october
if october 8th is a tue, then the 14th is the second monday of october
if october 8th is a wed, then the 13th is the second monday of october
if october 8th is a thu, then the 12th is the second monday of october
if october 8th is a fri, then the 11th is the second monday of october
if october 8th is a sat, then the 10th is the second monday of october

okay so what do we have in SQL Server that tells us what day of the week a date is? answer: DATEPART(dw,date)

i will not bother explaining how i derived the following formula, but i assure you it works

declare @.year integer

set @.year = 2006

select dateadd(day
, ( 9 - datepart(dw
, cast(cast(@.year as varchar)+'-10-08' as datetime)) ) % 7
, cast(cast(@.year as varchar)+'-10-08' as datetime)
) as secondmondaysecondmonday is a datetime value for the 2nd monday of october for the given year

to use this formula, all you have to do is plug whatever year you want into the @.year parameter

or, to make it a generic formula for whatever the current year is, you can simply use year(getdate()) instead of @.year

neat, eh?|||Thanks for your suggestion. Both the solutions are working fine for me.

Now, one step ahead

I want all the things to be parameterized

that is now i wanted to provide 'October', '2' and 'Mon' as argument

so the function will take the argument as

function day_Cal(@.year, @.month, @.whichWeek, @.whichDay)
returns @.day

That is need this to be generalized.

Expecting for a favorite solution.

Thanks in advance

Bala

How To filter data set based on value from another

I have a data set that contains rows with a column for "User ID". I want to filter a second data set that contains one or more user "role names" by "User ID" . I want to do this dynamically without having to define a parameter. I then want to to take the filtered "Role Names" data set and display on a report. Any pointers would be appreciated.

Data Set #1
User ID First Last ........
crace Chares Race
mmiller Mike Miller

Data Set #2
User ID Role
crace administrator
crace guest
mmiller guest

Desired Report Result

Page 1
crace Charles Race
Roles administrator
guest

Page 2
mmiller Mike Miller
Roles guest
Thanks
CharlesHi,
cycle through data set 1 with a for loop and save the id. then cylcle with a second loop through dataset 2 and look for the user ID. Then you have a variable which contains the row where this user is in the first dataset and a diferent one, which contains the rownumber in which the user is in dataset 2.|||Even though this would work it wouldn't be very efficient as there are over 20,000 rows to traverse through. Would there be a more efficient approach?

Thanks
Charles|||When importing the data into the new dataset, store the key in a array (or hashtable) and before inserting the next record - check the hashtable if it already contains the key, if so skip it.....|||Hi,

can you not use a sql statement with a where clause when retriving your data from the database?! so you would have one huge dataset containing the data in the way you want to have it.