Dear Friends,
I have the following tables;
-
Tbl_Employee - Store the details of Employees
CREATE TABLE [dbo].[Tbl_Employee](
[Emp_Code] nvarchar NULL,
[Emp_Name] nvarchar NULL,
[PAN] nvarchar NULL,
[Place_Office] nvarchar NULL,
[Bank_Name] nvarchar NULL,
[IFSC] nvarchar NULL,
[Acc_No] nvarchar NULL,
[Sal_Cat] nvarchar NULL,
[Pay_Level] [int] NULL
) ON [PRIMARY] -
Tbl_Head - Master Table for Salary Heads
CREATE TABLE [dbo].[Tbl_Head](
[Head_ID] [int] NULL,
[Head_Name] nvarchar NULL,
[Description] nvarchar NULL
) ON [PRIMARY]
-
Tbl_FSE - Store the earnings against each head
CREATE TABLE [dbo].[Tbl_FSE](
[sle_id] [int] NULL,
[Emp_Code] nvarchar NULL,
[Head_ID] [int] NULL,
[FSE_Amount] [numeric](18, 2) NULL,
[ETerm] [int] NULL,
[ESeparator] varchar NULL,
[ESettlement] [int] NULL,
[Sal_Month] [date] NULL
) ON [PRIMARY] -
Tbl_FSD - Store the deductions against each head
CREATE TABLE [dbo].[Tbl_FSD](
[sld_id] [int] NULL,
[Emp_Code] nvarchar NULL,
[Head_ID] [int] NULL,
[FSD_Amount] [numeric](18, 2) NULL,
[Term] [int] NULL,
[Separator] varchar NULL,
[Settlement] [int] NULL,
[Sal_Month] [date] NULL
) ON [PRIMARY] -
Tbl_FSED - Store Netpay against Net Payable Head
CREATE TABLE [dbo].[Tbl_FSED](
[sled_id] [int] NULL,
[Emp_Code] nvarchar NULL,
[Head_ID] [int] NULL,
[FSED_Amount] [numeric](18, 2) NULL,
[Sal_Month] [date] NULL
) ON [PRIMARY]
I had store the values in tables as follows;
Query for extracting the above values;
select EMPCODE Emp_Code,EMP_NAME,SALMONTH,FSEHN,FSE_Amount,FSDHN,FSD_Amount,FSEDHN,FSED_Amount from
(select coalesce(E.Sal_Month, D.Sal_Month, ED.Sal_Month) As SALMONTH,
Coalesce(E.emp_code, D.emp_code, ED.emp_code) As EMPCODE,h1.Head_Name FSEHN,E.FSE_Amount,
h2.Head_Name FSDHN,D.fsd_amount,h3.Head_Name FSEDHN,
ED.fsed_amount From tbl_fse E left join Tbl_Head h1 on E.Head_ID=h1.Head_ID Full Join tbl_fsd D On E.sle_id = D.sld_id
and E.Emp_Code=D.Emp_Code and E.Sal_Month=D.Sal_Month left join Tbl_Head h2 on D.Head_ID=h2.Head_ID Full Join tbl_fsed ED
On E.sle_id = ED.sled_id And D.sld_id = ED.sled_id and ED.Emp_Code=D.Emp_Code and ED.Sal_Month=D.Sal_Month
left join Tbl_Head h3 on ED.Head_ID=h3.Head_ID) fd join Tbl_Employee te on fd.EMPCODE=te.Emp_Code
In the tables FSEHN, FSDHN,FSEDHN columns storing the Head Names. The heads need to come in Excel as title.
FSE_Amount,FSD_Amount,FSED_Amount storing the head values. The amount need to be come under the head name.
I want the output like as follows;
Problem is Tbl_Employee have one row value but Tbl_FSE,Tbl_FSD,Tbl_FSED tables having multiple rows. The rows value need to be title of the column in excel file.
I hope that i had clarified.
Thanks & Regards,