Note- execution timeing is less than 5 sec
--- usp_AssociateAgeingDashBoard2 4002189,0,0
alter PROCEDURE usp_AssociateAgeingDashBoard
@intMappingId AS BIGINT, --@MappingId
@intClientId BIGINT=0, --@intClientId
@intFacilityId BIGINT=0 --@intBranchId
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ClientId
(
ClientId int
)
IF @intClientId=0
BEGIN
INSERT INTO #ClientId(ClientId)
SELECT ClientID FROM EmployeeClientMapping WITH (NOLOCK) WHERE EmployeeId =@intMappingId AND IsActive =1
END
ELSE
BEGIN
INSERT INTO #ClientId(ClientId) VALUES (@intClientId)
END
CREATE TABLE #FacilityId
(
FacilityId int
)
IF @intFacilityId=0
BEGIN
INSERT INTO #FacilityId(FacilityId)
SELECT FacilityId FROM EmployeeFacilityMapping WITH (NOLOCK) WHERE EmployeeId =@intMappingId AND IsActive =1
END
ELSE
BEGIN
INSERT INTO #FacilityId(FacilityId) VALUES (@intFacilityId)
END
SELECT * INTO #Ageing FROM (
select
CASE WHEN DATEDIFF(YY,DOB,GETDATE()) >=18 AND DATEDIFF(YY,DOB,GETDATE()) <=20 THEN '18-20'
WHEN DATEDIFF(YY,DOB,GETDATE()) >20 AND DATEDIFF(YY,DOB,GETDATE()) <=25 THEN '21-25'
WHEN DATEDIFF(YY,DOB,GETDATE()) >25 AND DATEDIFF(YY,DOB,GETDATE()) <=30 THEN '26-30'
WHEN DATEDIFF(YY,DOB,GETDATE()) >30 AND DATEDIFF(YY,DOB,GETDATE()) <=35 THEN '31-35'
WHEN DATEDIFF(YY,DOB,GETDATE()) >35 AND DATEDIFF(YY,DOB,GETDATE()) <=40 THEN '35-40'
WHEN DATEDIFF(YY,DOB,GETDATE()) >40 AND DATEDIFF(YY,DOB,GETDATE()) <=45 THEN '41-45'
WHEN DATEDIFF(YY,DOB,GETDATE()) >45 AND DATEDIFF(YY,DOB,GETDATE()) <=50 THEN '46-50'
WHEN DATEDIFF(YY,DOB,GETDATE()) >50 AND DATEDIFF(YY,DOB,GETDATE()) <=55 THEN '51-55'
WHEN DATEDIFF(YY,DOB,GETDATE()) >55 AND DATEDIFF(YY,DOB,GETDATE()) <=60 THEN '55-60'
END AS 'AgeGroup',
COUNT(
CASE WHEN DATEDIFF(YY,DOB,GETDATE()) >=18 AND DATEDIFF(YY,DOB,GETDATE()) <=20 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >20 AND DATEDIFF(YY,DOB,GETDATE()) <=25 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >25 AND DATEDIFF(YY,DOB,GETDATE()) <=30 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >30 AND DATEDIFF(YY,DOB,GETDATE()) <=35 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >35 AND DATEDIFF(YY,DOB,GETDATE()) <=40 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >40 AND DATEDIFF(YY,DOB,GETDATE()) <=45 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >45 AND DATEDIFF(YY,DOB,GETDATE()) <=50 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >50 AND DATEDIFF(YY,DOB,GETDATE()) <=55 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >55 AND DATEDIFF(YY,DOB,GETDATE()) <=60 THEN 1
END) AS 'Count',
CASE WHEN DATEDIFF(YY,DOB,GETDATE()) >=18 AND DATEDIFF(YY,DOB,GETDATE()) <=20 THEN 1
WHEN DATEDIFF(YY,DOB,GETDATE()) >20 AND DATEDIFF(YY,DOB,GETDATE()) <=25 THEN 2
WHEN DATEDIFF(YY,DOB,GETDATE()) >25 AND DATEDIFF(YY,DOB,GETDATE()) <=30 THEN 3
WHEN DATEDIFF(YY,DOB,GETDATE()) >30 AND DATEDIFF(YY,DOB,GETDATE()) <=35 THEN 4
WHEN DATEDIFF(YY,DOB,GETDATE()) >35 AND DATEDIFF(YY,DOB,GETDATE()) <=40 THEN 5
WHEN DATEDIFF(YY,DOB,GETDATE()) >40 AND DATEDIFF(YY,DOB,GETDATE()) <=45 THEN 6
WHEN DATEDIFF(YY,DOB,GETDATE()) >45 AND DATEDIFF(YY,DOB,GETDATE()) <=50 THEN 7
WHEN DATEDIFF(YY,DOB,GETDATE()) >50 AND DATEDIFF(YY,DOB,GETDATE()) <=55 THEN 8
WHEN DATEDIFF(YY,DOB,GETDATE()) >55 AND DATEDIFF(YY,DOB,GETDATE()) <=60 THEN 9
END AS 'Sequence'
from [vwAssociateDetailsBasic] AM WITH (NOLOCK)
INNER JOIN #ClientId Client On Client.ClientId= AM.ClientID
INNER JOIN #FacilityId Facility ON Facility.FacilityId = AM.FacilityID
WHERE ISACTIVE = 1
GROUP BY DOB)A
select AgeGroup,SUM(Count) as 'Count',Sequence from #Ageing WITH (NOLOCK)
WHERE AgeGroup IS NOT NULL
GROUP BY AgeGroup,Sequence
ORDER BY Sequence
DROP TABLE #Ageing
DROP TABLE #ClientId
DROP TABLE #FacilityId
END