DECLARE
@DivisionId INT,
@InsuranceAuthorityID INT,
@Fromdate DATETIME,
@Todate DATETIME
SELECT @DivisionId = NULL ,
@InsuranceAuthorityID = 39,
@Fromdate = '2012-06-01 00:00:00.000' ,
@Todate = NULL
SELECT @ToDate = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE())) WHERE
@ToDate IS NULL
SELECT DISTINCT
D.CalculatePremiumId,
A.EmployeeId,
D.Name AS 'Employee Name',
D.RelationShip,
REPLACE((CONVERT(VARCHAR(11), A.DateofBirth, 106)),'','-')AS DateOfBirth,
B.Description AS 'SumInsured',
(NumberOfPersonsInsured) AS 'Beneficiary',
D.HighestAge,
d.BasicPremium,
d.AdditionalMemberLoading ,
d.MembersPremium,
d.AdditionalPremiumForExtraCovers,
d.TotalPremium,
d.NetPremium,
d.ServiceTax,
d.FinalPremium
INTO #FinalDataMedicalInsurance
FROM OAS_Employee_Info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty AS A WITH(NOLOCK)
LEFT JOIN
OAS_PERSONAL_INFO.dbo.tbl_Personal_PersonalMaster AS FD
WITH(NOLOCK) ON FD.PersonalID = A.PersonalId
AND
ISNULL(fd.activeyn,1) = 1
AND
ISNULL(fd.deletedyn,0) = 0
LEFT JOIN
oas_incometax.dbo.tbl_PaySlip_MedicalInsuranceCalculatePremium_Details
D WITH(NOLOCK) ON A.EmployeeId =D.EmployeeID
AND ISNULL(D.ActiveYN,1) = 1
AND ISNULL(D.DeletedYN,0) = 0
LEFT JOIN
oas_employee_info.dbo.tbl_EmpInfo_familydetails C
WITH(NOLOCK) ON C.PersonalID = A.PersonalId
AND ISNULL(C.ActiveYN,1) =1
AND ISNULL(C.DeletedYN,0) = 0
LEFT JOIN OAS_Admin.dbo.tbl_Common_LookUp_Details B
WITH(NOLOCK) ON B.DetailId = D.SumInsuredId
AND ISNULL(C.ActiveYN,1) =1
AND ISNULL(C.DeletedYN,0) = 0
WHERE
(@DivisionId IS NULL OR A.DivisionId = @DivisionId)
AND D.InsuranceAuthorityId =@InsuranceAuthorityID
AND (D.FromDate = '2012-06-01 00:00:00.000')
UNION ALL
SELECT DISTINCT
MIC.CalculatePremiumId,
EJ.EmployeeId,
MCP.Name,
MCP.RelationShip,
MCP.DateofBirth,
'','','','','','','','','','',''
FROM
tbl_PaySlip_MedicalInsuranceCalculatePremiumChild_Details AS MCP
WITH(NOLOCK)
INNER JOIN
dbo.tbl_PaySlip_MedicalInsuranceCalculatePremium_Details AS
MIC WITH(NOLOCK) ON MIC.CalculatePremiumId = MCP.CalculatePremiumId
AND ISNULL(MIC.ActiveYN,1) = 1
AND ISNULL(MIC.DeletedYN,0) = 0
INNER JOIN
OAS_Employee_Info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty AS EJ
WITH(NOLOCK) ON EJ.EmployeeId = MCP.EmployeeId
WHERE
MIC.InsuranceAuthorityId = @InsuranceAuthorityId
AND ISNULL(MCP.ActiveYN,1) = 1
AND ISNULL(MCP.DeletedYN,0) = 0
AND (@DivisionId IS NULL OR EJ.DivisionId = @DivisionId)
AND (MIC.FromDate = '2012-06-01 00:00:00.000')
ORDER BY D.CalculatePremiumId DESC
SELECT CalculatePremiumId, EmployeeId,
[Employee Name], RelationShip, DateOfBirth ,SumInsured
,Beneficiary ,HighestAge ,BasicPremium,
AdditionalMemberLoading, MembersPremium,
AdditionalPremiumForExtraCovers, TotalPremium, NetPremium,
ServiceTax ,FinalPremium
FROM #FinalDataMedicalInsurance WHERE RelationShip = 'Self'
UNION ALL
SELECT CalculatePremiumId, EmployeeId,
[Employee Name], RelationShip , DateOfBirth ,SumInsured
,Beneficiary ,HighestAge ,BasicPremium,
AdditionalMemberLoading, MembersPremium,
AdditionalPremiumForExtraCovers, TotalPremium, NetPremium,
ServiceTax ,FinalPremium
FROM #FinalDataMedicalInsurance WHERE RelationShip <> 'Self'
ORDER BY EmployeeId desc
,case when [Relationship] = 'self' then 1 else 2 end
Error: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.