Making a sales,cost table equal for one year
select all sales from dbo where year = 2008
select all costs from dbo where year = 2008
but now i want to put beside this table
select all sales from dbo where year = 2009
select all costs from dbo where year = 2009
How can i achieve this in one table?
Is that possible to get missing years from the table column.
for example in table column i have something like this 2003,2004, 2005, 2006, 2008, 2010, 2011
so the result should have 2007,2009 (missing years)
I'm trying to pull a list of clients that have both of two codes in the code table. Here is what I have tried and it does not return any rows. I know that there are clients that have both codes.
[Code]....
I have to pull up a list of people by their branch number which could vary, and give a detailed report on there performance using a select queries. I have the select queries but how can i create a script to look similar to this one. Ive got the branch drop downlist already created just need to know how i can go about pulling all the users in this group and doing this select to all of them. someone told me I might need to use cursors to loop thru a select with every person in the list but im not sure how to go about that.
select fldusername from tbluser where fldio='o' and
flduserbranch=@branchid
is going to give me the names of the people i need to query. but how can i run a query on each of them seperate?
Here is the deal. I need to create a Stored Procedure that has 2 SELECT statements. Now in ASP.NET, I know that a DataSet can have multiple tables and the DataAdapter can do just that. I am stuck with a scenario where I need to get a value from the first SELECT statement which will later be used in the second SELECT statement. I am creating a SP that passes one parameter (@AnswerID). Which should give me 2 resultsets. See below SELECT statements.
[Code]....
So how do I get the QuestionID from the first SELECT statement?
Basically I am creating a Questionnaire that has Question Dependacies. So from the above SQL statements I would like to display the Question Title with it's relevant answer options but depending on the previous Answer selected hence the @AnswerID parameter.
The reason why I want to use 2 SELECT statements is because I don't want to have 2 roundtrips to th server. So in my code the DataAdapter should return 2 resultsets and fill the DataSet.
If there is any another solution that can prevent 2 roundtrips to the server, I would most definitely like to know how to do it.
Here's my SQL Statement
[Code]....
Unfortunately, I get the following error message:Message="The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
I have a select statement:
cmd = new SqlCommand("select lab_key from tblorderwhere", connection);
I would like to add a where statement that pass variable to the select statement. How I can do this?
Where lab_key =ChargeFine( that is my variable)
I am using join to get values from three tables. I want the first column to be Sl.No which autoincrements by 1.What should i write in the select for the first column.
View 4 RepliesIs it possible to sum to clolumns in one select statement?
[Code]....
[Code]....
ID sUM(aMOUNTpAID) sUM(aMOUNTdUE)
1234 506.64 4340.00
The actual rows look like this:
1234 10.00 5.00
1234 200.00 100.00
1234 130.00 130.00
1234 166.64 1000.00
totals 506.64 1235.00
i have the table like
TId DG rating DGMake DGCapacity
1 1 4 DG1 1X1
1 2 5 DG2 2X2
1 3 3 DG3 3X3
i need the output like this.
TId DG1 DG2 DG3 DG1Rating DG2Rating DG3Rating DG1Make DG2Make DG3Make DG1Capacity DG2Capaciy DG3Capacity
1 1 2 3 4 5 3 DG1 DG2 DG3 1X1 2X2 3X3
this might be an easy one, but I just can't get it.I am creating a page which will query a table with many columns and most items are not unique. I need to be able to get a list of records that match as many of the (up to 4) search criteria as possible.Example:am user searching for the following items, I enter at least one and up to 4 of the items below in a text box:Name, age, gender, weight (user may or may not fill in all of them).If he just enters "F" for gender, then he will get a list of thousands of females with their name, age, gender and weight.However if he enters "F" for gender and "300" for weight, he will get a much smaller list of returned records.I need to be able to create a sql statement that can perform that search with that functionality.
View 6 Replieshow can i use Distinct in my sql statement like that
SELECT
Distinct * from my_table ?? ? ? ?? ? ? ?
here is a sql select statement,
Select
ColA = ID
,ColB = Name
,ColC = invtotamt + (SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColD = ((SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColE = (SELECT SUM(TableB.camount) FROM TableB WHERE TableB.csucceed = 1 AND TableB.cID = InventoryTotal.invID GROUP BY TableB.cID)
FROM TABLE
it look complicated, and long statement, actually it's easy, it is like: (it's a sample, not a really one)
Select
ColA = ID
,ColB = Name
,ColC = ColD + ColE
,ColD = ColE + ColC
,ColE = ColC + ColD/100
FROM TABLE
it looks much easier with ALIAS. But, it seems i cannot directly use alias in select stetement like that.
if i cannot use alise, i have to copy paste lots of same column definition. how can i use the alias in select statement?? this bothers me a lots of times!
I used an function in select statement. the select return 3 rows but the function return same value in this 3 rows.
[Code]....
creating a dynamic select statement, so far my SP look like below
[code]....
SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script. There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table). I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site. So how can I adjust that SELECT statement so this one is combined with it somehow?:
SELECT Email FROM ExtraEmails WHERE Rules = 'true'
I have two sections of users data on my site, customers and employees, each one has a UserID that is FK to the UserID from aspnet_Users table.
I have a table that stores User messages
Looks Like This:
[URL]
It Has a FromUserID and ToUserID
Because Employees are Identified through EmployeeID and Customers are identified through CustomerID, I needed to use UserID's so Employees can leave messages for employees and Customers and Customers can leave messages to employees and other Customers 1-2 and 2-1
My Quandry is this, How Do I retrieve the information if the UserID might be a Customer or Might be an Employee, when the UserID could be from one of two tables
ie
How Would I Write?
SELECT wms.FromUserID, ed.FirstName, ed.LastName FROM ed INNER JOIN wms ON wms.FromUserID = ed.UserID
OR SELECT wms.FromUserID, cd.FirstName, cd.LastName FROM cd INNER JOIN wms ON wms.FromUserID = cd.UserID
How Do I Check to See? Do I Use IF EXISTS?
My table as 3*3 :
x y z
p q r
s t u
my question is :
the table first row middle value can .. like that below
y x y z
y p q r
y s t u
I need to execute a stored procedure and show the value it returns as a usertype column in a selection list. Eventually this select statment will be converted into a stored procedure, so basically the stored procedure will be called in another stored procedure. I can't quite get the syntax correct. I am showing a simplified version of a very long query so it's easy to understand. It gives me the 'Incorrect syntax near the keyword 'CREATE' error. The capitalized the code produces the error:
SELECT createdate,
createdby,
usercity,
userstate,
[Code]....
Can I not have a create table statement inside a select statement? I can't move it out of the select statement since
I need to pass the city and state as parameters which are inside the select statement. is there any better way to do this functionality?
I need to select some rows from a table and apply a distinct on two of the columns (keeping the most recent one) and get all the attributes of the selected rows.for example ,if I have a table with the following rows and coloumns
ID
A
B
DATE
1
1
1
1/1/2010
I need to do a select distinct on columns A and B keeping the most recent row and return all the coloumns of the selected rows so i need the result to be rows 2 and 3 and discard row 1 because there is another row (2) having the same data in columns A and B and the other row (2) is more recent.I tryed creating a veiw, use a common table expression and create a stored procedure...but my knowledge with sql is limited and cannot get the result i want...
Is there a way of getting that result without having to change the database structure?
sql_select = "SELECT auid FROM tbl_auAccount WHERE username='" & IUN & "'"
'Add Error Trapping Later
con = Server.CreateObject("ADODB.Connection")
con.Open(data_source)
'Execute the SQL Command
con.Execute(sql_select)
'Close the connection
con.Close()
con = Nothing
Now this works and I was able to put the value into a dropdown list box, but I need the value returned placed into a label such as:
lblShowNewUserID.Text = ? (? being the value returned from the Query). I thought this would be simple, but it is not (at least for a newbie like me). NOTE: (I will have instructions before this to be sure that only one value will be returned and taht the value will not be null).
I have this select statement which joins two tables and produces one column as its result. What I'm figuring out is how to add this data into another table as a new column. The output of the select statement and the table I'm going to transfer the data to has the same number of rows. Should I put it first in a temporary table? This column shouldn't allow nulls also.
View 6 RepliesI'm trying to use a function in my code behind to let me select a different column each time by calling the function with a different parameter.
However, I keep being told 'invalid column name', even though in the error page the column name appears perfectly valid.
Private Function getData(ByVal s As String) As String
i want to pass sql select statement dynamically from asp.net web page to report server.Depending upon this report has to be generated.
View 1 RepliesI am trying to track members volunteering. I have a table that has all our members information and another table I want to update what they volunteer for throughout the year. I have a ddl that they select a last name and it populates first name, last name and spouse in a formview. This all works. There are a couple of other textboxes that need to be filled out. When I try and run an update into the volunteer table I get no errors and page shows the meber was updated. However, when I go into the volunteer table, there is no data.
[Code]....
I don't understand why it is not updating into the table. Especially since I am getting no errors.
