SQL to get data for the last hour

If you have a date column, which can be unique to identify a row will make it easier to pull the rows that are added for any given hour, this simple one line query can come handy:

SELECT * FROM transactions

WHERE

  transactiondate

   between

DATEADD(hh,DATEPART(hh,GETDATE())-1,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())))

AND

DATEADD(ss,-1,DATEADD(hh,DATEPART(hh,GETDATE()),DATEADD(dd,0, DATEDIFF (dd,0,GETDATE()))))

Explaination:

@today : DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()) –> this gives the today’s date with 00:00:00 timestamp

@last hour : DATEPART(hh,GETDATE())-1 –> gives last hour

DATEADD(hh,@lasthour,@today))  –> gives date time of today’s last hour ex: mm/dd/yyyy hh:00:00

next to get the last second of the previous hour, minus a second from the next hour

@currenthour : DATEPART(hh,GETDATE()) –: get current  hour with 00:00:00 timestame

DATEADD(ss,-1,DATEADD(hh,@currenthour,@today)))  — add -1 second on the current date/time to get the hh:59:59

 

Installing SQL Server 2008 Express with Advanced Services

My First Impression

Even if you don’t upgrade your older SQL database (2000 or 2005) to SQL Server 2008 Express, I see lot of benefits using the new SQL Management Studio that comes part of the SQL Server 2008 Express. The New Management is free and backwards compatible with older SQL Servers.

The Advanced Services also includes the Reporting Services and Visual Studio (SQL Server Business Intelligence Studio) for developing reports.

Code Completion

This is very good compare to nothing, but I still feel it could have been better, may be the basic feature limitation.. the code completion appears only on the TSQL Statements, I mean it could be nice if I type SET and shows the possible SET options like NOCOUNT ON/OFF

 

 

 

 

 

 

 

 

 

 

 

 

 

DTS

DTS is back and it is good for simple export/import, but unfortunately, we can’t save the DTS package.

IceRocket Tags: SQL Server 2008,DTS,Express,Reporting Services

Using CF Agent and Manager

Concept

Tools and Services:

• CF Agent – The Agent that collects and uploads data
  
• CF Agent Manager – To Configure CF Agent
  
• CF Web Service (https://cloudfunnel.com/webservice) – To communicate directly with CF Server and you can integrate into you existing application for custom solutions
  
• CF Website (https://cloudfunnel.com/<accountname>/Website – To visualize data that are being collected and available in the CF Server
  

The objective of the Tools is to collect data and upload the CF Server

Install one “CF Agent” on each Computer/Network location where data is collected

The CF Agent runs in the background and executes the configured jobs assigned to that Agent

You will use “CF Agent Manager” to configure “CF Agent”

Cloud Funnel Agent (CFA) collects data and uploads to Cloud Funnel Server (Azure SQL Server).

• Agent – a Network Location, where data will be collected
  
• Job – a set configuration that defines what data to collect (Data Source), and how often (Schedule).
  
• An Agent can have multiple Jobs, each job maintains is own data source and schedule
  

 

Configuring CF Agent

Step 1: Run CF Agent Manager

• Available directly at: https://cloudfunnel.com/downloads/cfam/cfam.application Or Menu link is available in CF Website:
  

 

• Login
  

• Create Agent(s) and Jobs(s)
  
• The Left site tree shows existing Agents and Jobs (will be empty/blank if none exists)
  

• Click “New Agent” from the Agents menu to create a new Agent
  

  
  

• Select the Agent that you want to add a new Job and Click “New Job” from the Jobs menu
  

 

 

 

• Creating a new Job, make sure to select the appropriate Job Type
  

• Configure the Database – the screen shot shows configuring SQL server express database in the local system where CF Agent Manager is being run with a trusted connection.
  

• Query should be written to always return new data, as each time job is executed by the Agent
  

  For example the following query below returns the transactions happened last hour, will be useful when the job is run every hour.
  

  SELECT * FROM transactions WHERE transactiondate between DATEADD(hh,DATEPART(hh,GETDATE())-1,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))) AND DATEADD(ss,-1,DATEADD(hh,DATEPART(hh,GETDATE()),DATEADD(dd,0, DATEDIFF (dd,0,GETDATE()))))
  

  
  

• Unique Column(s) is optional, but very useful when query cannot be written to pull only new data. By defining the Unique column simplifies and CF Agent automatically pulls only new data.
  

For Example if the query is simply written as

    Select * from transactions

And then define a unique column as shown below:

• Here you enter a column name that is part of the query (select * from transactions should have column with a name transactiondate)
  
• Enter a default value to be used when a job runs first time, here since the job is never run, enter a value that will pull all the transactions when job is run first time
  
• Running Value – defined by a query or formula. (Note: formula will be available in future version)
  
• Enter a query that returns a scalar value, and this value will be retained each time job is run as per schedule
  

Explanation:

Let’s say first time the job is run on 4/25/2010 at 10 am, the database has data for the past 2 months. And you are creating a job that will be run every hour. But when the job is run first time, you will want all the data that is there in the database.    

• So you can enter a default value with a much older date : 1/1/2001, so when the query runs
  
• Select * from transactions where transactiondate > ’1/1/2001′ for the first time on 4/25/2010 10:00am.
  
• Since you have defined a unique column with a running value ‘select getdate()’, it keeps the value 4/25/2010 10:00 am.
  
• And when the job is run next hour, query runs like this (since the job is configured to run every hour):
  
• Select * from transactions where transactiondate > ’4/25/2010 10:00am’
  

 

• Here the schedule shows that is configured to run every hour
  

   

  
  

• After saving, you will on the left side
  

  
  

 

 

Uploading Data

• To upload data download and run the CF Agent on the system
  
• Available at https://cloudfunnel.com/downloads/cfa/cfa.application and also available in the website
  

  
  

• When run for the first time, it will ask to select an Agent, enter account, user and password and click Connect and then select the Agent (created earlier using CF Agent Manager) and Click Save
  

• After Clicking Save, you will the CF Agent Status Window, and you can minimize the window, it will hide and run in the background.
  

• You can anytime bring it from the system task tray:
  

  
  

• By double clicking on the Cloud Funnel Agent Icon you can bring back the CF Agent Status window

Using CF Agent and Manager

Concept

Tools and Services:

· CF Agent – The Agent that collects and uploads data

· CF Agent Manager – To Configure CF Agent

· CF Web Service (https://cloudfunnel.com/webservice) – To communicate directly with CF Server and you can integrate into you existing application for custom solutions

Technorati Tags: Cloud Funnel,SaaS,Azure

· CF Website (https://cloudfunnel.com/<accountname>/Website – To visualize data that are being collected and available in the CF Server

The objective of the Tools is to collect data and upload the CF Server

Each Computer/Network location where data needs to be collected, you have to install one “CF Agent”

The CF Agent runs in the background and executes the configured jobs assigned to that Agent

You will use “CF Agent Manager” to configure “CF Agent”

Cloud Funnel Agent (CFA) collects data and uploads to Cloud Funnel Server (Azure SQL Server).

· Agent – a Network Location, where data will be collected

· Job – a set configuration that defines what data to collect (Data Source), and how often (Schedule).

· An Agent can have multiple Jobs, each job maintains is own data source and schedule

Configuring CF Agent

Step 1: Run CF Agent Manager

· Available directly at: https://cloudfunnel.com/downloads/cfam/cfam.application Or Menu link is available in CF Website:

· Login

· Create Agent(s) and Jobs(s)

· The Left site tree shows existing Agents and Jobs (will be empty/blank if none exists)

· Click “New Agent” from the Agents menu to create a new Agent

· Select the Agent that you want to add a new Job and Click “New Job” from the Jobs menu

· Creating a new Job, make sure to select the appropriate Job Type

· Configure the Database – the screen shot shows configuring SQL server express database in the local system where CF Agent Manager is being run with a trusted connection.

· Query should be written to always return new data, as each time job is executed by the Agent

For example the following query below returns the transactions happened last hour, will be useful when the job is run every hour.

SELECT * FROM transactions WHERE transactiondate between DATEADD(hh,DATEPART(hh,GETDATE())-1,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))) AND DATEADD(ss,-1,DATEADD(hh,DATEPART(hh,GETDATE()),DATEADD(dd,0, DATEDIFF (dd,0,GETDATE()))))

· Unique Column(s) is optional, but very useful when query cannot be written to pull only new data. By defining the Unique column simplifies and CF Agent automatically pulls only new data.

For Example if the query is simply written as

Select * from transactions

And then define a unique column as shown below:

o Here you enter a column name that is part of the query (select * from transactions should have column with a name transactiondate)

o Enter a default value to be used when a job runs first time, here since the job is never run, enter a value that will pull all the transactions when job is run first time

o Running Value – defined by a query or formula. (Note: formula will be available in future version)

o Enter a query that returns a scalar value, and this value will be retained each time job is run as per schedule

Explanation:

Let’s say first time the job is run on 4/25/2010 at 10 am, the database has data for the past 2 months. And you are creating a job that will be run every hour. But when the job is run first time, you will want all the data that is there in the database.

o So you can enter a default value with a much older date : 1/1/2001, so when the query runs

o Select * from transactions where transactiondate > ‘1/1/2001’ for the first time on 4/25/2010 10:00am.

o Since you have defined a unique column with a running value ‘select getdate()’, it keeps the value 4/25/2010 10:00 am.

o And when the job is run next hour, query runs like this (since the job is configured to run every hour):

o Select * from transactions where transactiondate > ‘4/25/2010 10:00am’

 

· Here the schedule shows that is configured to run every hour

· After saving, you will on the left side

Uploading Data

· To upload data download and run the CF Agent on the system

· Available at https://cloudfunnel.com/downloads/cfa/cfa.application and also available in the website

· When run for the first time, it will ask to select an Agent, enter account, user and password and click Connect and then select the Agent (created earlier using CF Agent Manager) and Click Save

· After Clicking Save, you will the CF Agent Status Window, and you can minimize the window, it will hide and run in the background.

· You can anytime bring it from the system task tray:

· By double clicking on the Cloud Funnel Agent Icon you can bring back the CF Agent Status window

Cloud Funnel

Cloud Funnel : Data Transfer Service

	Cloud Funnel is proud to introduce the Data Transfer Service, that integrate instantly with any existing Applications using a combination of Web Service API and Cloud Funnel Tools. Typically collecting data from multiple geographically distributed locations is not every easy, from 3 party applications that are in many cases these locations will be in their own internet connection (DSL/Cable etc). Getting sensitive data, especially time-sensitive data, from one location to another becomes an enormous bottleneck. Writing custom code to collect and transfer by traditional means, and sharing the same data or a part of that data is often a over head and sometimes such requirement is only a short period of time. Often, Companies need to share the same data for backup or with other partners on a regular basis. The Cloud Funnel’s Data Transfer Service offers an instant solution to share this critical data easily, securely and quickly. Data can be collected within minutes and configured to share and consumed by any applications using Web Services API Quickly and easily.
How It Works Cloud Funnel uses desktop tools to pick up your data, and a high-speed backbone to transport your data instantly to the Cloud Database. Your data can be encrypted with your own encryption key as it is transferred onto the Cloud Database. Only you and the recipient (using Cloud Funnel API) have access to the encryption key to decrypt your data, so your data is protected en route, before being transmitted over the Cloud Funnel Storage backbone. Once transported to the Cloud, Using Web API you can integrate with your existing application or using Cloud Funnel’s your own private website to access your data from anywhere in the world.
	Tools Our Tools offer the highest level of user friendliness in the whole setup process right. Cloud Funnel offers these following tools : Cloud Funnel Agent (CFA): The Desktop Software Module that collects data as per the Job Schedule Configuration Cloud Funnel Agent Manager (CFAM) : The Desktop Software Module that configures the CFAM. Cloud Funnel Web Service (Private Web Service) : The Core Web Service (API), that provides functionality to read and write data to the Cloud Database. Cloud Funnel Web Site (Private Website) : A Web Application to access the data in the Cloud Database.

TSQL – Getting every day of the Current Month

This came handy, when i wanted to get all dates of the current month when joining with mutiple tables… and not all the tables  had all dates of the month

;WITH
   CTE0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    CTE1 AS(SELECT 1 AS c FROM CTE0 AS A, CTE0 AS B),
CTE2 AS(SELECT 1 AS c FROM CTE1 AS A, CTE1 AS B),
CTE3 AS(SELECT 1 AS c FROM CTE2 AS A, CTE2 AS B),
  SeqNumbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS SeqNumber FROM CTE3)

SELECT SeqNumber,CONVERT(VARCHAR(12),(GETDATE()-(DAY(GETDATE())-SeqNumber)),101) AS ReportingDate
FROM
    SeqNumbers
WHERE SeqNumber <=    DAY(GETDATE())

Fake House Rental Ad in Craigslist

This Monday morning I got a call from a lady claiming to be renting my house and she saw my ad in the Craigslist and if I still in USA or Africa?

The ad which i was not totally aware. and she confirmed that she is working with her Aunt a realtor. Though i told her, we are not looking for tenants now and if required she can talk to my Property Manager. and Then I talked to my property Manager, he said he did get calls asking how authentic we are in renting the house.

I got to know more from my property manager, that that lady is a MBA student and had already sent $800 twice!!!. Which I still wonder how someone would send money without meeting in person or any kind of information, I could not get hold of them to get more details.

Anyway, I am now worried there is someone using my name, created a fake email address with my name. and renting my house in Craigslist.

What Can I do?

Nothing. I searched all over the internet, I could report in www.ic3.org, and talk to my friends. and that’s it. There are several other news similar to rental scam like this, and apparently largely from Nigeria.

 

I had someone investigate this and give me more details on this:

1. A Yahoo email created using my name: vontlinsriprabuc@yahoo.com   — of course you can send email to the person who is running this scam.
2. IP Address used to send email communication for those who responded to the ad:

The result of WhoIs:

Location: Nigeria (high) [City: Lagos, Lagos]
[Caching always on for search engines and InfoPath]
% This is the AfriNIC Whois server.

% Note: this output has been filtered.

% Information related to ’41.219.226.0 – 41.219.226.255′

inetnum:        41.219.226.0 – 41.219.226.255
netname:        ORG-SA57-AFRINIC-20050513
descr:          Assigned to Lagos dial-pool customers
country:        NG
admin-c:        NS4-AFRINIC
tech-c:         CM9-AFRINIC
status:         Assigned PA
mnt-by:         STARCOMMS-MNT
mnt-lower:      STARCOMMS-MNT
source:         AFRINIC # Filtered
parent:         41.219.192.0 – 41.219.255.255

person:         NAVNEET SINGH
address:        Plot 1261, Bishop Kale Close, off Saka Tinubu
address:        Victoria Island, Lagos, Nigeria
phone:          +2347028000725
fax-no:         +234-1-811-0301
e-mail:         ********@starcomms.com
nic-hdl:        NS4-AFRINIC
source:         AFRINIC # Filtered

person:         Catalin Miclaus
address:        Plot 1261C, Bishop Kale Close, off Saka Tinubu
phone:          +234-7028000733
fax-no:         +234-1-8110301
e-mail:         *******@starcomms.com
nic-hdl:        CM9-AFRINIC
source:         AFRINIC # Filtered

3. Email from to who responded to the Craigslist:

Hello Dear,

Calvary greetings to you,also to your house hold..Thanks for the email and it is my gladness  hearing from you.i am the owner of the house you are making enquiry of.Actually I  resided in the house with my family,such as my wife and my only daugther before and presently we had packed due to my transfer from my working place and now situated in the (West Africa Nigeria) and presently my house is still available for rent ..including the utilities like hydro,washerand security,it is furnised but  if u which to move in with your furnishes no problem..
Pls i want you to note that,i am a kind and honest  man and also i spent alot on my property that i want to give you for rent,i will want you to take absolute maintenance of my house and want you to treat it as your own,i,it is not the money the main problem  but i want you to keep it tidy all the time so that i will be glad  to see it neat when i come for a check up.
 
SO IF YOU ARE REALY INTRESTED I WILL WANT YOU TO FILL THE  RENTAPPLICATIONS FORMS BELOW
RENT APPLICATION FORM
 
Also,Pls let me get this answer.
 
FIRST NAME:__________________
MIDDLE NAME: _________________
LAST NAME: __________________
PROFESSION: ________________
HOME PHONE (____) __________
(CELL)PHONE (____) __________
(WORK)PHONE (____) __________
KIDS _____ (YES/NO), HOW MANY ________
PRESENT ADDRESS: _____________________
CITY: _______________
STATE: ______________
ZIPCODE: ____________
HOW LONG DO YOU INTEND STAYING? ____________
WHEN DO YOU INTEND MOVING IN? ______________
HOW SOON CAN YOU HAVE THE DEPOSIT PAYMENT SENT TO ME___________________________
HOW SOON DO YOU WANT TO RECEIVE THE KEYS AND DOCUMETS OF THE HOUSE___________________________
DO YOU HAVE A PET: _____________
NAME OF PET: _____________
KIND OF PETS: _____________
DO YOU SMOKE? ______________
DO YOU DRINK? ______________
Looking forward to hear from you with all this details so that i can have it in my file incase of issuing the receipt for you and contacting you.Await your urgent reply so that we can discuss on how to get the document and the key to you,Note that the keys and documents of my house is with me here in west africa.please we are giving you the house base on trust and again i will want you to stick to your words,you know that,we do not see yet and only putting everything into Gods hand,so please do notl et us down in this our property and God bless you more as you do this..

You will only be able to drive by the house for now but not have a look at the interior until i have sent the keys and documents of the house to you..and the requirement for getting the keys and documents of the house is a fully refundable payment of $700 USD,which is the security deposit. Please note that the deposit made is fully refundable should in case you finally gain entrance into the house after receiving the keys and documents and feel unsatisfied or uncomfortable with the interior,but i am giving you a benefit of doubt that you will love everything about this lovely home..

PLEASE NOTE THAT YOUR RENT ONLY STARTS COUNTING AFTER YOU HAVE FINALLY MOVED INTO THE HOUSE, SO YOU NEED NOT TO WORRY ABOUT MAKING THE DEPOSIT PAYMENT NOW AND NOT MOVING IN IMMEDIATELY,ALL YOU’RE DOING IS SECURING THE HOUSE IN YOUR FAVOR IN ORDER NOT TO LOOSE IT TO OTHER INTERESTED APPLICANTS..

YOU CAN CONTACT ME ON +234-808-398-0705  Or  011-234-808-398-0705.
Thanks And God Bless You

Samsung Omnia – Verizon Wireless

I recently changed my phone  to Samsung Omnia, amazingly it  has lot of cool features and performs decently without disappointing too much.

What is good.

1. Phone with a laptop style mouse, comes handy when it is hard  to press keys in virtual keyboard
2. Side bar like in VISTA – another  cool feature, comes handy with limited widgets!!!
3. FM Radio
4. About 4 types  of virtual keyboards, not all the best.. but once you get used to it, can’t complain much
5. WIFI – I think this is windows mobile – very easy to configure and pops up whenever it finds a network
6. Opera browser is nice, but  it is  much better with www.skyfire.com  – generally on any windows  mobile
7. Camera – never had  such a good experience in a Phone camera before.. really nice.. see some pictures I took

this is a one click – point and shoot panorama photo without much effort..

View Full Album

 

What is not good!

1. GPS – I wish it worked better without having to disconnect my phone/broadband – and again, when i do it doesn’t work with google maps or Live Search
2. Even after selecting  not to notify on a wireless network, it keeps notifying – little  annoying
3. Battery Life – it was good until i  configured my email, once i configured my email to check every 5 minutes. i have to recharge twice a day – this is again depends on individual user preference
4. FM Radio doesn’t work on Bluetooth stereo headset

Searching through Stored procedure code using TSQL script

Even if you have a refactoring tool, sometimes i wonder what are all the storedprocedures that uses this table that starts with ‘products_’  or ‘system_’… or all the tables that have a column name ‘status_id’….

so here is the script that scans all the stored procedure code, and check if the table name is found

Note  —this will also return if the table name is in the comment too.. so the logic is, even if you want to search any text (comment) in a stored procedure

 

 

1. ———————————————————————————————–
2. – Parameters
3. ———————————————————————————————–

DECLARE @tableNameLike VARCHAR(100), @columnNameLike VARCHAR(100)

– Filter only stored procedures that uses that table(s) and (optionaly) if it has a column

SET @tableNameLike = ‘enterYourTableName%’ — If you have organized all your tables with

— standard prefix, will be useful to filter

SET @columnNameLike = ‘enterYourColumnName%’ — Only if the table has this column

———————————————————————————————–

– Local Variables

———————————————————————————————–

DECLARE @spname VARCHAR(100),@TABLEname VARCHAR(100)

DECLARE @spnames TABLE(spname VARCHAR(100))

DECLARE @EXISTS INT

DECLARE @sp_start INT, @sp_END INT ,@t_start INT , @t_END INT,@sp_INdex INT , @t_INdex INT

———————————————————————————————–

– DROP before Creating Temporary Tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

– Get all the Stored procedure names

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #spnames

FROM sys.objects

WHERE type = ‘P’

– Get all the table names that passes the filter parameter

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #TABLEnames

FROM

sysobjects T

JOIN

INformation_schema.columns C

on

C.TABLE_NAME = T.name

WHERE T.name like @tableNameLike AND T.xtype = ‘U’

AND C.column_name like @columnNameLike

– SELECT * FROM #spnames

– SELECT * FROM #TABLEnames

———————————————————————————————–

– Get and Set Loop Index

———————————————————————————————–

SELECT @sp_start = 1,@sp_END = max(rownum)+1

FROM #spnames

SELECT @t_start =1,@t_END = max(rownum)+1 FROM #TABLEnames

– SELECT @sp_Start,@sp_END,@t_start,@t_END

———————————————————————————————–

– Loop through each stored procedure name

———————————————————————————————–

while @sp_start < @sp_END

BEGIN

– Get the stored procedure name

SELECT @spname = [name]

FROM

#spnames

WHERE rownum = @sp_start

– Loop through each tablename to check if it is used in the stored procedure code

while @t_start < @t_END

BEGIN

– Get the table name

SELECT @TABLEname = [name]

FROM

#TABLEnames

WHERE

rownum = @t_start

– Check if the table name exists in the stored procedure code

– I am using the SUM(CHARINDEX(xxx)) since the stored procedure code may span

– multiple rows

SELECT @EXISTS = SUM(CHARINDEX(@TABLEname,[text]))

FROM syscomments

WHERE id = object_id(@spname)

– If it is greater than zero then it is used in the stored procedure code

IF @EXISTS > 0

BEGIN

INSERT INTO @spnames

SELECT @spname

END

SET @t_start = @t_start+1 – Increment the table index

END

SET @sp_start = @sp_start+1 – Increment the storedprocedure index

SET @t_start = 1 – Reset the table index

END

———————————————————————————————–

– To list all the spnames (remember there will be duplicates)

———————————————————————————————–

SELECT DISTINCT * FROM @spnames

———————————————————————————————–

– Drop the temporary tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

———————————————————————————————–

– END

———————————————————————————————–

IceRocket Tags: TSQL Stored procedure SQL Server 2005/2008 programming

TSQL String Range Comparison with wildcard

A simple note, and couldn’t find straight answer when i googled!!. so here it is..

This is to get result from a table when filtering in string ranges that will have a wild card for ex: ‘*’

declare @sv varchar(17),@ev varchar(17)
declare @sindex int, @eindex int

set @sv = ‘ABCDE11A09′
set @ev = ‘ABCDE11A79′

– Check the * pos if exists,if doesn’t exists return the len +1 , assuming the * at the last
select @sindex = case when charindex(‘*’,@sv)> 0 then charindex(‘*’,@sv) else len(@sv)+1 end
select @eindex = case when charindex(‘*’,@ev)> 0 then charindex(‘*’,@ev) else len(@ev)+1 end

– remove the * from the range strings
select @sv = case when charindex(‘*’,@sv)> 0 then substring(@sv,0,@sindex) else @sv  end
select @ev = case when charindex(‘*’,@ev)> 0 then substring(@ev,0,@eindex) else @ev  end

select @sindex,@eindex,@sv,@ev
select mycolumn,substring(VIN,0,@sindex), substring(VIN,0,@eindex) from mytable
where
    substring(mycolumn,0,@sindex) >= @sv AND
    substring(mycolumn,0,@eindex) <= @ev
ORDER BY mycolumn

IceRocket Tags: TSQL,SQL SERVER,Programming

My Old Photos

I just happened to find my old geocities - my first website.. and all the photos i had .. i guess about 9 years ago…

——————————————————————————————————————————————

Kottayam (Kerala, India) Photos

View Full Album

 

In Madurai (India), 1994

 

 

 

 

——————————————————————————————————————————————

At Kodaikanal (India), 1999

With my MCA Classmates…
Here my friends of MCA – Madras Christian College..
Suresh, Parthasarathy, Ruban, Rajkumar, Paul, Sivakumar, Gladson, David, Mallik, Ashok, Sarangan, Damu, Daniel, Prem

Angela Fransica, Naju, Gigy, Golda, Shirin, Nisha, Deanne, Beulah, Ida, Chitra, Nalini, Sujatha, Rajalakshmi, Subhashini, Nathira, Annie, Mahalakshmi, Queeny, Sheeba, Leena

——————————————————————————————————————————————

Madurai (India), 1993

Here my friends – Bsc Physics- Sourashtra College Madurai ..
Surendranath, Balaji, Kishore, Navin, Senthil, Venkatesh C.R., Venkatesh S.S, Pathanjali,
Karthic Babu, Manikandan, Karthikeyan, ManickaRaj, Pathanjali, Santhanakrishnan, Sanjay…    Sheela, Rohini, Aarthy, Roopa, Eswari, Padma, Lalitha, Vanitha, Sujatha, Sujitha, Vimala

——————————————————————————————————————————————

 

This is with Naju and Arun!

 

 

 

 

 

 

——————————————————————————————————————————————

and here my Room in the Selaiyur hall (MCC) with Ajit abraham and Arun Thomas

——————————————————————————————————————————————

 

This is infront of the Selaiyur hall with Arularasn sitting on the rock, arul sekar standing behind him and our college Michel Jackson hanging on the tree.

  ——————————————————————————————————————————————

Selaiyur – Katans — My Hall mates – Selaiyur Hall, MCC.

IceRocket Tags: Friends,Personal,Family

——————————————————————————————————————————————

 

In PSI Data Systems!!!

 

 

 

——————————————————————————————————————————————