Readmission to hospital within 28 days of discharge

This is very common question when we deal with patients in NHS data. Here is how the task is defined – Readmission rates and HES. In short we need to know how many times(or when) a patient was readmitted within 28 days of his last hospital discharge. Here is how I do that in SQL with self join.

The dates are being transferred from “DDMMYYYY” to SQL server datetime “YYYY-mm-dd”.

You can always ad disease code or any other conditions to the query. Hope it helps!

The columns are:
[Column 8] – Patient identifier
[Column 11] – Admission date
[Column 15] – Method of admission
[Column 19] – Discharge date
[Column 28] – Spell end – Yes/No
[Column 123] – PCT code
[Column 187] – Unique record ID

SELECT a.[Column 8] as 'PatientID', a.[Column 15] as 'Method of Admission',
convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105) as 'Admission Date',
convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105) as 'Dischrage Date',
MIN(convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105)) AS 'Readmission Date',
left(a.[Column 123],3) as 'PCT',
DateDiff(dd, convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105), MIN(convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105))) AS 'How many days'
FROM Readmissions_data a
INNER JOIN Readmissions_data b ON a.[Column 8] = b.[Column 8]
	AND convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105) BETWEEN convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105) AND DATEADD(dd, 28, convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105))
WHERE a.[Column 28]='Y' AND a.[Column 19]<>'' AND (right(a.[Column 19],2)='09' OR right(a.[Column 19],2)='10') AND a.[Column 187]<>b.[Column 187]
GROUP BY a.[Column 8],a.[Column 15],left(a.[Column 123],3),convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105),convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105)
ORDER BY convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105)

Clean whole database from unwanted string

Often we end up having an unwanted string injected into our database. It might be a virus or any text that you don’t need. This stored procedure is doing the hard work to remove all the unwanted instances.

ALTER PROC [dbo].[UpdateAllTables]
	@SearchStr nvarchar(100)


	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	SET @SearchStr = QUOTENAME('' + @SearchStr + '','''')

		SET @ColumnName = ''
		SET @TableName = 
							 ), 'IsMSShipped'
						       ) = 0

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
			SET @ColumnName =
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
			IF @ColumnName IS NOT NULL
					'UPDATE ' + @TableName + ' SET ' + @ColumnName + '=REPLACE(' + @ColumnName + ','+ @SearchStr + ','''') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2


Thursdays links part 2

What is Node.js?

Let’s start with V8. V8 is the JavaScript implementation used within Chrome. It utilises “Just In Time” compilation to achieve performance that was previously unattainable in JavaScript. In fact, these improvements lift V8 JavaScript into the same realms of performance as Clojure, Java or Go.

Should I use HTML5 or Silverlight?

I was in Belgium and The Netherlands this last week presenting and talking to folks in the community. After I presented on ASP.NET MVC 3, HTML5 and jQuery, one fellow came up after and said, “Should I use Silverlight or HTML5. I don’t understand what Microsoft’s strategy is or what to use in my app.”

Thursdays links

Countdown to Ext JS 4: Drawing and Charting

Build a Data-Driven Enterprise Web Site in 5 Minutes

Understanding the Dynamic Keyword in C# 4

Programming Windows Phone 7 Free eBook

ProgWinPhone7 246x300 Programming Windows Phone 7 Free eBookThis book is from the famous Charles Petzold. Here is the introduction:

This book is a gift from the Windows Phone 7 team at Microsoft to the programming community, and I am proud to have been a part of it. Within the pages that follow, I show you the basics of writing applications for Windows Phone 7 using the C# programming language with the Silverlight and XNA 2D frameworks.
Yes, Programming Windows Phone 7 is truly a free download, but for those readers who still love paper—as I certainly do—this book will also be available (for sale) divided into two fully-indexed print editions: Microsoft Silverlight Programming for Windows Phone 7 and Microsoft XNA Framework Programming for Windows Phone 7.
With the money you’ve saved downloading this book, please buy other books. Despite the plethora of information available online, books are still the best way to learn about programming within a coherent and cohesive tutorial narrative. Every book sale brings a tear of joy to an author’s eye, so please help make them weep overflowing rivers.
In particular, you might want to buy other books to supplement the material in this book. For example, I barely mention Web services in this book, and that’s a serious deficiency because Web services are likely to become increasingly important in Windows Phone 7 applications. My coverage of XNA is limited to 2D graphics and while I hope to add several 3D chapters in the next edition of this book, I don’t really get into the whole Xbox LIVE community aspect of game development. Nor do I discuss any programming tools beyond Visual Studio—not even Expression Blend.
My publisher Microsoft Press has a couple additional Windows Phone 7 books coming soon: Windows Phone 7 Silverlight Development Step by Step by Andy Wigley & Peter Foot offers a more tools-oriented approach. Although Michael Stroh’s Windows Phone 7 Plain & Simple is a guide to using the phone rather than developing for it, I suspect it will give developers some insights and ideas.
Moreover, I also hear that my old friend Doug Boling is working hard on a Windows Phone 7 enterprise-programming book that is likely to be considered his masterpiece. Be sure to check out that one.

To download click here.

10 Tips for Writing Quality Code

quality code matrix 10 Tips for Writing Quality CodePre-Development
– Gather all the requirements from clients up front. Schorr recommends thinking of these in terms of “stories” as “it’s more in line with how the non-developer thinks.”
– Clarify what is necessary and what’s just “nice-to-have.”
– Refuse to reproduce lousy code. In other words, turn down work if you’re going to be asked to reproduce a poorly designed system – unless you are being given the freedom to do it right. (This begs the question, of course: How do you define “lousy code”?)
– Reject unrealistic timelines
– Out-engineer user-error as much as possible. “In other words,” writes Schorr, “never trust that the user will do what you expect, especially when entering data.”
– Be open to including other languages and technologies where appropriate.
– Don’t reinvent the wheel.
– Review your code for speed, stability, security, and usability.
– Have non-technical people do real-world testing on your product.
– Revisit old code periodically and see what you would’ve done differently.

Microsoft Silverlight 4 and SharePoint 2010 Integration

Download free chapter which consists of 42 pages from here.

Techniques, practical tips, hints, and tricks for Silverlight interactions with SharePoint

  • Develop Silverlight RIAs that interact with SharePoint 2010 data and services
  • Explore the diverse alternatives for hosting a Silverlight RIA in a SharePoint 2010 Page
  • Work with the new SharePoint Silverlight Client Object Model to interact with elements in a SharePoint Site
  • Use Visual Studio 2010’s new features to debug Silverlight RIAs that interact with SharePoint 2010
  • Learn to perform CRUD (Create, Read, Update, and Delete) operations on SharePoint 2010 lists from Silverlight
  • Access and interact with external data sources and WCF Data Services
  • Use themes and work with multiple interactive animations and effects to create distinct User eXperiences (UX) in a SharePoint environment
  • Develop Silverlight RIAs that interact with SharePoint Visual Web Parts
  • A step-by-step, fast-paced book that guides you in implementing Silverlight 4 applications in SharePoint 2010 environments

In Detail

Silverlight is a powerful development platform for creating engaging, interactive user experiences for the Web, desktop, and mobile applications. Integrating Silverlight RIAs in SharePoint 2010 offers amazing opportunities to combine the power and flexibility offered by SharePoint. It is easy to create great user experiences when you have a step-by-step guide to implement Silverlight 4 applications on SharePoint 2010 sites.

This book is not a primer on Silverlight 4 or SharePoint 2010. This book focuses on the integration of Silverlight 4 and SharePoint 2010 and provides step-by-step guidelines for implementing Silverlight RIAs in SharePoint. It is filled with real-world examples that depict the various techniques for interacting with data and services provided by this powerful business collaboration platform, for the enterprise and the Internet. As you sit reading this, you might have already started thinking about the benefits of implementing multiple Silverlight applications in a SharePoint environment. This book will help bring those thoughts to fruition.

This book begins with the fundamental concepts of integrating Silverlight 4 with SharePoint 2010, such as the preparation of the development environment to create applications using Silverlight 4 and the addition of one or more Silverlight RIAs to a SharePoint site. Then, it moves on to the SharePoint Silverlight Client Object Model world, using step-by-step examples to combine a Silverlight application and a SharePoint module. It also covers methods to deploy and debug the Silverlight application while it runs as Silverlight Web Part in a SharePoint page. The book teaches you to take advantage of the new features offered by Visual Studio 2010 to browse SharePoint lists.

Once the reader has control over the SharePoint Silverlight Client Object Model and its asynchronous operations in Silverlight applications, it is time to access external databases through the new Business Connectivity Services (BCS) and interact with workflows. Then, the book explains to perform CRUD operations by consuming the new SharePoint 2010 WCF Data Services in Silverlight. In the end, you’ll learn to utilize Silverlight 4’s rich media features to add effects and interactive animations to images and videos, thus offering the final touches to the Silverlight 4 and SharePoint 2010 integration learning experience.

By the end of this book, you’ll learn to take advantage of the unique features offered by Silverlight in order to create impressive UX that interact with SharePoint 2010.

A hands-on guide packed with real-world examples for integrating your Silverlight 4 applications in a SharePoint 2010 environment.

New stuff in ASP.NET world!

Last week Scott Gu posted about several new things coming up onto the ASP.NET scene. They are:

IIS Developer Express: A lightweight web-server that is simple to setup, free, works with all versions of Windows, and is compatible with the full IIS 7.5.

SQL Server Compact Edition: A lightweight file-based database that is simple to setup, free, can be embedded within your ASP.NET applications, supports low-cost hosting environments, and enables databases to be optionally migrated to SQL Server.

ASP.NET “Razor”: A new view-engine option for ASP.NET that enables a code-focused templating syntax optimized around HTML generation. You can use “Razor” to easily embed VB or C# within HTML. It’s syntax is easy to write, simple to learn, and works with any text editor.

WebMatrix: A new lightweight web development tool that also integrates the above technologies, and makes it even easier for people to get started with web development using ASP.NET. This tool is free, provides core coding and database support, integrates with an open source web application gallery, and includes support to easily publish/deploy sites and applications to web hosting providers.