SQL CONVERT Function

My Tec Store
Syntax CONVERT(data_type(length),expression,style)SQL QueryUPDATE HS_HR_EMPLOYEESET EMP_EPF_NUMBER = CONVERT (VARCHAR (20),CONVERT(NUMERIC,NSSF_NUMBER))FROM AAA_NSSF_NO_UPLOADWHERE HS_HR_EMPLOYEE.EMP_NUMBER = AAA_NSSF_NO_UPLOAD.EMP_NUMBER

Shrink MS SQL Data Base

My Tec Store
Shrink SQL Data Base for reduce the Log File size.Log to SQL server with Sa account. Run below query with relevant values.USE <write the database name>;GO-- Truncate the log by changing the database recovery model to SIMPLE.ALTER DATABASE <write the database name>SET RECOVERY SIMPLE;GO-- Shrink the truncated log file to 10 MB.DBCC SHRINKFILE (<Database Logical Name>, 10);GO-- Reset the database ...

LEFT OUTER JOIN

My Tec Store
My Requirement is :All employees have some value for TRN_DTL_CODE = '000003'Only few employees have value for TRN_DTL_CODE = '000124'I want the data view for the format of TRN_STARTDATE,TRN_ENDDATE,EMP_NUMBER, NSSF, VOL_NSSFNSSF = Value of TRN_DTL_CODE = '000003'VOL_NSSF = Value of TRN_DTL_CODE = '000124'I used LEFT OUTER JOIN for create this.CREATE VIEW VW_HS_PR_VOLUN_NSSF ASSELECT A.TRN_STARTDATE AS TRN_STARTDATE, A.TRN_ENDDATE AS TRN_ENDDATE , ...

Format Field Option in Crystal Reports

My Tec Store
My requirement is to format all fields to a one font type, color and size.Right click the field which you want to apply the format of it for others. Then click the Paint brush icon in tool bar.Finally click the field which you want format.

Make Insert Queries for the Existing Data in MS SQL

My Tec Store
Step 1 : Right Click the MS SQL Data Base. Go --> Tasks --> Generate Scripts Step 2 : Click the Next button.Step 3 : Select the table/s data which you want to take as insert query. Then Click NextStep 4 : Click the advance button and select the Types of data to Scripts option and set it to the ...

Formula for Crystal Report Field Alignment

My Tec Store
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 optionI put the below formula for fulfill my requirement.IF {VW_RPT_PAYSLIP.ITEM} = ' Interest' THEN crJustifiedELSE IF {VW_RPT_PAYSLIP.ITEM} = ' Balance' THEN crJustified

Percentage Query in SQL

My Tec Store
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

Bank Diskette Generate from Crystal Reports

My Tec Store
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)

My Tec Store
CREATE FUNCTION HS_FN_LEV_APPROVING_PERSON_PARAPET (@EMP_NUMBER VARCHAR(6))RETURNS VARCHAR(6)AS BEGINDECLARE @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_NUMBERSET @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_H

DB Restore Error | Microsoft SQL Server, Error 3205

My Tec Store
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 ...

Default Web Site is not working with Share Point

My Tec Store
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

REPLICATE

My Tec Store
You can fix the character length by using below UPDATE HS_HR_CORPORATE_TITLE_NEPALSET CT_CODE = REPLICATE ('0',6- LEN (CT_CODE + 306)) + CAST ((CT_CODE + 306) AS VARCHAR)

UPPER Case in MS SQL

My Tec Store
By using the UPPER(), you can retrieve your character data with caps. SELECT RTRIM(LTRIM(UPPER(DSG_NAME))) FROM HS_HR_DESIGNATION

GROUP BY

My Tec Store
SELECT DBGROUP_ID,COUNT(*)FROM HS_HR_DESIGNATIONGROUP BY DBGROUP_IDApply the Conditions with GROUP BYSELECT DBGROUP_ID,COUNT(*)FROM HS_HR_DESIGNATIONGROUP BY DBGROUP_IDHAVING COUNT(*) > 6

Alter Column Ms SQL

My Tec Store
Fulfill the requirement of changing data type of particular field. ALTER TABLE EMP_NEPALALTER COLUMN SAL_GRD_CODE VARCHAR(10)

Oracle Update Query

My Tec Store
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 JSET J.EMP_NUMBER = (SELECT E.EMP_NUMBERFROM HS_HR_EMPLOYEE EWHERE E.EMP_DISPLAY_NUMBER = J.EMP_DISPLAY_NUMBER)WHERE EXISTS(SELECT E.EMP_NUMBERFROM HS_HR_EMPLOYEE EWHERE E.EMP_DISPLAY_NUMBER = J.EMP_DISPLAY_NUMBER)