Help required : get count of products

[manojkulkarni]

Hi Friends,

I have 2 tables consists of system & product information. system has multiple software installed and each will have its own expiry date.

I need to calculate the no. of systems expired or going to expire during selected period. Even if one of the product expired then we should consider that system as expired else it should be counted as per its date.

Below is the sample data

St Date:	01-01-2015
En Date:	30-01-2015

Expired	Going to expire	Rest
1	1	2

SystemID

1

2

3

4

5

SystemID	ProductID	EndOfLic
1	1	01-01-2014
1	2	01-02-2015
1	3	01-02-2016
2	1	01-01-2016
2	2	01-01-2016
2	3	01-01-2016
3	1	01-10-2016
3	2	01-10-2016
3	3	01-10-2016
4	1	25-01-2015
4	2	01-10-2016
4	3	01-10-2016

[sunny_talwar]

How do you determine if something is expired or not?

[Qrishna]

More clarifications needed here.

Try providing your selections and something like below screenshot would also be hlpful for us to resolve the problem.

[manojkulkarni]

Background:

Each system is configured with different software (nothing but product) and it can have multiple softwares. We are trying to identify which of the system is expired for the given date range. System considered as expired when any one of the software is expired. In the below sample, SystemID 1 is expired, since one of the product is expired in Jan'14, which is old date compared to the user selection (Jan'15)

if EndOfLic date is less than the selected date range, then particular product is expired for the system.

Date Selections:

St Date:	01-01-2015
En Date:	30-01-2015

Expected output :

Expired	Going to expire	Rest
1	1	2

Table1:

SystemID

1

2

3

4

5

Table2:

SystemID	ProductID	EndOfLic
1	1	01-01-2014
1	2	01-02-2015
1	3	01-02-2016
2	1	01-01-2016
2	2	01-01-2016
2	3	01-01-2016
3	1	01-10-2016
3	2	01-10-2016
3	3	01-10-2016
4	1	25-01-2015
4	2	01-10-2016
4	3	01-10-2016

[manojkulkarni]

Hi Krishna,

Below is date selections & expected result from tables. When i take count of expired systems, i should be getting one count of SystemID 1 and same system should not appear in Going to expire/Rest case. Since one of the product is going to expire in Jan'15, SystemID count is appearing in Going to Expire case as well, which i want to avoid. How to handle this ?

Date Selections:

St Date:	01-01-2015
En Date:	30-01-2015

Expected output :

Expired	Going to expire	Rest
1	1	2