Mgiyuk

Joining Two Subqueries That Contain A Subquery Still new to SQL. I have looked around for an answer on this, and the examples that I have found, I have mirrored my query to look like the examples, and this query still isn't working. All Im trying to do is join two subqueries, but both of these subqueries contain a subquery inside. I'm receiving the following error: Msg 156, Level 15, State 1, Line 26 Incorrect syntax near the keyword 'On'. This is the query, please assist, thank you. Select * From ( Select * From( Select x.ID, x.Date, x.USOHist, x.OVXHist, Abs(Cast((((x.USOHist / NullIf((y.USOHist),0))-1)*100) as Decimal(10,2))) AS '%USOH', Abs(Cast((((x.OVXHist / NullIf((y.OVXHist),0))-1)*100) as Decimal(10,2))) AS '%OVXH' From (Select a.Date as aDate, Max(b.Date) As aPrevDate From USO_OVX_Hist a Inner Join USO_OVX_Hist b on a.Date > b.Date Group By a.Date) Sub1 Inner Join USO_OVX_Hist x on Sub1.aDate = x.Date Inner Join USO_OVX_Hist...

SQL Server add all rows where a condition is validate I have a SQL Server database in which I need to add all the cost for a job family. I have a table like this Table : work +-------+-----------+-----------+---------+ | wonum | cost1 | cost2 | wogroup | +-------+-----------+-----------+---------+ | 1 | 30.12 | 157.14 | 1 | | 2 | 110.10 | 0.00 | 1 | | 3 | 12.67 | 45.45 | 1 | | 4 | 0.00 | 0.00 | 4 | | 5 | 400.00 | 11.54 | 4 | +-------+-----------+-----------+---------+ I need to add cost1 and cost2 for all the row who have the same wogroup but only for the on where wonum = wogroup . wogroup where wonum = wogroup Like this +-------+-----------+-----------+---------+---------+ | wonum | cost1 | cost2 | wogroup | total | +-------+-----------+-----------+---------+---------+ | 1 | 30.12 | 157.14 | 1 | 355.48 | | 2 | 110.10 | 0.00 | 1 | null | | ...

Create Index equivalent from Oracle to SQL Server I have a current index that I have pulled from Oracle and I am trying to add to SQL Server. As far as I can tell, I will have to remove the double quotes and use WITH(... However, I cannot find the Maxtrans and other keywords equivalent in SQL Server, especially the M_ROW$$ , which I was thinking it would be the ROW_NUMBER . WITH(... M_ROW$$ ROW_NUMBER CREATE UNIQUE INDEX ""MYDB"".""I_SNAP$MYTABLE"" ON ""MYDB"".""MYTABLE"" (""M_ROW$$"") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""IL_MIS_INDX"" Any help will be greatly appreciated. Thank you! M_ROW$$ is the name of a column o...

Query to Output SQL table to Excel or CSV I have been trying to export my table to a csv through a SQL query and no luck. The attempt was using the OUTPUT function but I got an error. Below is the query: SELECT * FROM [table]; OUTPUT TO 'C:foldernew_data.csv' FORMAT TEXT QUOTE '"' WITH COLUMN NAMES; This was the error: Msg 102, Level 15, State 1, Line 81 Incorrect syntax near 'OUTPUT'. Msg 319, Level 15, State 1, Line 84 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Am I not using the OUTPUT function correctly and is there an alternative query in SQL? the ";" after the SELECT terminates the statement. So you have two statements here: SELECT ...; and OUTPUT TO ...; Remove the semicolon after the select and see if that works. docs.microsoft...

SQL Server DAC columns and rows from TSqlObject I am trying to write a T4 Template that generates triggers for all my tables in my database based on extended properties using Microsoft.SqlServer.Dac.Model , but I am very confused how everything works in this namespace. Microsoft.SqlServer.Dac.Model I use: Model.GetObjects(DacQueryScopes.All, ModelSchema.ExtendedProperty) to get a list of all the tables in the database model schema but I cannot for the life of me figure out how I am supposed to get information such as column names from the returned table objects. Possible solution here: stackoverflow.com/questions/17983499/how-to-traverse-a-dacpac – Matthew Alltop Jun 29 at 18:19 By clicking "Post Your Answer", you acknowledge that you have ...

How do I give an SqlConnection a string as the AttachDbFilename? I'd like to have a string be used as the AttachDbFilename in my SqlConnection . AttachDbFilename SqlConnection However when I feed it a string it doesn't work and just gives me a null or invalid connection/failure. How do I properly provide AttachDbFilename as a string value in place of the normal set path? AttachDbFilename DialogResult result = openFileDialog1.ShowDialog(); if (result == DialogResult.OK) { string databasePath = openFileDialog1.InitialDirectory + openFileDialog1.FileName; SqlConnection dataBaseConnection = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=databasePath;Integrated Security=True;Connect Timeout=30;User Instance=True"); } 1 Answer 1 You have databasePath inside the string literal so right now you are trying to attach with a Db Filename of "databasePath", wh...

SQL Update not working for primary key I write a statement for updating details belongs to primary key(Mobile). but it is working only for other columns. when i update mobile number. it doesn't change. Here the my query private void button4_Click(object sender, EventArgs e) { con.Open(); SqlCommand cmd = new SqlCommand(@"UPDATE [dbo].[Table] SET [First] = '"+textBox1.Text+"',[Last] = '"+textBox2.Text+ "' ,[Mobile] = '" +textBox3.Text+ "' ,[Email] = '" +textBox4.Text+ "' ,[Category] = '" + comboBox1.Text + "' WHERE (Mobile='" + textBox3.Text + "')", con); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Updated Successfully"); display(); } These following columns can be updated First, Last, Email, Category but Mobile column cannot be updated. Mobile CREATE TABLE [dbo].[Table] ( [First] VARCHAR(50) NOT NULL , [Last] VA...

Two Tables with cascade delete I have two tables: [tbl_Records_DR] PK DAutoID. [tbl_Records_DR_Explain] PK DRefer_ID I'm creating my tables programmatically (only). Here's what I currently have that works well creating the tables: CREATE TABLE [tbl_Records_DR] ( " _ & "[DAutoID] int IDENTITY (1,1) PRIMARY KEY NOT NULL " _ & ", [Hub_Unique_Name] nvarchar(30) DEFAULT '' NULL " _ & ", [DStatus] nvarchar(30) DEFAULT '' NULL " _ & ", [DDate] datetime NULL " _ & ", [DTime_Start] datetime NULL " _ & ", [ReportCreatedFrom] nvarchar(5) DEFAULT 'DR' NULL);") CREATE TABLE [tbl_Records_DR_Explain] ( " _ & "[AutoID] int IDENTITY (1,1) NOT NULL " _ & ", [DRefer_ID] int PRIMARY KEY NOT NULL " _ & ", [DExplain] nvarchar(700) DEFAULT '' NULL " _ & ", [ImportCompleted] bit DEFAULT 0 NULL;") Thanks, ADawn ...

Convert specific BigInt to DateTime in T-SQL I have bigInt: 635307578922100000 which I need to convert to DateTime . 635307578922100000 DateTime I've tried few ways to do this: SELECT DATEADD(S, CONVERT(bigint,635307578922100000) / 1000, CONVERT(DATETIME, '1-1-1970 00:00:00')) and: SELECT DATEADD(ms, 635307578922100000 / 86400000, (635307578922100000 / 86400000) +25567) While I found the codes above work with bigInts like: 1283174502729 , with my bigInt I get the following error: bigInts 1283174502729 Msg 8115 ... Arithmetic overflow error converting expression to data type datetime. Does anyone have any idea how to solve it? What would be your expected result for the given input '635307578922100000' – StackUser Jul 26 '16 at 8:12 It looks like these are ticks. Possible dupli...

SQL Filter numbers between x and x and ignore strings I have a table in SQL Server where I need to filter rooms by name and type. The problem is, that the names are stored as varchar and there are also some rooms with letters. I need to filter out the rooms with letters before I can compare them as int or otherwhise I will get an error. varchar int Here's an example from Room.Name: 030 210a 210b Lan-Room-A 240 I can work around the room names with a or b with LEFT(Rooms.Name, 3) but if I want to add (LEFT(Rooms.Name, 3) BETWEEN 0 and 350 and it gets to Lan-Room-A it oviously can't convert a string to int. I also need to do additional filtering like Room.Type = 6 for example. a b LEFT(Rooms.Name, 3) (LEFT(Rooms.Name, 3) BETWEEN 0 and 350 Lan-Room-A Room.Type = 6 SELECT Room.Name, Room.Descr, Room.MainUser WHERE LEFT(Room.Name, 1) NOT LIKE '%[0-9]%' AND LEFT(Room.Name, 3) BETWEEN 0 AND 350 AND Room.Type = 6 (Removed some joins for simplici...

Add JSON Valuetypes to XML Nodes using SQL I have the below XML output generated using SQL query(added in the rextester link): <Main xmlns:json="http://www.samplenamespace.com/json"> <ID>1001</ID> <details> <name>John</name> <age>12</age> </details> </Main> I want to know how to add a namespace xmlns:json="http://www.samplenamespace.com/json" to the 'Main' node. xmlns:json="http://www.samplenamespace.com/json" desired outcome: <Main xmlns:json="http://www.samplenamespace.com/json"> <ID json:ValueType="Number">1001</ID> <details> <name>John</name> <age>12</age> </details> </Main> rextester link: http://rextester.com/FAV86925 any help?! 1 Answer 1 I don't know if I've got this correctly, but this...

fetch score from previous rank belonging to another student I'm trying to to fetch score from previous rank belonging to another student for every row in the following select statement. Now, I'd like to have the Score of previous Rank in each GroupCode for every CourseCode and StudentCode. SELECT StudentCode, CourseCode,GroupCode, Score, StudentRank FROM Table my table data Sample data and expected output would help – Ajay Gupta Jun 29 at 8:57 Could you post some sample data and expected results here please? Have you looked into LAG as well? – Larnu Jun 29 at 8:57 LAG If you're using SQL Server 2005 express, I recommend you consider upgrading. Unfortunat...

How can i get a range of number of characters in SQL Server using wildcard characters I have a query where i need to filter the value whether it has 6 or 7 or 8 or 9 characters not more than that. I know this SELECT * FROM TABLE_NAME WHERE col LIKE '______' OR col LIKE '_______' and so on.. Is there a general way to filter the number of characters. what if it varies a long range? Do i need to put this OR for that much conditions select * from table where len(col) between 6 and 9 – Chanukya Jun 29 at 8:54 This sounds like an XY Problem. You have a problem X and think that filtering by length (Y) is the solution. When Y doesn't work you ask about Y, not the actual problem X. Why do you need to do that at all? Such a query can't use indexes and be forced to scan all ro...

How to join SQL statements with different where clause on different column of same table I have 3 queries in sql: 1st query: select t1ID ,AVG(t2score) AS AVG1 from T1 WHERE t1m1 NOT IN (t2m1,t2m2,t2m3) and t1m2 IN (t2m1,t2m2,t2m3) group by t1ID Result +------+------+ | t1ID | AVG1 | +------+------+ | 1 | 55 | | 2 | 45 | | 3 | 73 | | 4 | 69 | +------+------+ 2nd query : select t1ID ,AVG(t2score) AS AVG2 from T1 WHERE t1m2 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3) group by t1ID Result +------+------+ | t1ID | AVG2 | +------+------+ | 1 | 68 | | 2 | 56 | | 3 | NULL | | 4 | NULL | +------+------+ 3rd query select t1ID ,AVG(t2score) AS AVGt3 from T1 WHERE t1m3 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3) group by t1ID Result +------+------+ | t1ID | AVG3 | +------+------+ | 1 | NULL | | 2 | 70 | | 3 | NULL | | 4 | NULL | +------+------+ How can I combine these three statements so that I get these results added together lik...

Hierarchical update query for the tree structure data in SQL create table #tmp(ChildID nvarchar(50),ParentID nvarchar(50), Percentage numeric(8,2)) insert into #tmp values ('1', NULL,NULL) insert into #tmp values ('1.1', '1',89) insert into #tmp values ('1.2', '1',NULL) insert into #tmp values ('1.2.1','1.2',96) insert into #tmp values ('1.2.2', '1.2',NULL) insert into #tmp values ('1.2.2.1', '1.2.2',87) insert into #tmp values ('1.2.2.2', '1.2.2',NULL) insert into #tmp values ('1.2.2.2.1', '1.2.2.2',87) I am trying CTE update but couldn't attack the expected result yet. Can some one help in this. CTE You have just given the input and expected output. Can you also add some explanation of what you are actually trying to accomplish? – Giorgos Betsos 2 days ago ...

Reflect sum of column to a label on a WinForm I want to reflect the sum of a column to a label on a WinForm. I don't know what's wrong with this code: private void btnT_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection("data source = TURKY-PC ; initial catalog = coffeeshopDB ; integrated security = true ; "); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand("select SUM (cost) from billTB", cn); cn.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { btnT.Text = dr["cost"].ToString(); } dr.Close(); cn.Close(); } The error exception that appears is: System.IndexOutOfRangeException: 'cost' System.IndexOutOfRangeException: 'cost' 1 Answer 1 If you're going to refer to the column by name,...