Formula for Crystal Report Field Alignment

My requirement is to change the alignment of the filed dynamically.

So that I right click the field and select the the Format Field option. Then I select the Horizontal Alignment option

I put the below formula for fulfill my requirement.

IF {VW_RPT_PAYSLIP.ITEM} = '    Interest' THEN
 crJustified
ELSE IF {VW_RPT_PAYSLIP.ITEM} = '    Balance' THEN
 crJustified

Percentage Query in SQL

SELECT (C.A/C.B)*100,C.EMP_GENDER FROM
(SELECT GEN.*,AALL.* FROM
(select CAST(COUNT(EMP_GENDER) AS DECIMAL) AS A,EMP_GENDER from hs_hr_employee
GROUP BY EMP_GENDER) GEN ,
(SELECT CAST(COUNT(EMP_GENDER) AS DECIMAL) AS B FROM HS_HR_EMPLOYEE) AALL) C

SQL User Mapping After the DB Restore

SQL Syntax :
sp_change_users_login 'Auto_Fix', '<Login Name>' , NULL, '<Password>'

Example :
sp_change_users_login 'Auto_Fix', 'HRMV6' , NULL, 'HRMV6'

Bank Diskette Generate from Crystal Reports

Configure the Formula with below or similar syntax according to the requirement.

{HS_HR_EMPLOYEE.EMP_CALLING_NAME}+' '+
{HS_HR_EMPLOYEE.EMP_SURNAME}+','+
right('0000000000000'+ltrim(rtrim({HS_PR_BANK_TRANSFERS.EBANK_ACC_NO})),13)+','+{HS_HR_BRANCH.BBRANCH_AUTO_CLR_HOUSE_CODE}+','+
totext({HS_PR_BANK_TRANSFERS.EBT_AMOUNT},"0.00")+','+{HS_HR_EMPLOYEE.EMP_PAYROLLNO}

Approal Function (SQL)

CREATE FUNCTION HS_FN_LEV_APPROVING_PERSON_PARAPET (@EMP_NUMBER VARCHAR(6))
RETURNS VARCHAR(6)
AS BEGIN

DECLARE @EMPLOYEE AS VARCHAR(6)
DECLARE @CORPORATE AS VARCHAR(6)
DECLARE @SUP_EMPLOYEE AS VARCHAR(6)
DECLARE @HR AS VARCHAR(6)
DECLARE @HOD AS VARCHAR(6)
DECLARE @CEO AS VARCHAR(6)
DECLARE @APPROVER AS VARCHAR(6)

SET @EMPLOYEE = @EMP_NUMBER
SET @SUP_EMPLOYEE =  ( SELECT EREP_SUP_EMP_NUMBER FROM HS_HR_EMP_REPORTTO WHERE EREP_REPORTING_MODE=1
AND EREP_SUB_EMP_NUMBER= @EMPLOYEE)
SET @CORPORATE = (SELECT CT_CODE FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE )
SET @HR = ( SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001' )
SET @CEO = (SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001')
SET @HOD = (SELECT HIE_HEAD_EMP_NUMBER FROM HS_HR_COMPANY_HIERARCHY WHERE DEF_LEVEL = 4
AND HIE_CODE IN (SELECT HIE_CODE_4 FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE ))

IF @CORPORATE IN ('000004','000008','000009','000010','000012','000014','000016')
SET @APPROVER = @SUP_EMPLOYEE
ELSE IF @EMPLOYEE = @HOD
SET @APPROVER = @CEO
ELSE
SET @APPROVER = @HOD

RETURN (@APPROVER)
END

GO

DB Restore Error | Microsoft SQL Server, Error 3205

I'm tried to restore the DB backup for MS SQL Server 2008. Unfortunately it appeared below error message.

I checked this again and found the reason of this. Reason was my SQL DB Backup is taken from the latest version of the MS SQL Server and I'm trying to restore it to the previous MS SQL Server version. As a solution of that I run the latest service pack for my MS SQL Server which used to restore my Data Base. You can also found this service packs by using the below link.

http://support.microsoft.com/kb/968382

Default Web Site is not working with Share Point

IIS Default Web Site is not working after the installation of Share Point. I couldn't  go the local host. The reason of this problem is Default port 80 bind with the Share Point. So that Default Web Site is not allow to use the port 80.

 










In this case what I'm do is assign another port for Default Web Site. After that my local host work with http://8282:localhost

Text Formating for Fixed Length in Excel

Excel function for format the cell content with fixed length
=TEXT(B2,"000000")

REPLICATE

 You can fix the character length by using below

UPDATE HS_HR_CORPORATE_TITLE_NEPAL
SET CT_CODE = REPLICATE ('0',6- LEN (CT_CODE + 306)) + CAST ((CT_CODE + 306) AS VARCHAR)

UPPER Case in MS SQL

 By using the UPPER(), you can retrieve your character data with caps.

SELECT RTRIM(LTRIM(UPPER(DSG_NAME))) FROM HS_HR_DESIGNATION

GROUP BY

SELECT DBGROUP_ID,COUNT(*)
FROM HS_HR_DESIGNATION
GROUP BY DBGROUP_ID

Apply the Conditions with GROUP BY

SELECT DBGROUP_ID,COUNT(*)
FROM HS_HR_DESIGNATION
GROUP BY DBGROUP_ID
HAVING COUNT(*) > 6 

Alter Column Ms SQL

 Fulfill the requirement of changing data type of particular field.

ALTER TABLE EMP_NEPAL
ALTER COLUMN SAL_GRD_CODE VARCHAR(10)

Oracle Update Query

The syntax for the update in Oracle is little bit differ than MS SQL. The below will describes simple query which was fulfilled my requirement.

UPDATE JAN_TAX_2012 J
SET J.EMP_NUMBER = 
(
SELECT E.EMP_NUMBER
FROM HS_HR_EMPLOYEE E
WHERE E.EMP_DISPLAY_NUMBER = J.EMP_DISPLAY_NUMBER
)
WHERE EXISTS
(
SELECT E.EMP_NUMBER
FROM HS_HR_EMPLOYEE E
WHERE E.EMP_DISPLAY_NUMBER = J.EMP_DISPLAY_NUMBER
)

SELECT TOP in MS SQL

Select the top rows from your select query

SELECT TOP 1 ELG_ORDER FROM HS_HR_BM_MEDICAL_ELG_DST WHERE EMP_NUMBER = @EMP_NUMBER ORDER BY ELG_ORDER DESC

SQL CURSOR Syntax

Follow the below MS SQL syntax for create new Cursor

DECLARE emFIRST CURSOR FOR
SELECT EMP_NUMBER FROM HS_HR_EMPLOYEE
OPEN emFIRST
FETCH NEXT FROM emFIRST INTO  @EMP_NUMBER
WHILE (@@fetch_status <> -1)      
BEGIN
SELECT @CORPORATE = CT_CODE FROM HS_HR_EMPLOYEE
PRINT @EMP_NUMBER
PRINT '---------------------'
FETCH NEXT FROM emFIRST INTO  @EMP_NUMBER  
END
CLOSE  emFIRST
DEALLOCATE emFIRST

Dynamic SUBSTRING() in MS SQL

My requirement is to use the SUBSTRING() dynamically in SQL query.
I have the set of data in a table. I want to pick only the last 6 digits.

General syntax of the SUBSTRING function
SUBSTRING ( expression ,start , length )
I'm using below query according to my requirement.

SELECT SUBSTRING(VALUE,LEN(VALUE)-6+1,LEN(VALUE)+1) FROM TEST

How to get the MS SQL Server Version

Use the below Query
SELECT @@version

Get Specify Date by using GETDATE() in MS SQL

My requirement is to get the 1st date of the year. I'm using below command line to fulfill this task.
SELECT CONVERT(VARCHAR,YEAR(GETDATE())-1)+'/01/01'  

When it become the last day of the year, I can change the query like this.
SELECT CONVERT(VARCHAR,YEAR(GETDATE())-1)+'/12/01' 


Then I want the 1st February in this year. It can be taken by using
SELECT CONVERT(VARCHAR,YEAR(GETDATE())-1)+'/02/01'  

I believe this command will help you to cater your various requirements. 

Table Backup in SQL

You can create table backups by using below commands.

In MS SQL
SELECT * INTO <backup_table_name> FROM <original_table_name>
Ex:
SELECT * INTO FUEL_DETAILS_BACK FROM FUEL_DETAILS 

In Oracle
CREATE TABLE   <backup_table_name>  AS SELECT * FROM <original_table_name>
Ex:
CREATE TABLE HR_LEAVE_ENTITLE2011 AS 
SELECT * FROM HR_LEAVE_ENTITLE

Register the .Net Framework in Windows Server Editions

Open the Command Prompt and Run the below command.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -i

Enable 32 bit in IIS Windows Server 2003 64 bit

Open the command prompt and run below command to enable 32 bits applications hosted  in IIS of Windows Server 2003 64 bit edition since the option is not available through the interface.

cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1

CONCATENATE() & SUBSTITUTE() in MS EXCEL

I'm using Microsoft Excel for formatted my data sheet. 

I want to merge few cells data to a one cell. The way of I do this

I want to remove some selected values in a cell. The way of I do this

DENSE_RANK()

My requirement is to order the contents of my selected SQL query. I can use ORDER BY for fulfill this task, but the problem is it will order my entire SQL query with ascending order or descending order according the way of using it. But my requirement is order the separate sub groups of my selected query. 

Let take this example. 

I want to order the each and every employees dependents, using their birthdays in ascending order with partition by the employee number. 

So I'm using the below SQL command. 

DENSE_RANK() OVER (PARTITION BY EMP_NUMBER ORDER BY DOB)

DENSE_RANK() OVER (PARTITION BY < PARTITION FIELD NAME> ORDER BY <ORDER FIELD NAME>)

Finally my result came as 

Rename Schema in MS SQL Server

My requirement is to rename the schema in selected tables/views.
Let take a example like this.
Existing table name - HRADMIN.VW_HR_EMPLOYEE
Required table name - HRUSR. VW_HR_EMPLOYEE

You can simply do this by using below SQL command by login with sa account. Make sure your rename schema is exists under your Data Base. Otherwise you need to create it manually.

ALTER SCHEMA <HRUSR> TRANSFER  <HRADMIN>.< VW_HR_EMPLOYEE >

But it's not flexible enough  when we are considering the large database which consisted lot of tables/views under same schema. The below Stored Procedure will help you to generate one script for entire schema which you want to rename.

<Views/tables which exists under ADM schema will rename the schema of CHATHURA >


ALTER PROCEDURE SP_CHANGE_SCHEMA
AS
DECLARE cursore CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA  =  'ADM'

DECLARE @schema sysname,
 @tab sysname,
 @sql varchar(500)

OPEN cursore  
FETCH NEXT FROM cursore INTO @schema, @tab

WHILE @@FETCH_STATUS = 0  
BEGIN

 SET @sql = 'ALTER SCHEMA CHATHURA TRANSFER ' + @schema + '.' + @tab  
 PRINT @sql  
 FETCH NEXT FROM cursore INTO @schema, @tab  
END

CLOSE cursore  
DEALLOCATE cursore

After creating the SP you can execute it by using this command.

EXEC SP_CHANGE_SCHEMA