tsql

Is it possible to set start of week for T-SQL DATEDIFF function?

I use DATEDIFF function to filter records added this week only: DATEDIFF(week, DateCreated, GETDATE()) = 0 and I noticed what it's assumed what week starts on Sunday. But in my case I would prefer to set start of week on Monday. Is it possible somehow in T-SQL? Thanks! Update: Below is an example showing what DATEDIFF doesn't che...

Replacement of compute by clause in t-sql

i have been working on query which uses compute by clause for avg and sum operators . As Microsoft declared this will be a discontinued feature ,what will be the replacement for this feature ...

SQL calculate difference between cell values

Hi i was wondering if anyone knows how i can calculate the difference between two tables in tsql. I dont mean finding which cells are different - i mean calulcating the numerical difference. eg - Table A has column1, column 2 and only one 1 row. A1 = 40, B1 = 30. Table B has column1, column 2 and only one 1 row. A1 = 25, B1 = 10. So how ...

Joining to a table with multiple rows for the join item

I have a table users which has a primary key userid and a datetime column pay_date. I've also got a table user_actions which references users via the column userid, and a datetime column action_date. I want to join the two tables together, fetching only the earliest action from the user_actions table which has an action_date later than...

TSQL to identify long Float values.

I'm dealing with a legacy system where I need to identify some bad records based on a column with a data type of Float. Good records have a value of... 1 2 1.01 2.01 Bad records are anything such as.. 1.009999999999999 2.003423785643000 3.009999990463260 I've tried a number of select statements where I Convert to Decimal and cast ...

Chinese Characters in SQL Service Broker Message

I have setup multiple SQL Service Broker Queues in a database but have not seen this problem before. A message containing XML is being converted to what appears to be mostly Chinese Characters. When I check the variable that is storing the XML prior to putting it in the message queue I can see that is in English and is well XML formed. W...

Convert XML PATH to XML EXPLICIT for SQL 2000

Hi, I have a section of code that runs on a SQL 2005 server as: SELECT e.[ElementName] , ISNULL(ElementValue, ElementDefaultValue) AS Value FROM [cx_Element] e WITH ( NOLOCK ) LEFT JOIN [cx_ReportStyleElements] rse WITH ( NOLOCK ) ON rse.[ElementId] = e. [ElementId] ...

SQL Server Group By Query using multiple values

I am new to Microsoft SQL Server and have been frustrated by a GROUP BY query that won't do what I want it to. The table is the following: make model distancefrom distanceto driverid toyota yaris 358.2 368.2 401 toyota yaris 368.2 378.7 103 toyota yaris 378.7 382.2 103 toyota yaris 382.2 392.2 103 to...

Identifying indentity column?

I have found out how to determine what columns are are primary key column of a given table by using this query: SELECT CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='tablename_here' AND CONSTRAINT_NAME LIKE 'PK_%' I can find out what the identity seed and increment is by using this ...

create index using openquery

How do I create an index on a table that exist in a remote SQL Server database using the openquery syntax? ...

How do I remove "duplicate" rows from a view?

I have a view which was working fine when I was joining my main table: LEFT OUTER JOIN OFFICE ON CLIENT.CASE_OFFICE = OFFICE.TABLE_CODE. However I needed to add the following join: LEFT OUTER JOIN OFFICE_MIS ON CLIENT.REFERRAL_OFFICE = OFFICE_MIS.TABLE_CODE Although I added DISTINCT, I still get a "duplicate" row. I say "duplicat...

How to group this query so it behaves correctly?

I have a table called Stock and another called Listed, within the Stock Table is a Status Code that indicates when something is at the front of the queue of items of stock - I want to be able to find the most recently added item and set this to be the "front of queue" status. For example to get all the items listed and then order them by...

Bitwise operations in T-SQL

I have a bitmasked int field in my database. Usually I manage it through c# code, but now I need to flip a bit in the mask using T-SQL How do I accomplish the following: The bit i want to flip: 1 << 8 (256) The mask value before i flip: 143 The mask value after i flip: 399 I'm not exactly a bit-flipping wizard but this c...

Can I depend on order of output when using row_number()

I believe the answer is no. And am looking for a counter example to show that order of output is not guaranteed, absent an order by clause. consider: create table #order (orderId int primary key clustered , customerId int not null -- references customer(customerId) , orderDateTIme datetime not null) insert into #order values ...

What is the best way to refactor database procedures?

I need to understand and clarify some T-SQL stored procedures that contain important business logic. Does anyone know of any tools, tips, tricks, or hints that can be used for unit testing so I can refactor this sql without breaking it? ...

How do you pad a NVARCHAR field with zeros using T-SQL in a SQL Server 2005 DB?

I have an Orders table that stores the Ordernumber as NVarChar. We manually increment the order number by querying for the biggest order number ordering in descending order and returning the top 1 and then adding 1. We have this implemented in Microsoft CRM 4.0. e.g Order Numbers (NVarchar) 99 456 32 When I query the above values i...

@@ERROR and/or TRY - CATCH

Will Try-Catch capture all errors that @@ERROR can? In the following code fragment, is it worthwhile to check for @@ERROR? Will RETURN 1111 ever occur? SET XACT_ABORT ON BEGIN TRANSACTION BEGIN TRY --do sql command here <<<<<<<<<<< SELECT @Error=@@ERROR IF @Error!=0 BEGIN IF XACT_STATE()!=0 BEGIN ...

Update column to be different aggregate values

I am creating a script that for "merging" and deleting duplicate rows from a table. The table contains address information, and uses an integer field for storing information about the email as bit flags (column name lngValue). For example, lngValue & 1 == 1 means its the primary address. There are instances of the same email being e...

Delete all rows in table

Normaly i would do a delete * from XXX but on this table thats very slow, it normaly has about 500k to 1m rows in it ( one is a varbinary(MAX) if that mathers ). Basicly im wondering if there is a quick way to emty the table of all content, its actualy quicker to drop and recreate it then to delete the content via the delete sql statem...

TSQL Left Join with multiple right hand rows

When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right? I'm trying to use this to exploit an ordering on the right table. so Select ColA, ColB, ColC from T Left Outer Join (Select ColA, ColB, ColC from T--CLARIFIED, this is a sel...