I’m writing a complex T-SQL query with CASE that will help me calculate how much time it will take me to migrate databases based on the size of each database: All good, it works when I set 1 hour of work for every database which has less then 100.00 MB. Also all other values are summed and in the Hours
Tag: tsql
How to make multiple aggregate function without break / divided the fields
Is it possible to make an aggregate function without break / divided the grouping fields? I make a query but it will divided into duplicate value in the first field, here is my query: My expected result: Emp. Name Count of Prod Total Account % Prod Duration Trip Emp.1 62 63 98,41% 30 Emp.2 45 48 93,75% 28 Emp…
SQL Server 2017 Convert Varchar to Date
I have a date stored as nvarchar which I cannot seem to convert to a date. I have tried the following but get the error message Conversion failed when converting date and/or time from character string Any ideas here? Answer Find the values that are causing the problem using try_convert(): Once you see what va…
Union ALL with different number of columns
I have an Employee table with these columns : EmployeeId Fullname Phone Department Team Function Manager and EmployeeHistory containing the history with different columns, but there are some in common : EmployeeId Fullname Email Geolocation Department Team Function Manager How can I union them? Answer Replace…
Dynamic SQL stored procedure and datetime
I have a simple query that I want to convert to dynamic SQL. I have 2 input parameters: a table and a datetime. And the output is the rowcount for the table and this specific datetime. I tried different solutions. I tried the query with execute sp_executesql, I tied to add the the ”’ before and af…
Get multiple clock in and out time from multiple inputs and neglect without clock [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed last year. Improve this question I have login table structure as below I want to get like Answer As mentioned…
Display null if only one value for Min(Column) and Max(column) SQL query
This is my original table Desired output: Current output with my query Query: Answer You can get your expected output from your sample input: See it in action: SQL Fiddle You should probably add some aliases to at least one set of columns. Not sure, though, how you got two rows of data with your sample query.…
SQL Server: Return a string in a specific format
In TSQL, I need to format a string in a predefined format. For eg: SNO STRING FORMAT OUTPUT 1 A5233GFCOP *XXXXX-XXXXX *A5233-GFCOP 2 K92374 /X-000XXXXX /K-00092374 3 H91543987 XXXXXXXXX H91543987 I am trying with FORMATMESSAGE() built in function. For ex: FORMATMESSAGE(‘*%s-%s’,’A5233′…
How to pivot given string by replacing special characters in SQL?
In an interview, I have been asked to pivot a string by replacing special characters. Given string was ‘AAA%BBB$CCC#DDD’ Expected output as shown in then image: How can we do this in SQL Server? Thanks in advance! Answer In SQL Server 2014?(I guess the operating system you are using is Windows Ser…
SQL – Combine two rows if difference is below threshhold
I have a table like this in SQL Server: What I would like to have is a query that outputs consolidated records based on the time difference between two consecutive records (end_time of row n and start_time row n+1) . All records where the time difference is less than 2 minutes should be combined into one time…