|
for sqlserver 2005
use unpivot method
|
|
|
|
|
Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received.
The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me.
Thank you Anup for your assistance and I will further explore the command you have suggested.
|
|
|
|
|
Hi,
I'm looking for a SQL-based reporting system with the following features for running against a SQL Server 2005 database.
- SQL for each report is to be hand coded (i.e. NOT 'ad-hoc/drag-n-drop end user reporting')
- Users will select parameters at run time
- Output to browser (don't care about controlling hard-copy formatting)
- Must be able to include complete T-SQL.
- Ideally include support for transactional operations
I wrote something very much like this in my last job, but would rather buy this time.
Anyone used or even know of such a beast? I've scoured Google with no result. Everything I've found is either 'Build reports without knowing SQL' or 'use this toolkit to integrate a specific report into your application.
Thanks,
Max.
|
|
|
|
|
It's called SQL Server Reporting Services. DevExpress also do an excellent reporting tool.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi There
I need to create report that needs to group by "[B]status[/B]= Funded and Not funded" , "[B]Institute[/B]" body is title
I need to find the [B]Total amount [/B] that Institute had earned for different Research Proposals. In One Proposal there could be Group of people involved
I am creating Report using this Query
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.Amount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
WHERE (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
My result is some thing like this
Title Inst Type Person Role SID Amount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
I want the total By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
I have created the group by Institute, added text box at the footer of Institute and assigned = Sum([Amount]) to the control source of that text box. I am not sure Why I am not getting the result
Thank you
Bijaya
|
|
|
|
|
You are not Using Group By method.For your knowledge U should Use Group By Query.
Like : SELECT A.CustomerName, A.SUM(OrderPrice) FROM Sales as A GROUP BY A.CustomerName
Best Regard
Anubhava Dimri
mailto: anubhava.prodata@gmail.com
|
|
|
|
|
Thank you Anubhava,
This is my Group By Query :
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Sum(Proposal.Amount) AS SumOfAmount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
GROUP BY Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.DateSubmitted
HAVING (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
Still result remains the same
Title Inst Type Person Role SID SumOfAmount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
At Institute footer cntains Text feild =sum(SumOfAmount) for Del= ($500,000 *3) + ($900,000 * 3) = $ 4200,000
for comp= $2400,000
Where As I want this
By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
Thank you
Bijaya
|
|
|
|
|
Hi Everybody,
I am working on the proble With the first query. I am not sure why I am not getting the Sum Amount by Institute(Inst)
Just to get Total Amount by Institute. I created another Query that is:
SELECT Institutions.IName,
Sum(Proposal.Amount) AS SumOfAmount,
Proposal.sid
FROM Proposal INNER JOIN (Institutions INNER JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID
GROUP BY Institutions.IName, Proposal.sid
HAVING (((Proposal.sid)=1 Or (Proposal.sid)=2));
Datasetview of the above Query is:
Inst SumAmount SID(status)
Del 14,00,000 funded
comp 12,00,000 not-funded
But when I assign this to Combo box at the Footer of Institute Group. I am not getting the accepcted result. I am getting this one.
Del = 500,000
Comp = 1200,000
Could onyone help me why this is happening?
I am counting on you, my project is due comming Monday. help Needed!
thank you
Bijaya 
|
|
|
|
|
I'm new to crystal report and currently is using CR Ver. 10 to develop my project. I would like to know how can i make my report to display data from db in horizontal with maximum of 3 rows of data per page.
My sql command look something as below:
select personid, personname, personfname, personlname, personmname
from person_tab
It should display as template below:
PERSON REPORT
ID | ID 1 | ID 2 | ID 3
NAME | NAME 1 | Name 2 | Name 3
FIRST NAME | First Name 1 | First Name 2 | First Name 3
LAST NAME | Last Name 1 | Last Name 2 | Last Name 3
MID NAME | Mid Name 1 | Mid Name 2 | Mid Name 3
|
|
|
|
|
you should used cross tab report type . it will available when u will add the report in own solution explorer.
lets me know if any things is needed.
|
|
|
|
|
Hi Experts
I facing a great Problem Pls Help.
I Have Database in SQL Server 2005.i giving it to My Client end.
i Want To Protect My Database Not To Open or Modified By Another User.
How To Make Certificate of Database and Use It at Client End So Another Person Not To Open My Database
|
|
|
|
|
I don't think that you can sign a SQL-database the way you sign a Word-document. A database is meant to be changed, if you want to restrict access then dive into permissions, users, passwords, policies and logons 
I are troll
|
|
|
|
|
Hi
I am using Visual Studio 2005 and C# to develop a Windows application in the cancer department where I work. It is a small patient management system. I was asked to use MS Access as the backend. For obvious reasons I would prefer to use SQL Server. Unfortunately the network guys running the hospital network are very sticky with what is allowed on their servers. This is quite understandable, but it does make things a bit difficult. We are only allocated a folder on the network that our department has access to. With MS Access it is easy enough. We just copy the MS Access database into the folder and point the application to it.
If I can use SQL Server how would I deploy it? Must SQL Server be installed on the server first? I will probably use SQL Server 2005 Express.
Thanks.
Kobus
|
|
|
|
|
SQL server must be installed on the Server and you continue as usuall and point to SQl that Access and that means your Code will change in the DAL , you will be using sqlclient not oledb
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
Your'e network people are idiots (or have strict policies about what they can allow departments) . You need some arguments that will move them off this policy position or get an exception. They will have some database they use and may allow you to use part of it, may be oracle or one of the other enterprise DBs.
Our network team for instance will not allow Access anywhere near a server, it has been known to crash a server and they are terrified of it. Unless properly managed it can grow horrendiously. Are you sure you do not need to install the jet drivers or even office on the server? This would be a good argument against Access.
Good luck.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I've a exe file in C:\. I need to run it from stored proc using xp_cmdshell. Please note that I'm using SQL Server 2000.
My stored proc is :
Create proc callExe
As
EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'
After executing this, the output is :
NULL
Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation
at PrintToPDF.Program.Main(String[] args)
NULL
and the exe file did not run.
Please help in this.
Also, I've tried with common exe file like 'notepad.exe' like:
ALTER proc callExe
As
EXEC master.dbo.xp_cmdshell 'notepad.exe'
When I executed this, the execution is going on and not showing any output. The execution time is more that 10 mins and going on...
Then I forcefully closed the isqlw.exe from Task Manager.
Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
You cannot run exe which has GUI. You can run exe which is running in background process.
|
|
|
|
|
Yes, I forgot to write there that the exe file does not have a GUI.
It will automatically convert the any file to pdf file and save to My Document folder.
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
Hello,
My Question is related to SQL Server Memory Usage,
I have a VB.net application with MSDE.
When i run my application, the sqlserver.exe in Task Manager uses memory of about 20 MB,
Then when I View any Crystal Report created report, the memory goes on increasing as the report is loaded and if reaches about 150 to 200 MB, but even after closing the report and also closing the application, SQL Server does not release memory.
So when we again start the application, again memory goes on accumulating but is never released.
Memory is released only when we Stop and start the SQL Server or restart the PC.
Can anyone tell me how to make SQL Server Release Memory from my VB.net application.
Thanks in Advance.
|
|
|
|
|
Hello,
sqlservr.exe gives back never the memory except when there is a memory pressure, that's to say when the operating system is needing memory for itself or for another application. For example, SQL Server is releasing memory when too much paging is occuring.
When the operating system is short of free memory, sqlserv.exe frees RAM memory ( K. Delaney neede about 20 pages in her book (Inside Microsoft SQL Server 2005 : the storage engine ) to explain the
management of the memory by SQL Server.
You may limit the amount of memory used by a sqlservr.exe ( there is 1 sqlservr.exe for each started instance of SQL Server ) thru the options Min server memory and Max server memory ( right-click on an instance , properties, tab memory ). For my 3 instances, i have limited Max server memory to 500 MB, so i am sure that never a sqlservr.exe will use more than 500 MB ( in fact, it is rare that the 3 instances are running, so i am sure that, with 2 GB of RAM , i am sure that the OS and applications other than sqlservr will have at least 500 MB of free memory.
You can't manage and free memory from your VB.net application.
But a connection to a server or database is one of the heaviest operation and i prefer to limit the number of connections by opening the connection once ( at the beginning of the application ) and closing the connection when exiting the application ( don't forget to close the connection in the exceptions treatement )
|
|
|
|
|
Hi
While installing SQL Server 2008, it asks for Service account and account name, pls click: [^]
Here, what are the meanings of options: "NT AUTHORITY\SYSTEM", "NT AUTHORITY\NETWORK SERVICE" etc..?
Are these registry keys or something like that. Where can i find information about this stuff?
I don't even know which subject they are related and where i must start to search about these.
Ihanks...
|
|
|
|
|
A simple table:
create table temptable(userid int,username varchar(100),score int)
Values inserted:
insert into temptable values(1,'BOB',17)
insert into temptable values(1,'BOB',21)
Test:
select * from temptable where userid =1
Result:
1,BOB,17
1,BOB,21
Returns 2 rows. Fine.
The requirement: Find number of times BOB has scored above 20.
Expected result
1,BOB,1
My attempt:
select userid,username,count(*) from temptable where userid=1 group by userid,username
Here how do I specify a condition for the "Count()"? It just need to count score>20 for BOB.
I'm a rock bottom sql noob. Any help?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Try:
select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
|
|
|
|
|
ed5756 wrote: select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
 imagine if BOB doesn't have any record that has score over 20. It won't return any result at all. May be I should have stressed that point. In this case, the result should be like :
1,BOB,0
So I'm clear with my question now?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
OK, you want to return a 0 if there are no scores over 20. That is trickier.
This will return the count of Bob's scores over 20:
Select userid,username,count(*) as [count] from temptable where userid=1 and score>20 group by userid
This union query will return that same result and also a dummy record with a 0 count:
Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1
One would think then that using the MAX function in a sub-select would get your desired result:
select userid,username,max(ct) as
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1)
group by userid,username
But I find that in T-SQL for some reason it throws an error. However, if I first define a view based on that union, then this works:
select userid,username,max(ct) from view_c as Over20 group by userid, username
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
