Tag: sql-server-2012

T-SQL progressive numbering partitions

I am aiming to obtain a record set like this date flag number 01 0 1 02 0 1 03 1 2 04 1 2 05 1 2 06 0 3 07 1 4 08 1 4 I start from the record set with “date” and “flag” only. I am trying to compute the “number” […]

Database Consolidation – Named instance vs default instance

I have searched SO, but cannot find a question/answer close enough. We are busy consolidating our database servers, partially due to address performance issues using cross-server (cross-instance – SEE: RPC) queries. A lot of extra thought is put to trying to manage the “chunk of data over the linked server” vs just letting the compiler […]

SQL Server 2012 : alternative to XML PATH

I have been tasked with reworking a set of existing legacy queries. The main problem I can find is they all use XML PATH() to make updates to various temp tables before returning the results. It breaks down like so, we create a temp table with the basic information as distinct values. OwnerId | FName […]

SQL Group by Client Location

Sample of Data I am trying to manipulate Order | OrderDate | ClientName| ClientAddress | City | State| Zip | ——-|———–|———–|—————|——–|——|——-| CO101 | 1/5/2015 | Client ABC| 101 Park Drive| Boston | MA | 02134 | C0102 | 2/6/2015 | Client ABC| 101 Park Drive| Boston | MA | 02134 | C0103 | 1/7/2015 | […]

Using Import wizard to import Excel data into table in SQL Server 2012 failing – text truncation

I have an Excel file that I import weekly into a table. I am using Excel 2010 and SSMS 2012. The import failed this week and I have been unable to determine why. I was able to successfully import the data into a new table. The table definitions of the new table match the ones […]

Function Not Returning Decimal Places despite being cast as decimals

I have the little bit of code below that is not returning a decimal place for the formula so instead of something like 4.55 I’m only getting a 4.00 any help would be appreciated thank you. CAST(datediff(mi,ord_startdate,ord_completiondate)/60 as decimal (10,2))

SQL Server concurrent 5 timestamps in a columns

I would like to get an SQL Query that would help me to fetch the times in a row whichever lies with in 5 minutes and the count of times noticed should greater than or equal to 5? For Example: Column A 17:22:23 17:44:31 17:25:36 17:24:11 17:47:39 17:26:22 17:22:44 My results should fetch the below […]

MSSQL: Get Subtotal of a total

How can I write a query to get the subtotal of a total? Example is below. select task_id, client_id, SUM(value1 + value2) AS ‘Total 1’, SUM(value3 + value4) AS ‘Total 2’, –pseudocode SUM(Total 1 + Total 2) AS ‘Total 1 + Total 2’ from table1 GROUP BY task_id,client_id Expected Results: task_id | client_id | Total […]

INSERT INTO EXCEPT using only 2 identify columns

hopefully this is an easy one for you folk smarter than me. I am trying to perform an insert into a table from another matching table, but I only want to insert if the row doesn’t already exist. Some of these values in the row if it exists may be changed. Which is okay. I’m […]

Searching a string in stored procedure definitions fails when the definition is too long

I run this query to find references to MyTable in the stored procedures in my data base: SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%MyTable%’ However, I did not find anything, the problem is that ROUTINE_DEFINITION is limited to 4000 characters and the SP that operated on MyTable was longer. Using View Dependencies […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.