Hi, I have received a text file in the following format: MthYear Customer Quantity Price Total Apr2003 Allan 100 5 500 --------- Austin 25 2 50 --------- George 1500 1 1500 ---------- Jessy 200 2 400 Apr2004 Jerry 600 3 1800 --------- Stella 250 2 500 June2005 XXXX 50 5 250
I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie The MthYear field for Austin, George,Jessy should be updated with Apr2003, The MthYear field for Stella should be updated with Apr2004 iiily, for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.
So I have code to fill my repeater with a data: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("db_ConnStr").ConnectionString) Dim cmd1 As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id INNER JOIN forum_posts AS forum_posts_1 ON aspnet_Users.uID = forum_posts_1.user_id GROUP BY aspnet_Users.UserName, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date HAVING (forum_posts_1.topic_id = @topic_id) ORDER BY forum_posts_1.post_date ASC", cnn) cmd1.SelectCommand.Parameters.AddWithValue("@topic_id", Request.QueryString("ID")) Dim ds As Data.DataSet = New Data.DataSet cnn.Open() cmd1.Fill(ds, "posts") Repeater1.DataSource = ds.Tables("posts") Page.DataBind() cnn.Close() End Sub And I want to take the value from column which I bolded - IlePost. I want take this out at compare with some condition. For example:Dim label as label = Repeater1.Findcontrol("Label1")If COLUMN < 10 thenlabel.text = "Less than 10"elselabel.text = "More than 10"I hope you understand me :-)
Table Name:-emp table structure:- Name Gender A Male B Male C FeMale D FeMale I want only one update query to update this table like this table structure:- Name Gender A FeMale B FeMale C Male D Male please help me?
Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11 category_child12 category_child13 category_child23 category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul
I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.
Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply
Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai
I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table. If any one have solution, then please help me.
hello i am a beginner i it possible to get this result by single query ?
i got a int column and a datetime column is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony
I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.
I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.
I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.
For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.
So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).
I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:
I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:
L M H ----------------------- 3 2 1
(L are values below 4, M between 4 and 5, H is 6 and above)
I need to write a single query that will append the values from one table into another table but I need to update a single field in the table with a static value. What I'm working with is an Access .adp with an SQL 2000 backend. The database is used to track ticket sales, payments, and contact info for season ticket holders. Prior to upsizing, each year the database would be copied and then choice bits modified for the next year.
In other words, the history was in several databases, i.e. db2001, db2002, db2003 and for the current year dbcurrent. I decided to create a historic tables in the current DB rather than have umpteen DBs on my SQL server.
My problem is that I need to create a query that appends to my table the previous years data and then updates the season field to reflect the season that the data came from.
In short, say I have a table named accounts with fields account, customer, addr1, addr2, ..., ticket type and I want it to end up in account_hist with fields account, customer, addr1, addr2, ..., ticket type, SEASON and make the season equal say 8 which represents the 8th season the team has played. I can get both queries to work seperately :D , but for end user ease, I want to perform both actions at the same time :confused: . Can anybody point me in the right direction? Thanks
I am using 3 tables: projects, project_manager and project_employee
projects
- project_id (int, PK) - project_name
project_manager
- project_id (int, PK) - manager_id (int, PK)
project_employee
- project_id (int, PK) - employee_id (int, PK)
Assume that a manager is currently logged in (so we know what is his ID), what I trying to do is write a query that will display a grid-view showing:
project_id, project_name and number of employees in project.
I tried these quires to test:
SELECT project_id, count(emp_id) as NumberOfEmployees into #tempTable FROM project_employee WHERE project_id IN (SELECT project_id FrOM projects) GROUP BY project_id
SELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_id
Problems are: 1 - I used two queries not one 2 - The result is for all projects of all managers, I just want to display projects for a specific manager (for example mag_id = 5) 3 - it created a temporary table - which I dont want.
Is there anyway to get what I want in a single query?
I'm trying to pass through a SQL statement to an Oracle database usingOPENROWSET. My problem is that I'm not sure of the exact syntax I needto use when the SQL statement itself contains single quotes.Unfortunately, OPENROWSET doesn't allow me to use parameters so I can'tget around the problem by assigning the SQL statement to a parameter oftype varchar or nvarchar as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',@chvSQL)I tried doubling the single quotes as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword','SELECT *FROM AWHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')')But that didn't work. Is there a way out of this?Thanks,Bill E.Hollywood, FL
I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value).
I'm storing id with comma (,) separator. Below is my asp.net code:
string sId=string.Empty;
for(int a=0;a<Id.Count;a++)
{
Id += Id;
if(a!=Id.Count-1)
sId +=",";
}
In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar.
Plz help me create Stored Procedure in SQL 2005 based on above scenario.
Email    | CC_Flag xxxx          0 yyyy          1 zzzz          1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag. Is it possible to fill two variables from a single SQl Task.
Hi I have a table which has couple of name cols and 10 varchar columns. The requirement was to retain the first two cols but convert the 10 cols into a single XMl.
and this query to convert all the varchar cols to a XML. Now the problem is I am not sure of how to combine both these queries to Insert into the new table.
I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.
I want to write more than one insert query in single store procedure. or Is it possible to insert a row into multiple tables with a single insert into query. Thank You.
Hi All, I am facing quotes problem. Without using the quotes my query is running fine, but I need to use IIF condition so for that I need quotes adjustment. I didn't figured it out how to adjust them, try several techniques but no success. I am using dotnetnuke. {IIF,"[frmradio,form]=text"," SELECT Docs.FileName, Dept_LegalLaw.MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, Dept_LegalLaw.LawDate, Dept_LegalLaw.Agreement, Dept_LegalLaw.Name, Dept_LegalLaw.NameSearch, Dept_LegalLawType.LawType, Dept_LegalLaw.LawNo, Dept_LegalMinistries.RegID, Dept_LegalLaw.IssueNo, Dept_LegalLaw.Attachment, Dept_LegalLaw.Amendment, Dept_LegalLaw.Scanned, Dept_LegalLaw.Html, Dept_LegalMinistries.Description FROM OPENQUERY(LEGALDBSERVER, 'SELECT Filename FROM SCOPE() WHERE Contains('" @FilterAnyWrd ")' ) AS Docs INNER JOIN Dept_LegalLaw ON Docs.FileName = Dept_LegalLaw.FileName INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID ", " "} {IIF,"'[frmradio,form]'='title'"," SELECT MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, LawDate, Agreement, Name, NameSearch, Dept_LegalLawType.LawType, LawNo, Dept_LegalMinistries.RegID, IssueNo, Attachment, Amendment, Scanned, Html, Dept_LegalMinistries.Description, Dept_LegalLaw.FileName FROM Dept_LegalLaw LEFT JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID COLLATE DATABASE_DEFAULT = Dept_LegalMinistries.RegID COLLATE DATABASE_DEFAULT INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID COLLATE DATABASE_DEFAULT = Dept_LegalLawType.LawID COLLATE DATABASE_DEFAULT WHERE @FilterLawNo AND @FilterLawID AND @FilterRegID AND @FilterIssueNo AND @FilterFromDate AND @FilterToDate AND @FilterNtContNew AND @FilterAgreement AND @FilterAllWrdNew AND @FilterExWrdNew AND @FilterAnyWrdNew ORDER BY [SORTTAG] ", " "} Thanks for any help
