In between counts for dates

[MK9885]

Hello,

I've a requirement to count a record by range based.

Ex:

Fact:

EMP     StartDate     EMPname    Status    

01       02/01/2014     ABC             Available

01       03/01/2017     BCD             Offline

In above scenario the fact has 2 entry for EMP01 for 02/2014 and 03/2017

So if I do counts it will come as 2.

But he is available through out 02/2014 till 03/2017.

So if I select 01/2015 or 05/2016 he should still be counted in that month.

My Fact is linked to master based on Datekey which is 20140201 and 20170301

I need to calculate based on range and also count him from 2014, 2015, 2016 and 2017 till March 01.

If I select any year or any month in between 2014 and 2017 I should get an entry, which I'm not getting now.

Can someone please help me calculating range based?

stalwar1‌ neelamsaroha1575‌

Thanks.

[neelamsaroha157]

Check this out.

[MK9885]

Thanks for the effort but

the data which I provided seem to be incorrect for year and month,,,

the below attachment is from my dev data..

I picked 1 emp

who has 8 records in fact but 54 records in emp table.

If I select Apr 2015 then he should still show as 1 count though he has no entry for Apr-2015.... which I don't seem to see in your previous attachment.

EDIT:

Please check the qvw, file...

I was able to generate the dates... let me know if that is correct way?

[zebhashmi]

I think what you need to do is

End Date - start Date

and chance the deference of days in to months

[MK9885]

Yes, but I'd like to know how to get that in between dates in master or fact table.

so that I won't miss the counts

[Anonymous]

Check out this.

Creating Reference Dates for Intervals

[zebhashmi]

O Ok than may be you can do that count the master calendar from start date to end date

count({<Date= {....}>}MasterCD)

May be

Thankx

[MK9885]

I checked it but not much of help...

Thanks though.

[neelamsaroha157]

Check this if it meets your requirements

[MK9885]

Yes, I suppose.
Basically your file is filling all the missing dates for Fact?

03/23/2013

03/22/2014

03/21/2015

01/01/2016

02/20/2016

03/19/2016

06/25/2016

03/18/2017

03/23/2013 till 03/22/2014 and 03/23/2014 till 03/21/2015, 03/22/2015 to 01/01/2016 and so on....?

why does it have only 2 years? 2013 and 2014 in Year field.

Isn't it suppose to have 2013, 2014, 2015, 2016 and 2017?

If we see I don't need STDATE...

I just need to populate missing dates from Fact

Like check the previous date and next date, populate missing dates...

Again, I really appreciate your help. Thanks

[neelamsaroha157]

How about this...