This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. declare @Something … Read more
It looks like vb code if you want c# code then use following code cmd = new SqlCommand(“select name from wq where id='” + TextBox1.Text + “‘”, con); con.Open(); dr = cmd.ExecuteReader(); dr.Read(); TextBox2.Text = dr[0].ToString(); dr.Close(); con.Close(); 7 solved Trouble writing a select query [closed]
If I have two columns, how can I select all distinct values of columnA where columnB is never a specific value given that columnA is the same value? solved If I have two columns, how can I select all distinct values of columnA where columnB is never a specific value given that columnA is the … Read more
//Example with mutliple coordinates before creating an arc. AREA DEFINED AS 133830N1450807E TO 132836N1444449E TO 133043N1443814E TO 133515N1443710E THEN CLOCKWISE ON A 15.3 NM ARC CENTERED ON 133416N1445256E TO THE POINT OF ORIGIN //Abbreviation // a // b // m(midangle) (cx,cy,ax,ay,bx,by) // x(lat) // y(long) //Xc=latitude provided in text for center point //Yc=longitude provided in … Read more
Nobody found the answer. But it is easier to downvote than solving code issues ! So, here is the answer, i hope it will help other guys who encounter the same problem. 1 / Assuming you build a query which creates a table with custom columns named “Name, State, Nb, Avg, Min, Max : string … Read more
Lets work backwards here – your stored proc will never work, you have not specified a field for the where, and it has 2 missing close parentheses. select * from MyTable where between CAST(@startDate AS VARCHAR(100) and CAST(@EndDateAS VARCHAR(100) should be select * from MyTable where SOMEFIELD between CAST(@startDate AS VARCHAR(100)) and CAST(@EndDateAS VARCHAR(100)) In … Read more
Take a look in syscomments for the text and join to sysobjects for the object type and name sysobjects syscomments solved How can I find all occurrences of particular text within all stored procedures in my database? [closed]
Like this? It seems easiest just to do it as three separate statements. DELETE FROM Products WHERE SubCatId IN (SELECT SubCatID FROM SubCategory WHERE MainCatId = @mainCatId); DELETE FROM SubCategory WHERE MainCatId = @mainCatId; DELETE FROM MainCategory WHERE MainCatId = @mainCatId; 8 solved Delete query to delete data from multiple tables in sql server and … Read more
A subquery in the SELECT can only select one attribute. I’m not sure what you are trying to do, but you’ll have to re-write to something like: SELECT NUMBER , STATUS , ID , SUM(CASE WHEN STATUS like ‘closed’ and OPEN_TIME >= @DateFrom and CLOSE_TIME <= @DateTo THEN 1 ELSE 0 END) as closen , … Read more
The method I have used here is a Tally Table to create extra rows. I JOIN onto the tally table where the number of hours / 24 (integer maths is useful) is greater than the tally number, and then can use that to calculate the hours. WITH YourTable AS( SELECT * FROM (VALUES(1,CONVERT(date,’2018/01/24′,111),4 ), (2,CONVERT(date,’2018/03/21′,111),40), … Read more
Try following xml linq. I put your input file into a text file and then read into DataTable. Then create XML from table : using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml; using System.Xml.Linq; using System.Data; using System.IO; namespace ConsoleApplication1 { class Program { const string INPUT_FILENAME = @”c:\temp\test.txt”; const string OUTPUT_FILENAME = … Read more
This meets the stated requirement (last full week of previous two months): SET DATEFIRST 1; DECLARE @s DATE = GETDATE(), @s1 DATE, @s2 DATE; SET @s = GETDATE(); — last day of last month: SET @s1 = DATEADD(DAY, -DAY(@s), @s); — last day of previous month: SET @s = DATEADD(MONTH, -1, @s); SET @s2 = … Read more
You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 – 2) month of your results with totals that are too large because they actually represent a combination of two calendar months. It worked in your … Read more
Using CASE Condition and Row_number we can achieve the above Output It’s Purely based on your sample Data DECLARE @Table1 TABLE (projName varchar(1), percentage int) ; INSERT INTO @Table1 (projName, percentage) VALUES (‘A’, 10), (‘A’, 25), (‘B’, 20), (‘B’, 30) ; Select CASE WHEN RN = 1 THEN projName ELSE NULL END projName, percentage from … Read more
At least immediately, I notice you are missing the end: create procedure new_SP ( @tablename varchar(50) ) as begin declare @xxx varchar(50) set @xxx= ‘create table ‘+@tablename+'( name varchar (50))’ print @xxx exec (@xxx) end — you are missing this If you add the END, then this should work. Just tested it in SQL Server … Read more