How To Get Top 3 And Bottom 3 Salaries

Mar 13, 2008

Hi,

I have a table like this..

empno empname dept sal
----------- ------------------------------ -------------------- -
1 mike HR 4000
2 John HR 2000
3 Patty HR 3500
4 Carly HR 2200
5 ABC HR 1000
6 DEF HR 4500
7 Ben HR 2200
8 HGI IT 4000
9 GOG IT 2000
10 LILY IT 3500
11 Cathy IT 2200
12 Paul IT 1000
13 Ben IT 4500
14 Ben Fin 2200
15 HGI Fin 4000
16 GOG Fin 2000
17 LILY Fin 3500
18 Cathy Fin 2200
19 Paul Fin 10800
20 Ben Fin 4500

How will i get Maximum and Minimum 3 salaries of EACH DEPARTMENT
ie i want to know who are the employees who are drawing top 3 max and min salaries in each department?

View 15 Replies


ADVERTISEMENT

How To Retrieve The Top Most 5 Salaries From Table

May 12, 2008

Hi all..

I want to retrieve the top most 5 salaries from the table please help.

Thanks in advance
Ramesh.

View 12 Replies View Related

Difference Between The Highest And Lowest Salaries

May 8, 2007

I need to display the difference between the highest and lowest salaries. Label the column DIFFERENCE. like below:


From what I came up with is should look something like so:

<b>SQL> select * from 'salary' order by salary desc limit
select * from 'salary' order by salary desc limit 1,1 </b>

DIFFERENCE
------------------
4200

View 8 Replies View Related

Row Not Inserting At Bottom

Jan 14, 2005

Greetings, SQLanoids,

When I insert a row of data into a table via a stored procedure, the row SOMETIMES gets inserted at the bottom, and sometimes gets inserted at another random location (top, midding, etc). I am using Microsoft SQL Server.

Dea anybody know the cause of this? Or how to ensure that the row gets inserted at the bottom?

I am a novice SQL Server user, but this seems like a simple problem that the more advanced users solved long ago. Thanks in advance!

View 3 Replies View Related

Sorting So 0 Is At The Bottom

Jul 20, 2005

Is there a way to sort records the following way.Original Table:KeyField Column 11 02 5003 2004 3005 0To this sorted tableKeyField Column 13 2004 3002 5001 05 0so that the numbers in column 1 are sorted asc but it treats a 0 asthe largest number. We need the first number greater than 0 to befirst in the table.

View 5 Replies View Related

Bottom N/Count (*) Query

Jun 30, 2005

Hi,

I am trying to construct a query which wuill give me the following results:

Source Data:
ID Version
s015 0
s015 1
s016 0
s017 0
s017 1
s017 2

Results:
ID Version
s015 1
s016 0
s017 1
s017 2

So, I was to discard the Version 0s unless there is no other version for the ID. Can anyone help? Thanks.

excelthoughts

View 1 Replies View Related

Displaying Sum At The Bottom Of The Table

Nov 9, 2006

neo writes "hi
i am a beginner at sql
this query confuses me...

q:A LIST OF SERVICE TRANSACTIONS(WITH A FULL DESCRIPTION OF THE SERVICE) ON A PARTICULAR DAY ALONG WITH THE NAMES OF THE CUSTOMER AND THEIR ROOM NUMBERS.THE TOTAL AMOUNT TRANSACTED NEEDS TO BE DISPLAYED AT THE BOTTOM OF THE LIST

REQD TABLES ARE
SERVICES,TRANSACTIONS AND RESERVATIONS...

all i can do was this answer...

SELECT T.TRANID,S.*,R.FNAME,R.LNAME,R.ROOMID FROM SERVICES AS S ,RESERVATIONS AS R,TRANSACTIONS AS T
WHERE S.SERNO=T.SERNO AND T.CUSTID=R.CUSTID ORDER BY T.TRANID
COMPUTE SUM(S.SERCHAR)

is there a better way to display in the same table the sum too?"

View 4 Replies View Related

ASC Result Set With Nulls At The Bottom

Jan 25, 2006

I have a results set that is sorted on LineNum. Not all items have aLineNum (some are null). I want my result set to return all items inASC LineNum order and then list all items with NULL LineNum at the endof the results. Any Suggestions?Thanks.

View 1 Replies View Related

Print At Bottom Of Page.

May 29, 2007

Hi.



I know the function of being able to set a section of a report to print at the bottom of the page, but does anyone know any work arounds for this? I have a main report (statement) which ive done a work around, to include the details i need at the bottom of the page into the footer, but then i have a second report (invoice) that needs to be included as subreport, which also needs infomation at the bottom of the page, but as its a subreport, sql reports does not allow for footer/header infomation in a subreport.

Unfortuantly i can not split the two reports out as they need to be printed together.



Any help/recomendations would be greatly received.



Thanks



Tom

View 4 Replies View Related

Select Bottom Record

Feb 19, 2008

Hi Guys, I am trying to select the bottom record in a tabel based on a primary key and having no luck. Can some please help. This is how I have tried.


"SELECT Bottom Week,DOR from Observation_Record Where ID = '" + ID + "'"Thanks Trafmore

View 4 Replies View Related

Text Direction - Bottom Toward Top

Jun 22, 2006

Hi all,

I have problem to change direction of the textbox, i need to change the driction as bottom to top.

But in report has provide the tb-rl and tb-lr. these are changed the direction as top to bottom.

anybody worked on it , please help.

Thanks

Murugan A









View 12 Replies View Related

How To Set The Text Direction BT-RL 'bottom To Top' In SRS

Jan 30, 2008

How can you set the text direction to BT-RL 'bottom to top' in SRS? There is control for TB-RL but not BT-RL. Need this for SRS label printing application.

View 1 Replies View Related

Selecting Bottom Rows From A Table

Dec 15, 1999

I need to select the last 6 rows from a table... I was doing it like this:

select * from table where id > (select max(id)-6 as id from table)

But if one of the records is deleted it will only show 5.... Is there any easy way to make it show the bottom 6 records?

much thanks sql gods...

View 2 Replies View Related

SQL Server And Mail (what&#39;s The Bottom Line?)

Jun 1, 1999

Hello all, I'd like to know if anyone has been able to get SQL Server 6.5 to send mail to a MAPI mail server other than Exchange. I would
like to implement this functionality so that my group and I can better monitor the results of backups and other scheduled tasks but we do not
have Exchange within our site. We currently use Netscape's mail server so it would be GREAT if someone with this knowledge could reply with
any tips or URLs or whatever. Just something to get me heading in the right direction. Thanks for your time.

Best,
mtf8

View 1 Replies View Related

Extra Space At Bottom Of Page

Jan 18, 2007

I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?



Thanks,

View 1 Replies View Related

Transact SQL :: Cursor Fetch From Bottom To Top?

Sep 14, 2015

I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.

It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:

'(1 row(s) affected)
6255.84
(1 row(s) affected)
Msg 16931, Level 16, State 1, Line 18

There are no rows in the current fetch buffer.

The statement has been terminated.'

how to fix the error?

Here is my code:

DECLARE @lastprice real
DECLARE @updatedprice real
DECLARE @updatedRe real
DECLARE @updatedAUX real
SET @lastprice = (
    SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
    )

[code].....

View 4 Replies View Related

Return Bottom 10 Rows Not Working.

Feb 1, 2007

Ok so this is more then likely very easy for most. But for me it's hard. I have a stored proc that I've been able to get to retun just the top 10 rows and have been able to order it asending. But it will only return the top 10 and I need to bottom 10 of the selection.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[proj_ListFiles](@project_id int)
as
select top 10(@project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@project_id

How can I make it return to bottom 10. On a side note about this, when it returns the recordset they all have the 'id' of the 'project_id' that I selected. Not that the side note really bothers me as it will not be returning the ID's to be visible.

Any help would be great,
Thanks
Tim

View 9 Replies View Related

Add Grand Total At Bottom Of Pivot Table?

Nov 9, 2013

I've been messing around with ROLLUP but have got nowhere,I have this:

DECLARE @sql NVARCHAR(MAX)
DECLARE @pivotsql VARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @selectlist VARCHAR(MAX)

[code]...

which works a treat - I just need a grand total at the bottom for each column.

View 7 Replies View Related

1 Inch Added To Bottom Margin Of Report

Dec 20, 2007

I'm having a relatively big issue with printing my check report. The report continues, time after time, and regardless of the margins I choose for the report, to be displaced an additional 1" up from the bottom of the page. The printing method is through our newly designed application which sends the emf rendering to the printer. We have gone through the liberty of modifying the actual print document in VB with margins set at 0,0,0,0 (L, R, T, B) and have paid close attention to the printer's maximum printing area. We have come to the conclusion that the problem is coming from reporting services. In addition to the extra margin space on the bottom of the page, there is also a slight yet noticeable shrinking of the page. Despite the fact that those are trade symptoms of another issue after the emf rendering we have modified every possible function of the printdocument and printer settings. So we continue to push the question, is this reporting services? We are fairly certain. I'm asssuming this is a bug in SSRS 2005, yet, I have yet to hear of any similiar case. This is our VS version info just incase:

Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Enterprise
Microsoft Visual Basic 2005 77642-113-3000004-41917
Microsoft Visual Basic 2005
Microsoft Visual C# 2005 77642-113-3000004-41917
Microsoft Visual C# 2005
Microsoft Visual C++ 2005 77642-113-3000004-41917
Microsoft Visual C++ 2005
Microsoft Visual J# 2005 77642-113-3000004-41917
Microsoft Visual J# 2005
Microsoft Visual Studio Tools for Office 77642-113-3000004-41917
Microsoft Visual Studio Tools for the Microsoft Office System
Microsoft Visual Web Developer 2005 77642-113-3000004-41917
Microsoft Visual Web Developer 2005
Visual Studio 2005 Team Edition for Developers 77642-113-3000004-41917
Microsoft Visual Studio 2005 Team Edition for Software Developers
Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

Microsoft Visual Studio 2005 Premier Partner Edition - ENU Service Pack 1 (KB926601)
This service pack is for Microsoft Visual Studio 2005 Premier Partner Edition - ENU.
If you later install a more recent service pack, this service pack will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/926601
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.1399.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00


I'm planning to talk to MS if nobody recognizes these symptoms. Any ideas would be greatly appreciated! Thanks!



UPDATE (12/20/2007 1:43:07 pm):
We have made a small break-through discovery. We found that that if we convert our report using a pdf rendering that the additional 1 inch added to the bottom margin is no longer there. So, pdf rendering would be an option if we became more desperate. However, this is too time consuming and we would much rather have the report directly sent to the print spooler instead of loading then spooling. Does anybody know what is going on with this apparent conflict between SSRS 2005 and our emf renderer?

View 6 Replies View Related

Reporting Services :: Top 5 And Bottom 3 With Colours In SSRS

Jun 11, 2015

I have to display Top 5 values in 'Green' and Bottom 5 in 'Red' out of some data.

Ex: my data is as below 
 
ID Marks
1 98
2 62
3 89
4 97
5 92
6 67

[Code] ....

Expected result is : if the marks are >= 80 then 'Green' (pick only top 5)  if marks are <= 70 then 'Red' (pick bottom 5) else 'Black'

As per my data even though we have values >=80 for more than 5 students display only top 5 in Green and same rule applies for Bottom 5 that means

ID 's : 1 ,3 , 4 , 5, 9 and their marks in Green (pick only Top 5 ) even though we have 6 students who got >=80
ID's : 2 , 6, 16 , 15 and their marks in Red (Bottom 5 but we have only 4 students who got <=70 )

View 2 Replies View Related

How To Put A Footer On The Bottom Of Sets Of Pages In A Report

Aug 16, 2007

Hi forum members.


I'm a new SQL Reporting user and could use some guidance.

Here is a simple explanation of my application: imagine a report which prints out a grade report for each of 10 students. Depending on how many notes a teacher makes on the student's individual grade report, each student's report might be 1 or 2 pages long.

To create this report I just used a List report item and laid out a typical grade report and filled in the appropriate fields (name, teacher, etc.) from my dataset query.

When I run the report, it is running great and the students and their grade reports all print out.

The problem I am running into is I want to make sure each student's name is on page 2 if the grade report for that student goes more than 1 page (so pages aren't separated from each other or mixed up). I tried using a Report Footer, but that just put the first student's name at the bottom of every page. I could put a textfield at the bottom the list control but that would just appear at whatever length page 2 ended up being (basically you could have a "footer" at mid-page if the whole list only took up 1.5 pages).

How can I create a footer for each set of pages? Basically this would be a "footer" to the list control itself.

Thanks in advance!
Dave
davehunt00

View 2 Replies View Related

Table Group Footer At Bottom Of Page: Possible?

Nov 29, 2007



I have a report (bills of lading for shipments) that uses a table to organize the data coming in and groups by customer. Each customer grouping has a header, body and footer in the table. A print run might generate 100 bills of lading, each between 1 and 4 pages long.

I have marked the group to force a new page when each new group occurs.

As it is the report runs beautifully EXCEPT that the footer prints wherever it happens to end up -- midway down the final page of the bill and looks quite goofy.

What I really need (and my client is really surprised that SSRS won't me) is to print the group footer at the bottom of the final page of each bill of lading.

Bills of lading may be several pages per customer, so until the group footer is hit, I don't want any footer info at all. As I said, it all works fine except for the final page of each bill, where the group footer occurs whereever it has ended up.

Jim


12/4/07

This post didn't get much of a response. So perhaps I should amend it -- If what I am trying to do is impossible, what would be the preferred way?

View 5 Replies View Related

Reporting Services :: Generate Single Bar On Bottom Of The Chart?

May 8, 2015

I am designing a bar chart in which five value comes .if all values comes then graph is working fine but my concern is when i have single value then in that case bar gets created on the middle of chart i want to generate the single bar on bottom of the chart.

View 5 Replies View Related

Is There A Keyword Or Function Opposite Of TOP? How Do I Get Various LOWEST/BOTTOM Values?

Jan 26, 2008

Hello Group,

I'm still a bit new to T-SQL and am wondering how to get various LOWEST values from a table, rather than TOP values. There does not appear to be a keyword opposite of TOP in T-SQL.

My real problem is this: I know how to get the 2nd highest value, third highest value, etc...but how does one get the 2nd lowest value, third lowest value, etc?

Would anyone be able to help?

Thanks!!

View 8 Replies View Related

How To Show Bottom Border Line Of A Table Row At Each Page End

Jan 25, 2007

Hi all,

I'm having an interesting problem. In my report, I use a table to show my data set. The borders of the table are giving me a hard time...

The problem is, I'm not being able to display the bottom border for a row, but only at the end of the page, causing the table containing multi paged report data to be seen inside a box. Just to be clear;

I don't want to display borders of each row
I don't want to display table footer in each page (Only on last page)
I've already tried to display page footer, top border. Its not the answer :D

Thanks very much...

SMang

View 4 Replies View Related

BorderStyle Not Shown As Solid For Top ,Bottom &&amp; Right Side Of Reports.

Mar 10, 2007

HI ,

Although I am setting the Border Style for each & every cell and /or Merge cells ,But it is not reflected in the Reports Pre-view for Bottom ,Top & Right Sides in the Reports?Any clue why this is happening & for removing this & getting the right results would be helpful .

Thanks

View 1 Replies View Related

Absolutely Page-bottom Alignment On Report Footer: Impossible?

Mar 21, 2007

Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.

Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.

This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.

Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.

So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.

Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.

I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.

Among what I've tried:

--Page footer (can't; databound)

--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).

--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.

I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?

Thank you for your time in reading this.

View 14 Replies View Related

Reporting Services :: Absolutely Page-bottom Alignment On Report Footer

Mar 21, 2007

Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements. This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope. Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.

Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.Among what I've tried:

--Page footer (can't; databound)
--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).
--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.

I may be having to explain to my client that you can't get there from here, and they may have to redesign their report.

View 13 Replies View Related

Inserting A Bottom Border (or Line Break) Every 3 Lines Into A Table In SSRS

Jan 29, 2008



Can this be done in SSRS or must it be done in the query? Ideas? Suggestions? See Example Below:

Current table look:

1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...

The Way I need it to look

1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...

TIA (Thanks in Advance!)

View 4 Replies View Related

Reporting Services :: Span Table Till Bottom Of Page - SSRS?

Sep 2, 2015

There is a huge empty space between my tablix which is inside a rectangle to the footer.

I need to be able to fit another row but the other rows go on to the second page. How can I fix this?

View 8 Replies View Related

Reporting Services :: SSRS Report Page Footer - How To Fix At Absolute Bottom With Dynamic Body Content

May 20, 2015

We have the customer requirement to display the footer of a SSRS Report fixed at the absolute bottom of a DIN A4 format page. The footer contains information like company address and stuff.

I searched quite a while on this topic and only found workarounds for SQL Server 2005 with Custom Code in the SSRS Report to calculate the size of the body content and then insert some empty lines to get the space needed to push the footer to the bottom of the page. But this won't work in SQL Server 2012. And I wasn't able to figure out how to achieve this yet.

View 2 Replies View Related

Expression After Table Object Forced To Display On The Last Page Even There Are Still Spaces At The Bottom Of The First Page.

Oct 16, 2006

The following objects are placed on the Report body of the Report pane of SQL Server 2005 Reporting Services :

<textbox: expression1>
<textbox: expression2>

<table:table1 with at least 30 columns and 30 expressions>

<textbox: string1> - considered as the Title in the Footer section of the report

<texbox:string2> <textbox:expression3>
<textbox:string3> <textbox:expression4>
<textbox:string4> <textbox:expression5>
<textbox:expression6>

I can't find any explanation why is it string1 and string 2 of the footer section of my report displayed separately from the expression3 which is aligned on it and the rest of the object on the second page.

The expected design is that all Footer items should be displayed together of whether it is placed on the first page or on the last page.

As a workaround of this, I converted string 1 into an expression (Added = and enclosed the string with double quote).. As a result, all of the items in the Footer section are now placed together on the last page of the report.

I also remember one of the issue I encountered before where the Footer items where placed together on the first page and still have space at the bottom of the page, but then expression 6 is forced to display (alone) on the last page of my report.

I can't find any discussion related to this, I wish somebody could give me an idea why RS behaved like this.

Thanks in advance

View 1 Replies View Related

How To Display Data At Bottom Of Report By Daily, Weekly, Monthly, SYTD Using Ssrs Report

Dec 14, 2007

hai iam new to ssrs, please help me.


i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at

Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.

thanks to advanced

Jacks v

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved