SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson) Table2 (tblTitles) 1, "John", "Miller", "Employee; Admin; Consultant" 2, "Joan", "Stevens", "Employee, Software Engineer, Consultant" and so on .... 1 in select statement:
then I try it like this : exec proc_add_comp 'new answer','by me',1
result is : (1 row(s) affected)
but when I look in the db, nothing was changed, comp_answer still has its old value.. comp_answer is nvarchar type column..isnt add operation allowed in update statement? thanks...
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986 2nd result : 17037903 3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?
The original data is from an MS SQL database to be transferred to a PostgreSQL database.
MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)
I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks, Tom Anderson Software Engineer Custom Business Solutions
Hi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work ALTER PROCEDURE dbo.UpdateStock @OrderID intASSELECT (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE (dbo.OrderDetails.OrderID = @OrderID) UPDATE dbo.Products SET dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the OrderDetails table the UPDATE the Stock feild in the Products Table by deducting the OrderDetails.Quantity From the Products.Stock to give me the new stock levelWell it works to an extent in that if I do this to SET dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks
I am unable to update the data using record by record below scenario.
Required output:
patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM (SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub) , FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
1 john john smith smith 1 john j smith smith 1 john jon smith smit
I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...
TRUNCATE TABLE [Temp_Export]; INSERT INTO [Temp_Export] (
[Code]....
The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd)
'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1 FROM dbo.ReportTable INNER JOIN dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget HAVING (dbo.ReportTable.Budget < 1) ORDER BY dbo.ReportTable.ProjectNo
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update mklopt set  FRMDAT = '12/31/2014' where  JOBCOD = 'PH14789'Â
I also want to include the following instead of running it one at a time
PH17523Â Â Â Â PH17524Â Â Â Â PH17525Â Â Â Â PH17553Â Â Â Â PH17555Â Â Â Â PH17556Â Â Â Â PH17557Â Â Â Â PH17558Â Â Â Â PH17571Â Â Â Â PH17573Â Â Â Â PH17574Â Â Â Â PH17575Â Â Â Â PH17576Â Â Â Â PH17577Â Â Â Â PH1757
Hi all, I want to save DBCC's result into a table to execute my statement but I don't know how to, exmple for DBCC OPENTRAN (my database) - how can I put SPID & UID to my variable from this statement? Thanks in advance Happy New Year PTH
I have a requirement to mark duplicate records when I pull them from the database. However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one. The code I have below creates a column called Dupes but marks all the duplicates - including the first one. Is there a way to only mark the 2nd, 3rd, 4th etc record ? SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber, Dupes = (select count(id) from CallLogswhere (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21), FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE people_id (primary key) first_name last_name
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1 Comment #1 by Person #1 Comment #2 by Person #3 Comment #3 by Person #8
Project #2 Comment #1 by Person #2 Comment #2 by Person #3 Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.
Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK
I want to use the returned table name and update the set eh FK's value = null.
Some how it is not working , can some one point me out on correct solution.
I have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:
Normally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.The select statement is as follows:
IF OBJECT_ID('tempdb..#trn') IS NOT NULL DROP TABLE #trn
IF OBJECT_ID('tempdb..#mv') IS NOT NULL DROP TABLE #mv SELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn, CASE PTR_TAC WHEN 'BUY' THEN 0 ELSE PTR_LOCALAMT END as amt_trn
