How to get CF Leave with deduction of burnt leave (Available Leave only applicable for 3 month)

micnie_2020 · July 8, 2019, 7:38am

Hi All,

I have this Data:

DECLARE @userData TABLE( UserName varchar(max) NOT NULL, ReferDate Date not null, Expired Date not null, Category varchar(max) not null);

insert into @userData
(UserName,ReferDate,Expired,Category)
select UserName='PersonA',ReferDate='2019-03-01',Expired='2019-06-30',Category='Work' union all
select UserName='PersonA',ReferDate='2019-03-01',Expired='2019-06-30',Category='Work Half' union all
select UserName='PersonA',ReferDate='2019-04-01',Expired='2019-07-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Expired='2019-07-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Expired='2019-07-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-05-01',Expired='2019-08-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-05-01',Expired='2019-08-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-06-01',Expired='2019-09-30',Category='Work' union all
select UserName='PersonA',ReferDate='2019-06-01',Expired='2019-09-30',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Expired='2019-10-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Expired='2019-10-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Expired='2019-10-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-07-01',Expired='2019-10-31',Category='Off 0.5 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Expired='2019-11-30',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Expired='2019-11-30',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Expired='2019-11-30',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Expired='2019-11-30',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-09-01',Expired='2019-12-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Expired='2019-12-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Expired='2019-12-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Expired='2020-01-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Expired='2020-01-31',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-11-01',Expired='2020-02-29',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Expired='2020-03-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Expired='2020-03-31',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Expired='2019-12-31',Category='Off 1 day'

I managed to generate Available & Taken Leave as Below:

Declare @Date Date
set @Date = '2019-07-01'

select UserName,Category='CF',Mth=Month(@Date), Yr=Year(@Date),cnt=sum(cnt) from (
select UserName,Category='CF',Mth=Month(DateAdd(month,-3,@Date)), Yr=Year(DateAdd(month,-3,@Date)),cnt=sum(cnt) from (
select UserName,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData where ReferDate <= dbo.udf_GetLastDayOfMonth(@Date)
group by UserName,Category
union all
select UserName,cnt=case when Category='Off 1 day' then -(count()/1.0) when Category='Off 0.5 day' then -(count()/2.0) else 0 end
from @userData where ReferDate <= dbo.udf_GetLastDayOfMonth(@Date)
group by UserName,Category
)A
group by UserName
--union all
--minus Burnt Leave query here -- How to do this portion ???
)D
group by UserName

Output:
May I know how to get below output CF?

The Available Leave need to clear within 3 month. If more than 3 month, it'll consider Burnt.

How to create a Burnt Leave query?

Please advise.

The validity only up to 3 full month, after will be disposed the balance day

Thanks.

Regards,
Micheale

micnie_2020 · July 8, 2019, 10:52am

Hi All,

Sorry.

I have drafted the result using this print screen.

I am getting stucked. How to calculate the forfeited days (Burnt) and CF as shown in the print screen using url above line? If the days doesn't utilize and after the expired date. The days balance shall forfeited. The work day only able to claimed within 3 month either off 1 day or off 0.5 day, Balance allow to CF if still under the 3 month period.

Please advise.

Thanks.

Regards,
Micheale

micnie_2020 · July 9, 2019, 1:23pm

Hi Expert,

Warmest Regards. I'm still unable to find the solution. Solution require to used Rolling up methodology or what is the accurate term for it so I able continue search from search engine?

I have summarised the data above into 2 table as below:-

TableA (Accumulated Earned Leave):
UserName StartDate Expired Date Accumulated Earned Leave
PersonA 2019-03-01 2019-05-31 1.500000
PersonA 2019-04-01 2019-06-30 3.000000
PersonA 2019-05-01 2019-07-31 0.000000
PersonA 2019-06-01 2019-08-31 2.000000
PersonA 2019-07-01 2019-09-30 2.000000
PersonA 2019-08-01 2019-10-31 2.000000
PersonA 2019-09-01 2019-11-30 2.000000
PersonA 2019-10-01 2019-12-31 0.000000
PersonA 2019-11-01 2020-01-31 1.000000
PersonA 2019-12-01 2020-02-29 2.000000

Table B (Used Leave):
UserName TakenDate Taken
PersonA 2019-03-01 0.000000
PersonA 2019-04-01 0.000000
PersonA 2019-05-01 2.000000
PersonA 2019-06-01 0.000000
PersonA 2019-07-01 1.500000
PersonA 2019-08-01 2.000000
PersonA 2019-09-01 2.000000
PersonA 2019-10-01 2.000000
PersonA 2019-11-01 0.000000
PersonA 2019-12-01 0.000000

Let said, I want to view

Hope my explanation clear.

Thanks in advance for your help.

Regards,
Micheale

harishgg1 · July 9, 2019, 4:43pm

hi Micheale

i am trying to do this

once i have something
i will post it

most probably tomorrow
its bedtime for me now

micnie_2020 · July 10, 2019, 12:15pm

Hi Haris,

Yes, above what you did is correctly. But caption is wrong. 1st column 201903-201905, 2nd column 201904-201906, etc.... Mean, the total days for the month valid within 3 month.

Thanks in advanced.

Thanks again.

Regards,
Micheale

harishgg1 · July 10, 2019, 3:24pm

hi

i have gotten the rows

now only left is union all part

drop create data ..

drop table #AccumulatedEarnedLeave 
go 

create table #AccumulatedEarnedLeave
(
UserName varchar(100),	
StartDate	date,
ExpiredDate date,
AccumulatedEarnedLeave decimal(10,2)
)
go 

insert into #AccumulatedEarnedLeave select 'PersonA','2019-03-01','2019-05-31',1.500000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-04-01','2019-06-30',3.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-05-01','2019-07-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-06-01','2019-08-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-07-01','2019-09-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-08-01','2019-10-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-09-01','2019-11-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-10-01','2019-12-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-11-01','2020-01-31',1.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-12-01','2020-02-29',2.000000



drop table #TakenLeave 

create table #TakenLeave 
(
username varchar(100),
takendate date ,
takenleave decimal(10,2)
)


insert into #TakenLeave select 	'PersonA','2019-03-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-04-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-05-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-06-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-07-01',1.500000
insert into #TakenLeave select 	'PersonA','2019-08-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-09-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-10-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-11-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-12-01',0.000000

SQL ...

DROP TABLE #abc

-- create data set

select datepart(mm,a.StartDate) as mon,a.AccumulatedEarnedLeave,b.takenleave,
sum(a.AccumulatedEarnedLeave) over( order by startdate ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
as Cumulative into #abc from #AccumulatedEarnedLeave a join #TakenLeave b
on a.StartDate = b.takendate

select * from #abc

DECLARE @cols AS NVARCHAR(max)

SELECT @cols = Stuff((SELECT ',' + Quotename(Cast( mon AS VARCHAR ))
FROM #abc
ORDER BY mon
FOR xml path('')), 1, 1, '')

DECLARE @cols1 AS NVARCHAR(max)
SELECT @cols1 = Stuff((SELECT ',max(' + Quotename(Cast( mon AS VARCHAR )) +')'+ ' as ' + Quotename(Cast( mon AS VARCHAR ))
FROM #abc
ORDER BY mon
FOR xml path('')), 1, 1, '')

DECLARE @query NVARCHAR(max)

set @query
= 'select ''EarnedLeave'',' + @cols1 +'
from
(
select * from #abc
) src
pivot
(
max(AccumulatedEarnedLeave) for mon in ('+@cols+')
) piv '

PRINT @query

EXEC Sp_executesql
@query;

DECLARE @query1 NVARCHAR(max)

set @query1
= 'select ''TakenLeave'',' + @cols1 +'
from
(
select * from #abc
) src
pivot
(
max(TakenLeave) for mon in ('+@cols+')
) piv '

PRINT @query1

EXEC Sp_executesql
@query1;

DECLARE @query2 NVARCHAR(max)

set @query2
= 'select ''Cumulative'',' + @cols1 +'
from
(
select * from #abc
) src
pivot
(
max(Cumulative) for mon in ('+@cols+')
) piv '

PRINT @query2

EXEC Sp_executesql
@query2;

micnie_2020 · July 11, 2019, 4:05am

Hi Haris,

Many thanks, really appreciate for your effort.

Can you re-share your Accumulative query please?

I transform the data according to your solution:

DECLARE @userData TABLE( UserName varchar(max) NOT NULL, ReferDate Date not null, Category varchar(max) not null);
insert into @userData
(UserName,ReferDate,Category)
select UserName='PersonA',ReferDate='2019-03-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-03-01',Category='Work Half' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-05-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-05-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-06-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-06-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Off 0.5 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-11-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Off 1 day'

Declare @Date Date
set @Date = '2019-07-01'

Declare @daysinlieu TABLE(UserName varchar(max) NOT NULL, StartTransactionDate Date not null,Total float not null default 0.0);
insert into @daysinlieu
(UserName,StartTransactionDate,Total)
select UserName,StartTransactionDate,Total=sum(cnt) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate

Declare @cumulative TABLE(UserName varchar(max) NOT NULL, StartTransactionDate Date not null,Cumulative float not null default 0.0);
insert into @cumulative
(UserName,StartTransactionDate,Cumulative)
SELECT r1.UserName,StartTransactionDate=DateAdd(month,-2,r1.StartTransactionDate), (SELECT sum(Acc) FROM
(
select UserName,Category='Earned Leave',StartTransactionDate,Acc=sum(Isnull(cnt,0)) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate
) r2
WHERE r2.UserName=r1.UserName and r2.StartTransactionDate between DateAdd(month,-2,r1.StartTransactionDate) and dbo.udf_GetLastDayOfMonth(r1.StartTransactionDate)) AS Cumulative
FROM (
select UserName,Category='Earned Leave',StartTransactionDate,Acc=sum(cnt) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate
) r1

Declare @Taken TABLE(UserName varchar(max) NOT NULL, Category varchar(max) NOT NULL, TakenDate Date not null,Taken float not null default 0.0);
insert into @Taken
(UserName,Category,TakenDate,Taken)
select UserName,Category='Taken Leave',TakenDate=ReferDate,Taken=sum(cnt) from(
select UserName,ReferDate,cnt=case when Category='Off 1 day' then (count()/1.0) when Category='Off 0.5 day' then (count()/2.0) else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,ReferDate

Declare @summaryMtrx TABLE(UserName varchar(max) NOT NULL, Category varchar(max) NOT NULL, TransactionDate nvarchar(max) not null,Total float not null default 0.0);
insert into @summaryMtrx
(UserName,Category,TransactionDate,Total)
select UserName,Category='Earned Leave',TransactionDate=LEFT(CONVERT(char(8), StartTransactionDate,112),6),Total=Total/1.0 from @daysinlieu
union all
select UserName,Category='Cumulative',TransactionDate=LEFT(CONVERT(char(8), StartTransactionDate,112),6),Cumulative=Cumulative/1.0 from @cumulative
union all
select UserName,Category='Taken Leave',TransactionDate=LEFT(CONVERT(char(8), TakenDate,112),6),Taken=-Taken/1.0 from @Taken

DECLARE @cols AS NVARCHAR(max)

SELECT @cols = Stuff((SELECT ',' + Quotename(Cast(TransactionDate AS VARCHAR ))
FROM (select distinct TransactionDate from @summaryMtrx) summa
ORDER BY TransactionDate
FOR xml path('')), 1, 1, '')

IF OBJECT_ID('tempdb..#tempSummary') IS NOT NULL
DROP TABLE #tempSummary

SELECT * INTO #tempSummary FROM @summaryMtrx

DECLARE @query NVARCHAR(max)

set @query = N'SELECT UserName,Category,' + @cols + N' from
(
select UserName, Category,TransactionDate, Total
from #tempSummary
) x
pivot
(
max(Total)
for TransactionDate in (' + @cols + N')
) p '

exec sp_executesql @query;

The output that needed based on scenario below, if enter the date for view report

Regards,
Micheale

harishgg1 · July 11, 2019, 6:30am

hi Micheale

drop create data ..

drop table #AccumulatedEarnedLeave 
go 

create table #AccumulatedEarnedLeave
(
UserName varchar(100),	
StartDate	date,
ExpiredDate date,
AccumulatedEarnedLeave decimal(10,2)
)
go 

insert into #AccumulatedEarnedLeave select 'PersonA','2019-03-01','2019-05-31',1.500000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-04-01','2019-06-30',3.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-05-01','2019-07-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-06-01','2019-08-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-07-01','2019-09-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-08-01','2019-10-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-09-01','2019-11-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-10-01','2019-12-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-11-01','2020-01-31',1.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-12-01','2020-02-29',2.000000



drop table #TakenLeave 

create table #TakenLeave 
(
username varchar(100),
takendate date ,
takenleave decimal(10,2)
)


insert into #TakenLeave select 	'PersonA','2019-03-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-04-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-05-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-06-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-07-01',1.500000
insert into #TakenLeave select 	'PersonA','2019-08-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-09-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-10-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-11-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-12-01',0.000000

 

-----------------------------------------------------------------------------------------------------------------------


    DROP TABLE #abc

-- create data set 

select datepart(mm,a.StartDate) as mon,a.AccumulatedEarnedLeave,b.takenleave, 
sum(a.AccumulatedEarnedLeave) over( order by startdate ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) 
as Cumulative into #abc  from #AccumulatedEarnedLeave a join #TakenLeave b 
on a.StartDate = b.takendate

select * from #abc 
-----------------------

Accumulated Leave SQL

DECLARE @cols AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT ',' + Quotename(Cast( mon AS VARCHAR ))  
                      FROM   #abc  
                      ORDER  BY mon 
                      FOR xml path('')), 1, 1, '') 

DECLARE @cols1 AS NVARCHAR(max) 
SELECT @cols1 = Stuff((SELECT ',max(' + Quotename(Cast( mon AS VARCHAR )) +')'+ ' as ' + Quotename(Cast( mon AS VARCHAR ))
                      FROM   #abc  
                      ORDER  BY mon 
                      FOR xml path('')), 1, 1, '') 

    DECLARE @query NVARCHAR(max)

    set @query
    = 'select ''EarnedLeave'',' + @cols1 +'
    from
    (
    select * from #abc
    ) src
    pivot
    (
    max(AccumulatedEarnedLeave) for mon in ('+@cols+')
    ) piv '

    PRINT @query

    EXEC Sp_executesql
    @query;

I also found another way of doing this

hope this helps

drop create data and create data set ...

drop table #AccumulatedEarnedLeave 
go 

create table #AccumulatedEarnedLeave
(
UserName varchar(100),	
StartDate	date,
ExpiredDate date,
AccumulatedEarnedLeave decimal(10,2)
)
go 

insert into #AccumulatedEarnedLeave select 'PersonA','2019-03-01','2019-05-31',1.500000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-04-01','2019-06-30',3.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-05-01','2019-07-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-06-01','2019-08-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-07-01','2019-09-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-08-01','2019-10-31',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-09-01','2019-11-30',2.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-10-01','2019-12-31',0.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-11-01','2020-01-31',1.000000	
insert into #AccumulatedEarnedLeave select 'PersonA','2019-12-01','2020-02-29',2.000000



drop table #TakenLeave 

create table #TakenLeave 
(
username varchar(100),
takendate date ,
takenleave decimal(10,2)
)


insert into #TakenLeave select 	'PersonA','2019-03-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-04-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-05-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-06-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-07-01',1.500000
insert into #TakenLeave select 	'PersonA','2019-08-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-09-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-10-01',2.000000
insert into #TakenLeave select 	'PersonA','2019-11-01',0.000000
insert into #TakenLeave select 	'PersonA','2019-12-01',0.000000

       DROP TABLE #abc

-- create data set 

select datepart(mm,a.StartDate) as mon,a.AccumulatedEarnedLeave,b.takenleave, 
sum(a.AccumulatedEarnedLeave) over( order by startdate ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) 
as Cumulative into #abc  from #AccumulatedEarnedLeave a join #TakenLeave b 
on a.StartDate = b.takendate

select * from #abc

SQL ..

 --DECLARE @cols AS NVARCHAR(max) 
--SELECT @cols = 'select ''Month'',' + Stuff((SELECT ',' + Cast( mon AS VARCHAR ) + ' as ' + QUOTENAME(mon)
--                      FROM   #abc  
--                      ORDER  BY mon 
--                      FOR xml path('')), 1, 1, '') 

DECLARE @cols1 AS NVARCHAR(max) 
SELECT @cols1 = 'select ''Earned Leave'',' + Stuff((SELECT ','+ Cast( AccumulatedEarnedLeave AS VARCHAR )+ ' as ' + QUOTENAME(mon)
                      FROM   #abc  
                      ORDER  BY mon 
                      FOR xml path('')), 1, 1, '') 

DECLARE @cols2 AS NVARCHAR(max) 
SELECT @cols2 = 'select ''takenleave'','+Stuff((SELECT ',' + Cast( takenleave AS VARCHAR ) 
                      FROM   #abc  
                      ORDER  BY mon 
                      FOR xml path('')), 1, 1, '') 


DECLARE @cols3 AS NVARCHAR(max) 
SELECT @cols3 = 'select ''Cumulative'','+Stuff((SELECT ',' + Cast( Cumulative AS VARCHAR )
                      FROM   #abc  
                      ORDER  BY mon 
                      FOR xml path('')), 1, 1, '') 

DECLARE @cols4 AS NVARCHAR(max) 
-- @cols + ' union all ' +  
select @cols4 = @cols1 + ' union all ' +   @cols2 + ' union all '+  @cols3

EXEC Sp_executesql @cols4

micnie_2020 · July 11, 2019, 1:01pm

Hi,

I think I need to use FIFO solution to distribute the "taken" based on date range with gap value (Earned leave for the particular periods) for the 3 month(s) range. If there is balance, LAG function need to be utilize. Need to have a bucket function for recursion I supposed. I have the idea, but still cracking my head for the solution. This involved Date Range.

This article just an basic FIFO: https://www.sqlservercentral.com/articles/solving-fifo-queues-using-windowed-functions.

Thanks.

Regards,
Micheale

harishgg1 · July 11, 2019, 1:17pm

ok micheale

sorry i could not help you out

good luck !!!

micnie_2020 · July 11, 2019, 1:19pm

Hi Haris,

Many thanks for your kind heart & effort. Really appreciate it so much. God bless you richly.

Regards,
Micheale

harishgg1 · July 11, 2019, 3:27pm

Hi Micheale

If i understand what you are trying to do ???

its a piece of cake for me !!!!
i have a lot of experience in SQL

Let me know if you need anything

micnie_2020 · July 16, 2019, 6:32am

Hi Haris,

Issue resolved.

Here is the solutions:-
DECLARE @userData TABLE( UserName varchar(max) NOT NULL, ReferDate Date not null, Category varchar(max) not null);
insert into @userData
(UserName,ReferDate,Category)
select UserName='PersonA',ReferDate='2019-03-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-03-01',Category='Work Half' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-04-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-05-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-05-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-06-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-06-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-07-01',Category='Off 0.5 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-08-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-10-01',Category='Off 1 day' union all
select UserName='PersonA',ReferDate='2019-11-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-12-01',Category='Work' union all
select UserName='PersonA',ReferDate='2019-09-01',Category='Off 1 day'

Declare @daysinlieu TABLE(UserName varchar(max) NOT NULL, StartTransactionDate Date not null,Total float not null default 0.0);
insert into @daysinlieu
(UserName,StartTransactionDate,Total)
select UserName,StartTransactionDate,Total=sum(cnt) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate

Declare @cumulative TABLE(UserName varchar(max) NOT NULL, StartTransactionDate Date not null,Cumulative float not null default 0.0);
insert into @cumulative
(UserName,StartTransactionDate,Cumulative)
SELECT r1.UserName,r1.StartTransactionDate, (SELECT sum(Acc) FROM
(
select UserName,Category='Earned Leave',StartTransactionDate,Acc=sum(cnt) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate
) r2
WHERE r2.UserName=r1.UserName and r2.StartTransactionDate between DateAdd(month,-2,r1.StartTransactionDate) and dbo.udf_GetLastDayOfMonth(r1.StartTransactionDate)) AS Cumulative
FROM (
select UserName,Category='Earned Leave',StartTransactionDate,Acc=sum(cnt) from(
select UserName,StartTransactionDate=ReferDate,cnt=case when Category='Work Half' then count()/2.0 when Category='Work' then count()/1.0 else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,StartTransactionDate
) r1

Declare @Taken TABLE(UserName varchar(max) NOT NULL, Category varchar(max) NOT NULL, TakenDate Date not null,Taken float not null default 0.0);
insert into @Taken
(UserName,Category,TakenDate,Taken)
select UserName,Category='Taken Leave',TakenDate=ReferDate,Taken=sum(cnt) from(
select UserName,ReferDate,cnt=case when Category='Off 1 day' then (count()/1.0) when Category='Off 0.5 day' then (count()/2.0) else 0 end
from @userData
group by UserName,ReferDate,Category
)A
group by UserName,ReferDate

;WITH SRC
AS (
SELECT EARNED.*
,TAKEN.[Taken]
,LAG(EARNED.[Total], 3) OVER (
PARTITION BY EARNED.[UserName] ORDER BY EARNED.[StartTransactionDate]
) AS [DaysThatCouldBeBurnt]
,SUM([Taken]) OVER (
PARTITION BY EARNED.[UserName] ORDER BY EARNED.[StartTransactionDate] ROWS BETWEEN 3 PRECEDING
AND 1 PRECEDING
) AS [TakenInLastThreeMonths]
FROM @daysinlieu AS EARNED
JOIN @Taken AS TAKEN ON EARNED.[UserName] = TAKEN.[UserName]
AND EARNED.[StartTransactionDate] = TAKEN.[TakenDate]
)
SELECT *
,CASE
WHEN [DaysThatCouldBeBurnt] - [TakenInLastThreeMonths] < 0
THEN 0
ELSE [DaysThatCouldBeBurnt] - [TakenInLastThreeMonths]
END AS [Burnt]
FROM SRC

Thanks.

Regards,
Micheale