Just a wild guess: SELECT * FROM table AS S1 WHERE EXISTS ( SELECT * FROM table AS S2 WHERE ( S2.code = S1.code ) AND ( ( S1.column1 IS NULL AND S2.column1 IS NOT NULL ) OR ( S2.column1 IS NULL AND S1.column1 IS NOT NULL ) ) ) ; 1 solved 2 rows … Read more
You’re close! You need to use PATINDEX instead of CHARINDEX. After that, you can use SUBSTRING to pull the six numbers out. This should work for you: Select SUBSTRING(d.Notes, PATINDEX(‘%[0-9][0-9][0-9][0-9][0-9][0-9]%’, d.Notes), 6) From YourTable d If there are records in your table that do not have six consecutive numbers, you can use either of the … Read more
You seem to want aggregation: select disport, actualagent, . . . , country, sum(pallats), sum(gross) from t group by disport, actualagent, . . ., country; You need to list all the columns where the . . . is. 1 solved Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate … Read more
You can use apply: select a.*, b.* from a cross apply (select top (1) b.* from b where b.code = a.code and b.time < getdate() order by b.time desc ) b; This assumes that time is really a datetime. If you just want to compare times, then use convert(time, getdate()). 2 solved Left join combining … Read more
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
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
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
–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
This should work: SELECT cast(COALESCE(sum([ColumnA]), 0) as DECIMAL(18,0)) AS [Weight] FROM [DB1].[dbo].[Tabel1] 0 solved How to handle NULL’s to show 0 in T-SQL query
. 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