sql-server

[Solved] Select preferred address if exists otherwise select ‘work’ address

by

This should do: SELECT * FROM peopleaffiliation pa LEFT OUTER JOIN addresses addr ON pa.addressid = addr.addressid LEFT OUTER JOIN addresstypes addtyp ON addtyp.addresstypeid = addr.addresstypeid WHERE (addr.preferredaddress = 1 ) OR( COALESCE(addr.preferredaddress, 0) = 0 AND addtyp.addresstypeconst=”Work” ) 0 solved Select preferred address if exists otherwise select ‘work’ address

[Solved] How to swap rows in SQL Server

by

Put the original values in a temporary table with the IDs swapped then join to the temporary table whilst updating the table, example code below: –drop temp table if exists IF OBJECT_ID(‘tempdb..#Temp’, ‘U’) IS NOT NULL DROP TABLE #Temp –need to store the original values SELECT *,CASE WHEN Room_Number=9104 then 9103 ELSE 9104 END AS … Read more

[Solved] Display table values in textboxes [closed]

by

try something like this…. using (SqlConnection con = new SqlConnection(strConnect)) { con.Open(); using (SqlCommand com = new SqlCommand(“SELECT FirstName, LastName, EmailId, MobileNUmber FROM myTable WHERE id=@ID”, con)) { com.Parameters.AddWithValue(“@ID”, iD); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(com); da.Fill(dt); txtFirstName.Text = dt.Rows[0][“FirstName”]; txtName.Text = dt.Rows[0][“LastName”]; } } 3 solved Display table values in … Read more

[Solved] SELECT statement with if ELSE in SQL Server

by

If I understood your question correctly, you want distinct ref number with doc number is the following condition (as per the output shown). If the all the existing doc numbers are same for a ref number, then get max(doc) otherwise sum(doc). SELECT ref, (CASE WHEN MAX(doc) = MIN(doc) THEN MAX(doc) ELSE SUM(doc) END) AS doc … Read more

[Solved] Can’t update decimal field in mssql

by

The problem was that I called the ExecuteAsync method inside multithreading and the thread was closed before the ExecuteAsync method returned the result. I fixed it by replacing the ExecuteAsync method with the Execute method. The .Wait() didn’t help nor the getawaiter. 1 solved Can’t update decimal field in mssql

[Solved] SQL Server constraint [closed]

by

As I wrote in my comment, I would not set the existing value to null. Instead, a computed column seems like a better option to me. Also, varchar(1) is the second worst data type you can choose (nvarchar(1) is even worst). First, if you know you only ever going to have a fixed length string, … Read more

[Solved] Hackerrank SQL challenge

by

Since you want first and last, I’d probably just use a union and top 1. makes it clear as to what you’re after and easy to maintain. And since you can use alias in order by… I’d alias len(city) SELECT TOP 1 city, len(city) LenCity FROM station ORDER BY LenCity ASC UNION ALL SELECT TOP … Read more

[Solved] SQL query for formatted output [closed]

by

I assumed Time column Value are fixed and that are 1,3,5,7. Below Query is similar to what you wanted to achieve. select distinct T.id,T.iMid, (select amount from myTable where time = 1 and id = T.id and iMid = T.iMid) as Time1, (select amount from myTable where time = 3 and id = T.id and … Read more

[Solved] SQL Server Transpose Rows Into New Table

by

I figured it out. See below for the code. INSERT INTO Results (eventID,Result1,Result2,Result3,Result4,Result5) SELECT eventID, [1] as Result1, [2] as Result2, [3] as Result3, [4] as Result4, [5] as Result5 FROM (SELECT [inputTable].eventID,[inputTable].eQualType,[inputTable].Value1 FROM eventQData WHERE eQualType IN (1, 2, 3, 4, 5) AND (Value2 > 0) ) AS inputTable PIVOT (MAX(Value1) FOR [eQualType] IN … Read more

[Solved] SQL — SELECT 3 TABLES WITH 2 IDs

by

This is a very bad data model. Change it if you can. If there is a column gender in the client table, why muddle through with some generic list? Just add a table gender and link to its rows with client.gender_id: table gender (gender_id, description) table client (client_id, name, gender_id) If you really must make … Read more

[Solved] Convert a string to date in SQL Server

by

I guess you want something like this DECLARE @str VARCHAR(50)= ‘1 year 12 months 2 days’ DECLARE @days INT= LEFT(@str, Charindex(‘ ‘, @str)), @months INT = Substring(@str, Charindex(‘months’, @str) – 3, 2), @years INT = Substring(@str, Charindex(‘days’, @str) – 3, 2); WITH days_back AS (SELECT Dateadd(day, -@days, Cast(Getdate() AS DATE)) AS day_date), month_back AS (SELECT … Read more