studio NETSOULS

Applying Web To Your Business

How to use CROSS JOIN queries to find records out of sequence

by Sonu Rani

25 Feb 2009

Many of us have faced a problem where we need to compare historical records for a particular set of records to ensure that records have been saved in the correct order.

This could be for comparisons of dates or numbers or anything.  The need is to ensure that the historical records have been saved in the correct order. Using Cross joins, we can identify records that have been stored out of order for a subset of records.

Let’s say I have table in which there are three fields:

EmployeeId
Version
CreateDate

Here, EmployeeId is an integer field and is our primary ID. Version is also an integer field,considering this as a secondary ID. So, in totality EmployeeId and Version will form the Composite Key. There can be many versions of the same EmployeeId and each Version would have a corresponding CreateDate.

Our problem is to compare the CreateDate of all the versions of a particular employee on an iterative basis using a single query to ensure the CreateDate are in order based on the Version for a particular employee.

The below scripts will help you understand the problem and its solution.  I am using a new table, adding some data and then using the query to determine which records are out of order.

   1: CREATE TABLE Employees(
   2:      EmployeeID INT        NOT NULL,
   3:      Version    INT        NOT NULL,
   4:      CreateDate DATETIME   NULL,
   5: CONSTRAINT pk_Employees PRIMARY KEY CLUSTERED 
   6: (
   7:      [EmployeeID] ASC,
   8:      [Version] ASC
   9: ))
  10: GO
  11: INSERT INTO Employees VALUES(1,0,'03/10/2000')
  12: INSERT INTO Employees VALUES(1,1,'03/16/2000')
  13: INSERT INTO Employees VALUES(1,2,'03/19/2000')
  14: INSERT INTO Employees VALUES(1,3,'03/18/2000')
  15: INSERT INTO Employees VALUES(1,4,'03/17/2000')
  16: INSERT INTO Employees VALUES(2,0,'02/10/2000')
  17: INSERT INTO Employees VALUES(2,1,'02/11/2000')
  18: INSERT INTO Employees VALUES(2,2,'02/18/2000')
  19: INSERT INTO Employees VALUES(3,0,'03/25/2000')
  20: INSERT INTO Employees VALUES(3,1,'03/23/2000')
  21: INSERT INTO Employees VALUES(3,2,'03/26/2000')
  22: INSERT INTO Employees VALUES(3,3,'03/30/2000')
  23: INSERT INTO Employees VALUES(4,0,'08/19/2000')
  24: INSERT INTO Employees VALUES(4,1,'08/20/2000')
  25: INSERT INTO Employees VALUES(4,2,'08/23/2000')
  26: INSERT INTO Employees VALUES(4,3,'08/24/2000')
  27: GO
  28: SELECT *,
  29:      (SELECT 
  30:           CASE WHEN (SUM(CASE WHEN B.CreateDate<C.CreateDate THEN 0 
  31:                ELSE 1 
  32:                END))>=1 THEN 1 
  33:           ELSE 0 
  34:           END AS IsNotProper 
  35:      FROM Employees B CROSS JOIN 
  36:           Employees C
  37:      WHERE B.EmployeID=A.EmployeID 
  38:           AND C.EmployeID=A.EmployeID 
  39:           AND B.Version<>C.Version 
  40:           AND B.Version<C.Version) 
  41:      AS [OutOfOrder] 
  42: FROM Employees A

In the output it will display all the records with a flag called OutOfOrder. If the flag is 1 that means there is a problem with the record order. Otherwise, the EmployeeID’s versions are in proper order.

In the above example you can see that EmployeeID 1 has two records that are out of order.  The 2003-03-18 record came before the 2008-03-17 record, so the query picked these up as being out of order.

Query Result

So, instead of using loops and cursors to determine if there is an issue it handles everything in a single query.

Tags:
  • Currently 0 /5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

How to make a RadAjaxLoadingPanel Span over the whole page

by Sonu Rani

6 Jan 2009

The following code library example demonstrates how to make a RadAjaxLoadingPanel expand to occupy the full page width and height.

Some important notes:

  • The RadAjaxLoadingPanel has to be Sticky (IsSticky="true") and absolutely positioned with width and height set to 100%.
  • The <body> tag needs its margin reset to zero, otherwise the loading panel will not start from the browser viewport edge.
  • The html, body and form tags need a min-height:100% style, in casе the page content is less than the browser viewport height.
  • If the page content is more than the browser viewport height, you need to set the loading panel's height client-side with Javascript.
  • The RadAjaxLoadingPanel must be placed inside the <form> element. Otherwise it must not have relatively positioned parent elements, because they will prevent it from expanding properly.
  • If the RadAjaxLoadingPanel does not cover some relatively positioned elements on the page, then move the loading panel after those elements in the page markup and/or set some large enough z-index style to the RadAjaxLoadingPanel's CSS class.

You can scroll randomly up or down before making the AJAX request to see that the modal background covers the whole page and the background image is always centered in the current visible portion of the loading panel. This is achieved by the RequestStart() javascript function.

Tags:
  • Currently 0 /5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Convert a IList<customobject> property to comma separated

by Sonu Rani

2 Dec 2008

A lot of times in the applications we require to pass comma separated values to the stored procedures, these values are contained in a custom object IList or IList<string> type. 

/// <summary>
/// Converts a list item property name value to comma seperated values
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Items"></param>
/// <param name="PropertyName"></param>
/// <returns></returns>
public static string ConvertTo<T>(IList<T> Items, string PropertyName) 
{
    StringBuilder builder = new StringBuilder();
    Type entityType = typeof(T);
 
    // iterate through the property
    foreach (T Item in Items)
    {
        if (string.IsNullOrEmpty(PropertyName))
            builder.Append(Item.ToString()).Append(",");
        else 
            // get the value of the property name passed
            builder.Append(
                entityType.GetProperty(PropertyName).GetValue(Item, null).ToString()
            ).Append(",");
    }
 
    return builder.ToString().TrimEnd(new char[] { ',' });
}
Tags: , ,
  • Currently 2 /5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Reading Excel sheets

by Sonu Rani

30 Aug 2008

So, I've been writing a little app to combine some Excel spreadsheets. These sheets all have header rows, so I can open them up with an OleDbConnection, do a "SELECT * FROM [Sheet1$]" and go along my merry way. The problem is, two of the columns I need to work with just aren't there. They're on the excel sheet just fine, but my OleDbDataReader finds nothing in those columns on any row. Looking at the sheet, I see that in those columns, there isn't any data for about 12 rows. So, I put in zeros at the top of those columns, and then it works fine.

Is the OleDbConnection really making assumptions about the dataset based on the first row? After much googling and little success, I try to find a definition of the connectionstring, hoping there's some attribute like "rows to scan for schema" I can set, to tell it to actually read my data. Nathan points me to an excellent resource, Connectionstrings.com, and they kindly let me know that I can specify HDR=Yes; to indicate that I have a header row in my sheets, and IMEX=1; which, according to Connectionstrings.com:  tells the driver to always read "intermixed" data columns as text.

Apparently, the two columns in question were, in fact "intermixed" data columns, and once I set that in my connectionstring, all worked fine.
If you have, for instance, both strings and numerics in one external data column, you can get unexpected results - like some of your data doesn’t show up and the above solution doesn't work for you, then again after a lot of googling and research, i tried to change the registry keys as mentioned in this article External Data - Mixed Data Types

The mixed use of "1" and "Yes" aside, why the hell would your database driver just silently ignore data? I mean, if "intermixed" data columns is an error condition, then have the balls to throw a exception, warning, event log entry, anything. Don't just not work and expect me to magically know where the problem is. I almost re-implemented the whole damn thing using Excel objects and the Office API, and that would've taken me another couple of days. A pox on the Excel team!

Tags: ,
  • Currently 0 /5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

About Us

studio NETSOULS is a complete IT services company, offering strategy, design, development and implementation of the total solution for your web and IT initiatives. The solutions we provide, enables businesses to leverage leading edge technology to gain sustainable competitive advantages in today's marketplace.

We specialize in designing, developing and deploying the next generation of IT solutions including e-business solutions Read more...

Tags

This will be shown to users with no Flash or Javascript.

Contact Us

My status

Quote of the Day

"Do you see a man wise in his own eyes? There is more hope for a fool than for him."

- Proverbs 26:12

NutritionVista

www.NutritionVista.com

Archives


Advertisements


Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2008

Log in

Design by: studio NETSOULS