LEFT OUTER JOIN

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_NSSF
NSSF = 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 AS
SELECT A.TRN_STARTDATE AS TRN_STARTDATE, A.TRN_ENDDATE AS TRN_ENDDATE , A.EMP_NUMBER AS EMP_NUMBER,
A.TRN_PROC_EMP_AMT AS NSSF, ISNULL (B.TRN_PROC_EMP_AMT,0)  AS VOL_NSSF   FROM
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000003' ) A LEFT OUTER JOIN 
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000124' ) B
ON
A.EMP_NUMBER = B.EMP_NUMBER AND
A.TRN_STARTDATE = B.TRN_STARTDATE AND
A.TRN_ENDDATE = B.TRN_ENDDATE

Format Field Option in Crystal Reports

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

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 Next

Step 4 : Click the advance button and select the Types of data to Scripts option and set it to the Schema and data. Then give a path for save your query. 

Step 5 : Wait till the process compete and collect you query . 

How to View Logical File Names in MS SQL DB

Log to the MS SQL Server with Sa login and run the below query after selecting the Data base.


SELECT FILE_NAME(1) AS 'File Name 1', FILE_NAME(2) AS 'File Name 2';

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'