Filtering/Grouping by multiple columns in sql subquery

I have been struggling for a while to have multiple columns in subquery and then filtering out the result.

Often with composite keys in the staging table, you want to check if there are any duplicates.

Taking an example of a fictitious "sales" table:

select * from sales where order_number in
(select order_number from sales
group by order_number, customer_id
having count(*)>1)

Above query might not give the desired results if I am looking for particular results with filters. The reason being I did not apply all the fields in the select query for the ones used in group by.

An extensible solution below takes care of that:

--Use CTE to store result for further filtering
with cte as (select s1.field1, s1.field2, s1.field3 from sales as s1
where exists
--Have a subquery for multiple column grouping. Compare it with the fields of the parent query, which would yield 1 corresponding record
(select s2.field1, s2.field2, sd.field3  from sales as s2
where s2.field1=sd1.field1 and s2.field2=s1.field2 and sd.field3=s1.field3
group by field1,field2,field3
) --Have your extra condition here for filtering
)
select * from cte where reached_target='Y'  -- Get all those qualified records needed

In this case, Common Table Expression (CTE) does come in very handy to filter out the final resultset.

Connecting to a Remote SQL Server and Import Data from your local SQL Server (2008 Express)

I have a remote database sitting on VPS and I need to transfer the data to the remote server. All I do is first register/connect the remote Sql server in my Management Studio. For this, enter the provided server name using the registered domain name (or IP Address)  along with the provided port number. Append the Sql server name (I appended \SQLExpress).

Doing this will enable you to connect the remote server in your local management studio (assuming you enter the correct authentication details for username/password !).

Before you go ahead with the next step of doing data migration, make sure first you run the database scripts so that your indexes/foreign keys are intact. Once this is done, you can use the import/export wizard to export data from your local machine to the remote server. A tiny trick is to use the import wizard from the destination database rather than using the export wizard from the source database. This will ensure your configuration for destination database is aptly configured.

Sorry this is very bland write-up without any screenshots or proper explanation, but hopefully I'll provide them when I have more time.

Batch Update using SQL

Though this is a fairly easy process, its worth mentioning here since it is something which cannot be thought about. Using top x in your query, you could basically choose how many records need to be updated. This is helpful for processing the records as required

UPDATE top (100)  TableName 
SET
    column1 = getdate(),
    remarks = 'batch for API Upload'
 where column2 is not null and column1 > '2012-1-1'

Extract Date from DateTime Field in SQL

I was working on SQL to create a view to get the reports for our subscribers and group them by day. This is fairly easy to implement in .NET, but I have been licking SQL candies for a while and so wanted to try it out. This is how I did:

 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

I could have used varchar converstion and "trimmed" the time value, but I had to further sort the resultset based upon the date obtained, so it was important for me to have it as a date type.

Using Trim() in LINQ to SQL on an Empty string

This is kind of a silly thing I did, now that I think about it, but I must admit I was ignorant.

I didnt realize that you don't necessarily need a string utility to "Trim()" an Empty string of whitespaces, becuause, well, it doesn't matter.

To SQL, ' ' is same as '          '.

What I realized when using Trim() function, was this function was adding an overload of ltrim(rtrim(emptystringfield)) to my already resource intensive query, which in effect was increasing my query execution time. So use Trim() only when you need it, especially when using expensive queries.

Searching for numeric character(s) in Column of a Table in SQL

For Reference:

select * from [YourTable] where 
patindex('%[0-9]%',[yourcolumn]) <> 0

That was kinda easy!

Deleting duplicate records using SQL

I have been working on SQL lately and its worth mentioning in the blog as to how easily find and delete duplicate records in the database.

Here's a nice solution posted by Pinal Dave on his blog:

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

NJoy!

Exporting nvarchar fields from SQL Server to a csv file

I am posting this more so for a reminder for myself to acknowledge myself that there is something called observation.

I am working on this data migration project(like I didnt say that earlier!) and I was exporting firstname and lastname (nvarchar fields) to a csv file and I was having issues on the final export. I was getting the following error:

I searched on the internet to find a solution to it, but couldn't find one. I was feeling frustated. Then I thought there must be something that should solve it.

I went all over again through the sql server export screens and found that little tiny checkbox that my eyes weren't able to catch before:

So I marked the "Unicode" checkbox, and I was able to export all my records successfully!

Moral of the story: Keenly observe the details before banging your head on the monitor!

Handling NULL values in SQL case statements while inserting or Updating Records

I am working on this massive data import project. Reminds me of those old days, when lots of stuff was done at the SQL level and and .NET took care of the UI Side. LINQ spoiled me to do all the SQL stuff using LINQ to SQL.

Anyway, working on SQL console is a lot of fun. Especially for big data imports and exports, that's the way to go. Feels like you are more so of a DBA!

NULL values are always tricky to work with. I came across this problem where I had to write a case statement where if the value is NULL, update the 'remarks' field, else append the value to the field:

Here's what I was doing:

update [tablename]
set remarks=case remarks when null then 'source:ms.csv'
else (remarks + '; source:ms.csv') end
where email='xyz@abc.com'

Apparently, the field was'nt updated since the remarks field was blank for that record. Looks strange!

After doing a little research and trying a few conditions, here's how it worked:

update [tablename]
set remarks=case isnull(remarks,'true') when 'true' then 'source:ms.csv'
else (remarks + ' source:ms.csv') end
where email='xyz@abc.com'

Note that the second parameter should be implicitly convertible to the first parameter of isnull.

UPDATE: April 2012
For numeric/decimal/int columns, this would be it:

update [tablename]
set totalcount=case isnull(totalcount,0) when 0 then 1
else (totalcount + 1) end
where email='xyz@abc.com'

Check the microsft documentation here for details.

Thats how it goes! You can take care of bigger chunks of data, but its the little things like those NULL values that drive you crazy!