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.
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 allSELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union allSELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT 'CCC', '2014-04-09 00:00:00' , NULL union allSELECT 'BBB', '2014-04-11 00:00:00' , '2014-07-11 00:00:00' union allSELECT 'A', '2014-05-11 00:00:00' , '2014-05-21 00:00:00' union allSELECT 'DD', '2014-05-11 00:00:00' , NULL union allSELECT '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 @tabWHERE( (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_DATE1 chandu 2014-03-11 NULL2 chandu 2014-02-11 2014-03-203 AAA 2014-01-11 2014-05-114 CCC 2014-04-09 NULL8 DDD 2014-03-10 2014-04-11 */Expected output:sno name EFFECTIVE_DATE EXPIRATION_DATE1 chandu 2014-03-11 NULL3 AAA 2014-01-11 2014-05-114 CCC 2014-04-09 NULL8 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)?CheersMIK |
|
|
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 allSELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT 'chandu', '2013-01-01 00:00:00' , '2013-12-31 00:00:00' DECLARE @CurrentDate DATETIME = '2014-03-11 00:00:00'SELECT QueryWHERE name = 'chandu'Output for name = 'chandu': 'chandu', '2014-01-01 00:00:00', NULLOutput 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 |
|
|
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 |
|
|
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 allSELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT '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 |
|
|
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 ELIGIBLE2) 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 daysNOTE: one user can eligible for more than one policyProblem 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 |
|
|
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 allSELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT '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 allSELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT '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 @tabSELECT * FROM @tabWHERE--( (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.CheersMIK |
|
|
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 @tabWHERE 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 |
|
|
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 @tabWHERE (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. CheersMIK |
|
|
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 allSELECT 'AAA', '2014-04-11 00:00:00' , '2014-05-11 00:00:00' union allSELECT '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 @tabWHERE 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_DATE2 AAA 2014-04-11 2014-05-11 */--for use ChanduSELECT * FROM @tabWHERE 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_DATE1 chandu 2014-01-01 NULL3 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 |
|
|
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_dateFROM ( 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=1CheersMIK |
|
|
|
|
|
|
|