tsql

[Solved] . It’s working fine when I pass 10-15 input but taking more than 5 minutes for 30 input. I need to make it work for 100 inputs

by

. It’s working fine when I pass 10-15 input but taking more than 5 minutes for 30 input. I need to make it work for 100 inputs solved . It’s working fine when I pass 10-15 input but taking more than 5 minutes for 30 input. I need to make it work for 100 inputs

[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] 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] 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 — 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] Sql Data Summery [closed]

by

use a recursive cte that starts with elements whose frompoint matches no topoint in a self join so as to identify the starting points. then eleminate the intermediate results by joining again and use only those data whose topoint match no frompoint in another self join. with cte as ( select r.name, r.frompoint, r.topoint from … Read more

[Solved] T-SQL query on dynamic field with pivot

by

Maybe something like this: Test data: CREATE TABLE #PhoneBook(ID INT,Name VARCHAR(100)) INSERT INTO #PhoneBook VALUES(1,’Reza’),(2,’Ali’) CREATE TABLE #DynamicField(ID INT,Caption VARCHAR(100)) INSERT INTO #DynamicField VALUES(1,’Job’),(2,’Level’) CREATE TABLE #PhoneBook_DynamicField_Rel(ID INT,PhoneBookID INT, DynamicFieldID INT,Value VARCHAR(100)) INSERT INTO #PhoneBook_DynamicField_Rel VALUES(1,1,1,’Emp’),(2,1,2,’1′),(3,2,1,’SomeJob’) Getting the colums DECLARE @cols VARCHAR(MAX) SELECT @cols=STUFF ( ( SELECT ‘,’ +QUOTENAME(tbl.Caption) FROM #DynamicField AS tbl FOR XML … Read more

[Solved] Optimize query using Concat method

by

I would try with this : SELECT [CaseID], STUFF( (SELECT CONCAT(‘; ‘, A.[AssignedPathologist]) FROM CTE1 A WHERE A.[CaseID] = B.[CaseID] FOR XML PATH(”) ),1, 1, ” ) As [AssignedPathologist] FROM (SELECT DISTINCT CaseID CTE1 B) B; For newer versions you can use string_agg() : SELECT CASEID, STRING_AGG(AssignedPathologist, ‘; ‘) AS AssignedPathologist FROM CTE1 C1 GROUP … Read more

[Solved] NULL values in multivalue parameter

by

Finally found the answer: [dbo].[USP_GetProjectPhase] @PurchaseOrder INT AS SELECT -1 AS ‘ProjectPhaseID’ ,’No Filter’ AS ‘Phase’ UNION SELECT pp.ProjectPhaseID ,pp.Phase FROM ProjectPhase pp WHERE @PurchaseOrder = pp.PurchaseOrderId In my query I changed the WHERE clause to: WHERE (reg.ProjectPhaseId IN (SELECT Value FROM fnLocal_CmnParseList(@Phase,’,’)) OR @Phase=”-1″) 1 solved NULL values in multivalue parameter

[Solved] Convert nvarchar(255) to date SQL

by

You can take advantage of SQL Server’s flexibility to recognize various date formats. If you have the right regional settings, this should just work: cast(mycol as date) This gives you back a value of date datetype that corresponds to the input string; Demo on DB Fiddle: select cast(‘Jan 18 2019 12:00AM’ as date) | (No … Read more

[Solved] How subquery works?

by

The query in the parenthesis is called a correlated sub-query. That is because, if you look at the first FROM, it has an alias for the salary table FROM salary a, which in this case is a. The sub-query references the outer query in the WHERE b.salary > a.salary condition, where it checks the condition … Read more