[Solved] T-SQL | SQL-Server Pivot [closed]

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]

[Solved] T-SQL to generate expect result

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

[Solved] Want to the check the records existing in the date range in sql server [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

[Solved] 2 rows presented as 1

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

[Solved] I’m looking for a way to trim a string so that only 6 consecutive digits show

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

[Solved] Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate row. And show that duplicate rows once [closed]

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

[Solved] Left join combining GETDATE()

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

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

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()

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?

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]

–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