SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Small Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/11/2014 :  05:29:03  Show Profile  Reply with Quote
Hi,

I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)


Below is the detailed script for sample data...

declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', GETDATE(), NULL union all
SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all
SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'CCC', '2014-04-09 00:00:00' , NULL union all
SELECT 'BBB', '2014-04-11 00:00:00' , '2014-07-11 00:00:00' union all
SELECT 'A', '2014-05-11 00:00:00' , '2014-05-21 00:00:00' union all
SELECT 'DD', '2014-05-11 00:00:00' , NULL union all
SELECT 'DDD', '2014-03-10 00:00:00' , '2014-04-11 00:00:00'

--Query
DECLARE @CurrentDate DATETIME = '2014-03-11 00:00:00'
SELECT * FROM @tab
WHERE
(
(EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
OR
( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
)

Output:
/*
sno name EFFECTIVE_DATE EXPIRATION_DATE
1 chandu 2014-03-11 NULL
2 chandu 2014-02-11 2014-03-20
3 AAA 2014-01-11 2014-05-11
4 CCC 2014-04-09 NULL
8 DDD 2014-03-10 2014-04-11
*/

Expected output:
sno name EFFECTIVE_DATE EXPIRATION_DATE
1 chandu 2014-03-11 NULL
3 AAA 2014-01-11 2014-05-11
4 CCC 2014-04-09 NULL
8 DDD 2014-03-10 2014-04-11

NOTE: Please provide the query WITH OUT using GROUP BY clause


--
Chandu

Edited by - bandi on 03/11/2014 06:12:36

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/11/2014 :  07:10:50  Show Profile  Reply with Quote
I might not understood it completely but that record which you wants to exclude, is appearing due to the where condition which you called grace period records.

If you use only
WHERE (EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )

may be you get what you're looking for (at least for the given data)?

Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/12/2014 :  02:10:48  Show Profile  Reply with Quote
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', '2014-01-01 00:00:00', NULL union all
SELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00'

DECLARE @CurrentDate DATETIME = '2014-03-11 00:00:00'
SELECT Query
WHERE name = 'chandu'

Output for name = 'chandu':
'chandu', '2014-01-01 00:00:00', NULL

Output for name = 'AAA':
'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00'


Note: When "@CurrentDate BETWEEN EFFECTIVE_DATE AND COALESCE(EXPIRATION_DATE, @CurrentDate)" is true then return records....

If the above is not the case then only go for second condition
" ( @CurrentDate BETWEEN DATEADD(DD, -30, EFFECTIVE_DATE) AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE), @CurrentDate))"


--
Chandu
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 03/12/2014 :  04:07:11  Show Profile  Reply with Quote
why the second row should be excluded? what was yours third condition?

2 chandu 2014-02-11 2014-03-20
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/12/2014 :  05:27:02  Show Profile  Reply with Quote
Please consider the latest sample data....
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', '2014-01-01 00:00:00', NULL union all
SELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00'

For the above sample data
Name= 'Chandu'
OUTPUT should be
'chandu', '2014-01-01 00:00:00', NULL

This record ('chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00' ) is excluded from the result because of the following conditions:
1) Given Current date ( Assume 11th March, 2014) is not in the range of Effective & Expiration Dates
2) Even though the second condition ( @CurrentDate BETWEEN Effective-30 days and Expiration+90 days ) is true it shouldn't be in the final output (Reason: @CurrentDate BETWEEN Effective and Expiration satisfied)


--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/12/2014 :  05:34:53  Show Profile  Reply with Quote
One thing here is,

1) If the given date is in the range of Effective and Expiration dates then user is ELIGIBLE
2) If user is not ELIGIBLE (as per 1st condition), then check for user eligibility with grace period... ( Grace period condition is Given Date should be in the range of Effective-30 days and Expiration+90 days

NOTE: one user can eligible for more than one policy

Problem is: If one user is eligible without grace period NO NEED to check for GRACE Period; in the ELSE case only check for GRACE PERIOD

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/12/2014 :  06:51:19  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Please consider the latest sample data....
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', '2014-01-01 00:00:00', NULL union all
SELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00'

For the above sample data
Name= 'Chandu'
OUTPUT should be
'chandu', '2014-01-01 00:00:00', NULL

This record ('chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00' ) is excluded from the result because of the following conditions:
1) Given Current date ( Assume 11th March, 2014) is not in the range of Effective & Expiration Dates
2) Even though the second condition ( @CurrentDate BETWEEN Effective-30 days and Expiration+90 days ) is true it shouldn't be in the final output (Reason: @CurrentDate BETWEEN Effective and Expiration satisfied)


--
Chandu



Did you try checking without implementing the grace period condition??
It would return your desired record

declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', '2014-01-01 00:00:00', NULL union all
SELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00'

--Query
DECLARE @CurrentDate DATETIME = '2014-03-11 00:00:00'
SELECT * FROM @tab
SELECT * FROM @tab
WHERE--(
(EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
--OR
--( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
--)

May be grace period condition is for some specific data case/scenario, which if you provide along with the desired output, we (at least I) might understand your narrative more clearly :) But as far as the given data and desired output is concerned, can be obtained via excluding a where condition.

Cheers
MIK

Edited by - MIK_2008 on 03/12/2014 06:59:04
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/13/2014 :  02:36:50  Show Profile  Reply with Quote
Use above sample data....

I want record for user = 'AAA' as (2 AAA 2014-04-11 2014-05-11)

SELECT * FROM @tab
WHERE name = 'AAA' and
--(
(EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
--OR
--( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
--)

NOTE: If we uncomment above GRACE Condition , then we will get record for user 'AAA'.....
But this will not work for user 'chandu'


--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  07:24:39  Show Profile  Reply with Quote
I am not sure what in fact you're looking for .. on 03/12/2014 : 05:27:02 you said the output should be -> "'chandu', '2014-01-01 00:00:00', NULL" record. And in the recent one you're looking for record "AAA".

Anyway if you're looking for AAA, use the CurrentDate as "2014-03-12". may be this is something which is confusing you? since AAA has the affective_date as 04/11/2014 and subtracting 30 days from this makes it 2014-03-12, due to which grace period check is failing and the query is not pulling what you're looking for!

1) if you want only record sno = 1 and not sno =3, then no need for grace period

SELECT * FROM @tab
WHERE (EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )

2) If you want all three records then set the Datetime = 2014-03-12 (instead of 11 March). And use
SELECT * FROM @tab
WHERE ( (EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
OR ( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate ))

3) But out of the three records if you want Chandu to appear only once (either sno=1 or sno=3) you may need to encounter this via row_number and apply appropriate partition/order by for the in point 2.




Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/13/2014 :  07:48:20  Show Profile  Reply with Quote
Am sorry to bather you again...

declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', '2014-01-01 00:00:00', NULL union all
SELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union all
SELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00'

--Query
DECLARE @CurrentDate DATETIME = '2014-03-13 00:00:00'

SELECT * FROM @tab
WHERE name = 'AAA' and
((EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
OR
( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
)
OUTPUT:
/*
sno name EFFECTIVE_DATE EXPIRATION_DATE
2 AAA 2014-04-11 2014-05-11
*/

--for use Chandu
SELECT * FROM @tab
WHERE name = 'chandu' and
((EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
OR
( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
)

OUTPUT from above query:
/*
sno name EFFECTIVE_DATE EXPIRATION_DATE
1 chandu 2014-01-01 NULL
3 chandu 2013-01-01 2013-12-31
*/

But i want only sno=1 record for chandu...
If we modify above query it should also work for user 'AAA'
-

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  08:04:05  Show Profile  Reply with Quote
Do you mean only to show Sno=1 and SNo=2, using the query having grace period as well?

All depends how the real data is but this might help you giving an Idea....

SELECT Sno,Name,Effective_Date,Expiration_date
FROM
(
SELECT Sno,Name,Effective_date,Expiration_Date,Row_Number() Over(Partition by Name Order by Expiration_Date) Seq
FROM @tab
WHERE --name = 'chandu' and
((EFFECTIVE_DATE <= @CurrentDate AND COALESCE(EXPIRATION_DATE, @CurrentDate ) >= @CurrentDate )
OR
( DATEADD(DD, -30, EFFECTIVE_DATE) <= @CurrentDate AND COALESCE(DATEADD( DD, 90, EXPIRATION_DATE) , @CurrentDate ) >= @CurrentDate )
)
)A WHERE Seq=1

Cheers
MIK

Edited by - MIK_2008 on 03/13/2014 08:04:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000