Create a new count table for a table with intervals

[madushanfernand]

Hi All

following is the data set which i extracted from the data base ( Query 1 and Table 1 ).

can anyone please help me to get the out put as follows ( Table 2 ).

Here is my case :

Table 1 has the employee id and his or her career start date and and career end date.

i want to create a table ( Table 2 ) to get the active employee count for the year and month.

for an instance, if the year is 2018 and month is JAN, then the active employees as of 31/01/2018 count should be calculated as

COUNT (DISTINCT IF (EMP_START_DATE<=MonthEndDate and  MonthEndDate<EMP_END_DATE, EMP_ID))

where MonthEndDate  is 31/01/2018 for the above example.

Can we create the solution table using SQL or Qlikview.? Any of the way is expected.

Your help would be really appreciated.

Thank you

Madushan

Query 1,

Table 1,

Table 2,

[mdmukramali]

Hi,

You can follow the Mr. hic‌ below post which will help you to full fill your requirement.

Creating Reference Dates for Intervals

Kindly find the attached application which I have created a sample document based on employee HireDate and EndDate.

if you have any further help do let me know.

Thanks,

Mohammed Mukram.

[madushanfernand]

Hi Mukram

thanks a lot for your response.

i have gone through the post. but couldn't find which you have attached.

could you please send me the attachment again.

[mdmukramali · Accepted solution]

Hi,

find the Attached the file.

[madushanfernand]

Hi

thank you very much for your assistance, this is what exactly i wanted.