tsql

[Solved] How can I sort between characters and numbers in column [closed]

by

Here is another option Example Select Robot_Type from (Select distinct Robot_Type From YourTable ) A Order By try_convert(int,left(replace(Robot_Type,’IRB’,”),patindex(‘%[a-z,/]%’,replace(Robot_Type,’IRB’,”)+’a’)-1)) ,Robot_Type Returns Robot_Type NULL IRB 120 IRB140 IRB 360 IRB 910SC —<< Disconnect from your desired results IRB 1200 IRB 1410 IRB 1520 IRB1600 IRB1600/1660 IRB1600ID IRB1600ID/1660ID IRB1660ID IRB 2400 IRB 2600 IRB 4400 IRB 4600 IRB … Read more

[Solved] Where Clause OverRides Max()

by

Now that your requirements are clear, I think this is what you need: SELECT ID, saledate, empName, saleStatusCode FROM ( SELECT ROW_NUMBER() OVER (ORDER BY saledate DESC) RowOrder, * FROM #Test ) SUBQ WHERE RowOrder = 1 In your case, the result is: 1 2014-12-12 00:00:00.000 Joe Joe Joe 2 Actually the result would show … Read more

[Solved] Assig multiple columns in a case statement?

by

Your sample desired output isn’t all clear in my opinion, but maybe this is what you want? SELECT [FileName], CASE WHEN [FileName] LIKE ‘ATE_%’ THEN CAST(SUBSTRING([FileName],5,5) AS NVARCHAR(100)) WHEN [FileName] LIKE ‘%Adhoc%’ THEN CAST([FileName] + ‘ ‘ + [SheetName] AS NVARCHAR(100)) WHEN [FileName] LIKE ‘AdvantageData%’ THEN [SheetName] END AS ABTALookUp, CASE WHEN [FileName] LIKE ‘ATE_%’ … Read more

[Solved] Sql to group by 36 hours [closed]

by

–Set up table and data DECLARE @ATable TABLE ([date] DATETIME, [Count] INT) INSERT @ATable( date, Count ) SELECT ‘2015-05-14 01:00:00’, 1 UNION ALL SELECT ‘2015-05-15 02:00:00’, 2 UNION ALL SELECT ‘2015-05-15 20:00:00’, 3 UNION ALL SELECT ‘2015-05-16 03:00:00’, 4 — Query SELECT d.[date], ( — This subquery returns the sum of counts for the 36 … Read more

[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