I'm trying to calculate the % of people but it doesn't seem to be working. I get the following error:
ORA-00904: "ACT"."PAT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
What am I doing wrong?
WITH ENCOUNTERS AS
(
SELECT COUNT(*) AS TOT_COUNT
FROM
(
SELECT DISTINCT PATIENT.PAT_ID
[code]....
My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:
---------------------
Members
---------------------
ID | Goal
---------------------
---------------------
Donors
---------------------
Amount | MemberID
---------------------
I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:
CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN
All it does is return 0.
Here is the sample data .
Tradedate Type TotalOrdQty FillQty
2008-01-24 ATS 1392906.0 817965.0
2008-01-23 ATS 645306.0 469269.0
.... Goes on
Depending on the Type Column, I have to calculate the Percentage change in the TotalOrderQty and FillQty ( Daily , monthly , Yearly )
Is there a way to calculate the Percentage change with out using a
cursor.
Thanks
Venu
Hi,
I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:
QTY SUM OF QTY
0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)
What am I doing wrong?
Thanks in advance!
Isabelle
Hi All,
I have to create a table like this using SSRS.
LoanStatus NoOfLoans Amouunt Percentage
Started
1,000
1,000,000.00
100%
Sent to Partner
350
350,000.00
35%
Denied by Partner
50
50,000.00
5%
Accepted by Partner
300
30,000.00
30%
Loan Apps Pnd Disb
10
100,000.00
1%
Loan Apps Disbursed
290
290,000.00
29%
Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.
Please can anyone tell me how to calculate the grand total and Percentages?
Calculating % of Column Total in Charts...
Doing this exercise in Excel is always very simple:Â
Why i got "SqlException was unhandled by user code" "{"Incorrect syntax near '12'."}" ?
How can i only get people which is brithday is today?
The "rc_brithday" datatype is datetime
Thanks you
Code is below:
cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)
conPubs.Open()
dtrTitles = cmdSelect.ExecuteReader()
Currently, I have set up a SQL 7.0 database backup job and have one person notified if the job succeeds, but HOW do I choose an option to notify a different person if the job fails ?
Thank you in advance
hello,My friend and I are working on a project involving oracle 9i and an oldUnify database. We got the oracle to see the unify (oracle running onwindows) by using the heterogenous services on oracle.However, that was a test environment. The production environment hasoracle running on linux. The problem is that the odbc drivers for theold unify database are only available on windows!So, I was wondering about something.In the production environment, they also have a MS-SQL 2000 database.Does that database have a function similar to oracle's heterogenousservices that will let us connect to the old unify database and passthat data to oracle?We're building custom apps. on oracle 10g application server. Runningoracle on windows in the production environment is not going to happen.Thanks,Dave
View 2 Replies View Related
I have a report that need to be delivered to two different persons.
It has 10 columns. But one of them is not supposed to be seen by one person. Can I do this without creating another report and delivering two different reports although they are almost the same?
Thanks
Hi,I have a gridview on a page, which should select data from a database. I want it to select the data row if it finds its UserName in the UserName column, but there will be many usernames in the username column, seperated by commas.Here is the select statement that I have at the moment, which doesnt return any data atall:SELECT [message], [senddate], [subject], [messageid], [sendername], [recievername] FROM [Messages] WHERE ([recievername] LIKE '% ' + @recievername + ' %')@recievername = Profile("UserName") which is the vwd wizards way of saying the current user. A Username column has data like this:bezzer , bezlan , beezer , beezler with a space between commas and a space at the start and the end.Thanks if someone can help!Jon
View 2 Replies View RelatedHi Friends,
If I upload my Sql Server Database to a hosting company, can they see my tables?
Regards,
Is there a script or table that I can check to find out if there are any users who have BLANK passwords?
thanks
Is there a way to kick users out of your databases if there's no activity for a certain duration of time. For example, I have a database thats being shared by 5 people. One went to lunch and locked their office just when I needed to do maintanence. I had to wait for them to get back & logout be fore I could do anything?
View 1 Replies View RelatedCurious, what does one get out of going to a conference like PASS? (besides getting a free trip from work!).
I can see how it might be a place for consultants to mingle and potentially land new contracts. What other opportunities are there?
This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
View 16 Replies View RelatedI'm exporting a large file from a large Production database (SQL).Users are currently updating and inserting new records.Does the export take a snapshot of the data when it starts ? or willit freeze the database so all udpates/inserts happen after the exportcompletes?Also, how do I get the delta of data that wasn't exported, if I wantto get the difference the next day?Thank yoU!!!
View 4 Replies View RelatedI run a report which displays transcripts for members. One page per person with their name, courses they've took, date of the course and their score. The problem I am having is when the report runs and comes across the same last name for many people (say, Hanna and Billy Alexander), it's only giving me the first "Alexander" it comes across and it is putting all the information for the other "Alexander's" in that first transcript. How do I separate out the people who's last name is the same say "Smith" or Jones"?
I have a distinct clause on my query and when I run it I can see the other people with the same last name and their unique courses and scores (each of them have a unique member ID) - it's when I actually run the report that it groups the information (courses, dates, scores, etc) for the same last name people all under the first one it comes across. Hope that made sense.
Thx,
Billy
i have a report that is specifc to a user (has a user parameter)
is there a way to send out a dynamic subscription where the report is sent to the user "BOB" using "BOB" as a parameter, and a report that is sent to the user "Sue", uses the parameter "sue"
is this possible - i hope im making some sort of sense
thank you in advance
Jonny
Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:
Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles in article table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)
Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...
Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?
Thanks once again for taking the time.
Cheers,
Tyro
I'm developing a client-server application using Visual Basic and SQL Server
6.5 for 700 users. The application currently requires users to login to the
database using their network account (via trusted connections). Each of the
users takes up a maximum of 6 connections. Unfortunately, other
considerations will force us to discard use of trusted connections. So it
only leaves us either to create 700 user accounts on SQL Server separately
from their network logins or to create one SQL Server account and everybody
uses that same account to login to the database. For political reasons, the
customer would prefer not to ask their support group to maintain 700 user
accounts on SQL Server if it can be avoided.
My question: is there any technical limitation or other negative consequence
of having 700 users login to the same account to SQL Server if SQL Server
can handle that many connections (it would be 6*700 = 4200 connections)
simultaneously? Are we supposed to do things like this? It seems that we
don’t have better choice than this.
Hi all,
I'm having a problem working the SQL out for the following:
I have a table Person:
personId----------name
1----------------Robert
2----------------Frank
and another table Question
personId------questionId---------answer
1-------------1-----------------Y
1-------------2-----------------Y
1-------------3-----------------Y
2-------------1-----------------Y
2-------------2-----------------Y
2-------------3-----------------N
and I am trying to find a way of returning a list of ids of people who have answered Y to the first and second questions but N to the third.
If I try:
SELECT DISTINCT personId FROM Person INNER JOIN
Question ON question.personId = Person.personId
WHERE (Question.questionId = '1') AND (Question.answer = 'Y') AND (Question.questionId = '2') AND (Question.answer = 'Y') AND (Question.questionId = '3') AND (Question.answer = 'N')
I get no records returned.
I've tried putting brackets around the :
((Question.questionId = '1') AND (Question.answer = 'Y'))
bits of the Where clause - but the query analyser takes them away.
Any help would be greatly appreciated!
I have a set of rows in a table like for example
Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34
Now I want to use Row Number function or any way where I can get the result as below
Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2
I would like to replace mymail@mail.com by mypdl@mail.com , where mypdl is basically a people distribution list with 2 emails: my own email and my coworker and fellow DBA.
IF EXISTS
(
SELECT TOP 1 [PercentUsed]
FROM [BackupSize]
WHERE PercentUsed>=70 AND [drivename]='D:' -- D is the internal drive
ORDER BY [MetricDate] DESC
[Code] ...
But I think this won't work by design. Maybe I need to create an operator?
I am trying to write a report that gives me a list of all enquiries based on top 10 people who received it the most.
The fields are:
enquiry.enquiry_number
enquiry.enquiry_time
officer.officer_name
What I want to do is run the report which details each enquiry each officer has received but stop running once top 10 officers have the most enquiries are in the results.
The officers are identified using the officer.officer_name field, enquiry.enquiry_number field shows each enquiry record.
I am having a problem transforming a data set to the structure I need. I have data in the following format.
Id Visit1 Visit2 Visit3 Visit4
1 2Mar2010 27Mar2010 24Apr2010 8Jul2010
2 2Apr2010 3May2010 4Jun2010 11Jul2010
I need to transform it into a table which gives a count of the number of subjects at each visit at the end of each month.
For example, in March there will be 1 subject at visit 2. In April, there will be a total of 2 subjects, 1 at visit 1 and 1 at visit 3. In May there will be 2 subjects, 1 at visit 3 and 1 at visit 2, etc.
Here is the table I was hoping to produce.
Date.....Subjects.......Visit1.....Visit2.....Visit3.....Visit4
March....1.............................1
April......2...................1.........................1
May.......2.............................1..............1
Jun.......2............................................2
Jul.......2...........................................................2
SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
Does anyone know where I can get a Thesaurus (or list) of People's Names and their relationships?
For Example:
Bill = Billy = Billie = Will = Willie = William
Sue = Susie = Susan = Suzanne
I can build the needed xml file to become a Thesaurus for MSSQL Full Text Searching. But I can't find a list of related people names anywhere.
Any help would be great! Thanks!
Lyam
SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
Why is it that when you want to add a user or a group to SSRS through either Management Studio or the Reports Manager, you have no people picker to lookup into Active Directory.
You can pull up AD users and computers in MMC and do a search and then put their account name in - but that seems like a huge pain.
Am I missing something here - or did Microsoft miss something really big here.
I want to create a report which should be looked like this.
Members Joined
Members
Borrower
Depositor
Members Joined Yesterday
8
4
4
Members Joined Last 7 days
39
20
19
Members Joined Last 30 Days
243
120
122
Members
Borrower
Depositor
2007 Members Joined to Date
1,052
510
542
Q1-07
415
200
215
Q2-07
445
210
235
Q3-07 (To date)
192
100
92
All Members Joined
1,052
510
542
In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.
Example:
MembersTable
MemberName MemberID etc
LoansTable
MemberName CommencedDate
DepositorTable
MemberName CommencedDate
I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?
Please Can anyone give an idea? How to start and which direction I should go etc?
Thanks
Hi, I am using sql server 2005 entreprise edition. I am just wondering
when I have a report with parameters and the report uses stored
procedure to retrieve the result set for use in the report, if two
people from different places are trying to view the report at the same
time, and each of them use different set of parameters.
Will they be able to view their report correctly??
Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?