Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Small Query Help

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-11 : 05:29:03
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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-11 : 07:10:50
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-12 : 02:10:48
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

982 Posts

Posted - 2014-03-12 : 04:07:11
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-12 : 05:27:02
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-12 : 05:34:53
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-12 : 06:51:19
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-13 : 02:36:50
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-13 : 07:24:39
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-13 : 07:48:20
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-13 : 08:04:05
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
Go to Top of Page
   

- Advertisement -