Introduction T-SQL (Transact-SQL) is a powerful language used to query and manipulate data in a Microsoft SQL Server database. It is a powerful language that allows you to perform complex operations on data stored in a database. One of the most common operations is to rotate a table, which is the process of rearranging the … Read more
Try: select Agent, max(case when `desc` = ‘Total’ then ColValue else 0 end) Total, max(case when `desc` = ‘Replaced’ then ColValue else 0 end) Replaced from tbl group by Agent Demo sqlfiddle 1 solved T-SQL | SQL-Server Pivot [closed]
I’m assuming your datetime variable is a VARCHAR – this can be directly cast via CONVERT: Declare @YourVariable Varchar (15) = ‘JAN 01 1996’ Select Convert(Date, @YourVariable) 1996-01-01 2 solved Converting datetime variable format
Assuming that I understand the problem: you have a number of rows with sections (“Cates”) and symbols (“Type”); if there are any symbols ending in a minus sign then these indicate a row without a minus sign should be removed; symbols are never “mixed” per section, i.e. a section can never have “A” and “B-“; … Read more
In SQL Server, the update should start: update TBLSurvey set Status = -1 from TBLSurvey . . . The syntax for update in SQL Server is here. If I had to guess, it looks like you are confused with aspects of MySQL syntax. solved Incorrect syntax near the keyword ‘set’ and ‘as’ [closed]
Try this with myTable ( numberid, startDate, endDate ) as( select numberid, CONVERT(DATETIME,startDate), CONVERT(DATETIME,endDate) from ( values (4405598510,’2011-08-06 00:00:00′,NULL), (2418680054,’2011-08-06 00:00:00′,’2011-12-28 00:00:00′), (4405598510,’2011-08-06 00:00:00′,NULL), (1810168034,’2011-08-06 00:00:00′,NULL), (6849266569,’2011-08-06 00:00:00′,’2014-09-02 00:00:00′), (2682265222,’2011-08-09 00:58:00′,’2012-09-20 00:00:00′), (6253123963,’2011-08-09 00:00:00′,’2011-07-01 00:00:00′), (8276745680,’2011-08-10 00:00:00′,’2014-06-27 00:00:00′), (3873103800,’2011-08-10 00:00:00′,’2013-07-16 00:00:00′), (3703761027,’2011-08-06 00:00:00′,NULL), (1810168034,’2011-08-06 00:00:00′,NULL) ) [ ] (numberid,startDate,endDate) ) select Numberid, startDate, endDate, case … Read more
You have two froms in the query. FROM FROM table1 –change this solved Incorrect syntax near the keyword ‘FROM’. No clue [closed]
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